Jump to content

RockSqlFinder (outdated thread, link to current version inside)


bernhard
 Share

Recommended Posts

11 minutes ago, dragan said:

There goes the weekend...

Just make sure you have the latest version by deleting /site/modules/RockFinder and doing a git clone git@gitlab.com:baumrock/RockFinder.git

Link to comment
Share on other sites

I'm having troubles installing the profile. Maybe 100MB for the .sql is a bit too much?

I come to this screen and then nothing happens (I waited 5 minutes).

install-screen.PNG.a36c0690a460f646e1315f2167a34469.PNG

"Profile Import" is not a link or button.

I don't use site profiles very often... Is it safe to just import your sql manually? or will that somehow break the install process?

PW didn't yet ask me about setting up an admin username + password + admin URL

checking the DB: it's only 170KB.

db.PNG

Link to comment
Share on other sites

What kind of DB-export did you do?

https://docs.phpmyadmin.net/en/latest/import_export.html#id3

It seems you didn't use "extended inserts" and thus the whole .sql is much bigger than it needs to be.

db-import.PNG.b3c2c7dc8ade9550577762ce287591be.PNG

So, instead of 

INSERT INTO `field_body` (`pages_id`, `data`) VALUES('1028', '5acf61ceb89d4');
INSERT INTO `field_body` (`pages_id`, `data`) VALUES('1029', '5acf61ceba60b');
INSERT INTO `field_body` (`pages_id`, `data`) VALUES('1030', '5acf61cebc4e7');

the much shorter syntax could be as described here: http://www.scriptalicious.com/blog/2009/04/complete-inserts-or-extended-inserts-in-phpmyadmin/

 

Link to comment
Share on other sites

Thanks to @thetuningspoon reminding me of this thread I updated RockFinder to use the SQL portion of the $pages->find() operation instead of using findIDs() method. This has three main advantages:

  • The query stays small even when querying thousands of pages (before this meant we where listing thousands of IDs in the WHERE statement)
  • No need for  $finder->sort = true/false; The returned results are always returned in the same sort order as defined in the selector
  • RockFinder now also supports PW versions prior to 3.0.46 because it no longer needs the findIDs() method

Before:

WHERE
  `pages`.`id` IN (21245,........)
ORDER BY
  field(`pages`.`id`, 21245,........)

After:

WHERE (pages.templates_id=54) 
AND ((pages.parent_id=21205 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=21205 OR pages_id=21205))) 
AND (pages.status<1024) 
GROUP BY pages.id 

Version 6 is on gitlab :)

  • Like 1
Link to comment
Share on other sites

Hi @bernhard,

A bit OT, but in your development of this module have you had the need yet to get the path or URL of the pages you are including in a query? Do you know a way of getting or constructing the page path/URL from the database data without getting Page objects involved?

From a quick investigation it looks like PW builds the path/URL with methods in the Page class but I was hoping to avoid having to load Page objects. Any tips?

Link to comment
Share on other sites

Hey @Robin S I have not had the need for that, but I also haven't come across the page path in the db...

One option would be the "Page Paths" module in the core section of the modules, that says:

Quote

Enables page paths/urls to be queryable by selectors. Also offers potential for improved load performance. Builds an index at install (may take time on a large site). Currently supports only single languages sites.

Another option would be a field populated by a saveready hook. This could also be multilang. But I guess you want it directly from the DB? The saveready hook would maybe not be a good solution if you rename a parent with 1000s of children... you would have to take care of those situations and also it would be slow.

Maybe you could build a query that joins all the pagenames together to one path? You would need to define a max nesting level then, I guess... Maybe that query would be slow? Sorry, no better ideas atm :)

  • Like 1
Link to comment
Share on other sites

@thetuningspoon seems that you have some experience with pw's internal pagefinder. I would highly appreciate if could find some time to compare my implementation to the core features of pagefinder. I have the feeling that some parts of RockFinder are overly complex and maybe already built into the core and there is some room for improvement...

Link to comment
Share on other sites

I wonder if the slowdown you saw was just your servers mysql config file needing tweaks to increase ram it can use for sorting.  Maybe you can post your my.ini file?

Also, one thing pw core is not good at I have found, is getting a unique list of things.  ie. Find all distinct Person names from template person, but without loading them all into memory and then looping through them all to merge them down.  

Is it easy to do a distinct or group by with this and set the results as a processwire page array?  If so, this is a lifesafer in some situations.

Link to comment
Share on other sites

14 hours ago, joer80 said:

I wonder if the slowdown you saw was just your servers mysql config file needing tweaks to increase ram it can use for sorting.  Maybe you can post your my.ini file?

I don't think so, but I don't care about that any more as the new version works differently and always returns a sorted resultset quickly and efficiently ?

14 hours ago, joer80 said:

Is it easy to do a distinct or group by with this and set the results as a processwire page array?  If so, this is a lifesafer in some situations.

Absolutely! By default the finder query would be this:

$finder = new RockFinder('template=person', ['title', 'surname']);
$sql = $finder->getSQL();
d($sql, [6,999]);

448791550_2018-05-1210_13_31.png.50a453c9ea829301a94a9179344c5955.png

Doing the distinct:

2143681106_2018-05-1210_19_19.png.7f6e091b68f045aaccfdfa4e90195d71.png

getting ids (verbose):

701544513_2018-05-1210_22_29.thumb.png.cdfe6295d02cefe1a9fd74989153f41b.png

Selecting only ids by group_by:

110232373_2018-05-1210_25_16.thumb.png.1fcdbc73902bbbb32292bd55ed485935.png

And finally to get the pages:

239427359_2018-05-1210_30_16.thumb.png.27253128ccd521209b59e645624b2096.png

(btw: the portion of the sql dump is from an outdated version of rockfinder... but it still works)

I see those kind of things have been requested several times now and I guess they will pop up quite often (doing aggregations, distincts, group_bys etc). It would be great to have a method for that in RockFinder, so if you have some time I would appreciate a PR making things like this possible:

$finder = new RockFinder('template=person', ['title', 'surname']);
$sql = $finder->getSQL();

$sql = "SELECT id FROM ($sql) as tmp group by concat(title,coalesce(surname,''))";
$ids = $finder->getArray($sql);
$distinct_pages = $finder->getPages($ids);

// or
$sql = "SELECT id FROM ($sql) as tmp group by concat(title,coalesce(surname,''))";
$distinct_pages = $finder->getPages($sql, 'id');

?

 

  • Like 2
  • Thanks 1
Link to comment
Share on other sites

On 5/12/2018 at 10:35 AM, bernhard said:

I see those kind of things have been requested several times now and I guess they will pop up quite often (doing aggregations, distincts, group_bys etc). It would be great to have a method for that in RockFinder, so if you have some time I would appreciate a PR making things like this possible:

I've added 3 new methods to the module: getArray(), getValues() and getPages() and officially released the module as BETA with a dedicated thread: 

I also added docs for all available methods with examples, here are the new ones:

getArrays.png?raw=true

getValues.png?raw=true

getPages.png?raw=true

@joer80 see here for what you requested: https://gitlab.com/baumrock/RockFinder/tree/master#custom-sql-aggregations-groupings-distincts

 

@Guy Verville I cannot reproduce your issue - please provide more information with exact steps to reproduce.

Please use the other thread for all further discussion.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...