RockSqlFinder (outdated thread, link to current version inside)

[bernhard]

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

[dragan]

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).

"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.

[dragan]

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.

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/

 

[bernhard]

@dragan see pm

[bernhard]

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

[Robin S]

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?

[bernhard]

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

[bernhard]

@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...

[joer80]

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.

[bernhard]

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]);

Doing the distinct:

getting ids (verbose):

Selecting only ids by group_by:

And finally to get the pages:

(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');

?

 

[Guy Verville]

Hi,

I tried your module but I got this when trying to access it.

I am using PW 3.0.99

Edited May 12, 2018 by Guy Verville
adding PW Version.

[bernhard]

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:

@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.

[kongondo]

moderator note

I've moved this thread to module dev since it is no longer a support thread. I might lock it in the future if there is a need.