[deprecated] RockFinder1

[KarlvonKarton]

1 hour ago, dragan said:

it may have to do with how mySQL handles case-sensitivity on different OS

I think you're right.  I still wonder why the SQL is altered (RF or OS or combi OS/SQL), but I also understand now that I simply made the mistake by using uppercase for fields either way.  Good to know for future projects...

[dotnetic]

@KarlvonKarton I had that excact problem with uppercase fields on a Windows server also. Then on the live server which is a Linux machine, the queries did not work, becaus lowercase was used. Make sure to use all lowercase fields in your database.

[bernhard]

RockFinder now supports array-syntax, which makes the code a lot cleaner:

[flydev]

Thanks for the update - Would you have an idea why my queries are running so much faster on mysql5.7 than mysql5.6 ?  What is your version on your dev platform ? Did you tested the two server version on your side ? 

Check. I switched my MySQL server version on MAMP from 5.6 to 5.7 and when I clicked a button which execute quite big query, it was, oh wow, instant. Switched back to 5.6, the query took like 8 to 12  52 seconds ? 

Still, I didn't investigated what's going on, but result... and the same happened on a Windows Server with MySQL 5.6/5.7 upgrade.

 

 

Edit: Another one looking through millions of pages...

Edited January 23, 2019 by flydev
The big query

[bernhard]

I'm using 5.7 both on dev and live. 

3 hours ago, flydev said:

Would you have an idea why my queries are running so much faster on mysql5.7 than mysql5.6 ?

Sorry, no idea ? http://smalldatum.blogspot.com/2017/06/sysbench-for-mysql-50-51-55-56-57-and-8.html , while http://www.oaktable.net/content/mysql-56-vs-57 says they perform quite similar. Maybe 5.6 has a problem with those subqueries?

[flydev]

I am going to ask on StackExchange but I got another (magic) find, which give incredible performance on InnoDB schema.

 

Adjusting `innodb_buffer_pool_size` (it need to be calculated) give better result than those in the above screenshots... 

The query to calculate the size to set based on the current data set :

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

 

So for my DB I needed to set the var to 2GB (still not 100% sure) but the result are there - lower than in my previous screenshot : 

 

From 18s to 4s :

 

From 96ms to ˜6ms :

 

? ?

[bernhard]

Nice! ? Please let us now about the results/answers.

[bernhard]

New version online: 1.1.2

• The selector for the finder is now always stored as string, but can be retrieved as string or array - getSelectorStr() or getSelectorArr()

This can be useful to share code for grids that are similar but need little adjustments, like one grid shows all available trainings, one grid shows only trainings that where booked by the current user:

include(__DIR__.'/trainings.php');

$finder->setSelectorValue('client', $this->user);
$finder->addField('done');

$this->setData($finder);

 

[dotnetic]

4 hours ago, flydev said:

How do you generate these nice logs/speed tests?

[dotnetic]

I got the following notice in TracyDebugger after upgrading to 1.1.2:

PHP Notice: Array to string conversion in ...\bewerbertool-talents\wire\core\PageFinder.php:195

and the grid does not load if TracyDebugger is active because of this notice, I think.

The notice seem to come from my query which has an OR condition in it:

$stellen = new RockFinder("template=stelle,status=1|2049", ['title', 'stellekosten', 'kostenproclick', 'mitarbeiterid', 'created', 'bookmark']);

If i disable Tracy it works fine, and also if I click the reload grid button. But on a browser reload the error happens.

It worked before even with Tracy active.

[bernhard]

OR selectors should not be a problem. Could you please try to refresh all cached files? Maybe there's something else that causes this? Please try to narrow it down a little more... What's the array that is converted to string on line 195 of PageFinder.php ?

[dotnetic]

Seems to have to do with the status field, which is for pageStatus. The line 195 in PageFinder.php returns this:

I solved it with using a different query. I only used status=1|2049 before, because "include=all" did not work, and I even had unpublished pages in my result.

And that was what I wanted. Show only published pages, or show them all. Don't know why it did not work before, but now it does again.

[simonGG]

Hi there,

first of all thanks for this great module! 
Im working on a search wich will need a lot of filtering.
For example i want to get all persons with a height between 160cm & 170cm and a weight between 50kg & 60kg

For the moment i came up with this approche.
But im wondering if there is a more elegant "build in" functionality to solve this.

The Array $searchFields could contain more or less fields as in this example .

$searchFields = [ [ 'promo_koerpergroesse', 160, 170 ], [ 'promo_koerpergewicht', 50 ,60 ] ];
foreach ( $searchFields as $searchField => $searchFieldValue ) 
{
	$filterField 	= $searchFieldValue[0];
	$filterValMin 	= $searchFieldValue[1];
	$filterValMax 	= $searchFieldValue[2];

	$finder->filter( function( $row ) use ( $filterField, $filterValMin, $filterValMax ) {
		return $row->$filterField > $filterValMin & $row->$filterField < $filterValMax; 
	});
}

Thanks / ciao Simon

[bernhard]

Just merged a PR that takes care of camelCase fieldnames: https://github.com/BernhardBaumrock/RockFinder/pull/4/commits/c3bef03c5fc00b9439c0b6bba0997a5843f5868e

@simonGG the filter might work, but it's for sure more efficient to do this via SQL. But if you don't have lots of entries it might be easier to stay with your solution.

[monollonom]

3 hours ago, bernhard said:

Just merged a PR that takes care of camelCase fieldnames: https://github.com/BernhardBaumrock/RockFinder/pull/4/commits/c3bef03c5fc00b9439c0b6bba0997a5843f5868e

@simonGG the filter might work, but it's for sure more efficient to do this via SQL. But if you don't have lots of entries it might be easier to stay with your solution.

Hey, PR author here, just wanted to thank you for this great module @bernhard. I wrote a module which was loading more than a thousand pages and it was taking almost a minute to load... now it's down to a second or less ! ?

[bernhard]

7 minutes ago, monollonom said:

Hey, PR author here, just wanted to thank you for this great module @bernhard. I wrote a module which was loading more than a thousand pages and it was taking almost a minute to load... now it's down to a second or less ! ?

Great to hear that ? Thank you for your PR ? 

[monollonom]

Hi,

(disclaimer : I'm a noob at sql)

I am facing a situation where I would need to get the titles of pages linked in a page reference within a repeater. I tried a few combinations but each time I get an empty array. Maybe somebody can point me to the right direction ?

Context : I have an event page with sub-events (repeater), each of which are held by different persons (page reference).

Thanks !

Edit : in the end I created a page reference field in my template that is populated with the persons linked in each sub-events, using a hook. Simpler...

[kongondo]

Hi @bernhard,

I've not had a chance to test this yet.  Quick question, in multilingual setups, is it language aware? If not, can it be made to be language aware?

Many thanks for all the hard work!

[bernhard]

Hi @kongondo,

it is language aware in general, yes. There might be some special cases though like repeater fields, where some additional tweaks would be necessary. I've updated the docs with some words about that: https://github.com/BernhardBaumrock/RockFinder/blob/master/readme.md#multilanguage

Hope that helps. Easy finders should just be fine and you can always use RockFinder as a start and join custom SQL as you need ? 

[kongondo]

1 hour ago, bernhard said:

Hope that helps. Easy finders should just be fine

It does, thanks! An easy finder like page title in 'my language' is what I was after. I'd also want this for page reference fields, pointing to the found pages titles.  I'll check the docs.

Cheers.

[bernhard]

The docs are far from complete. For page reference fields you might have a look at https://github.com/BernhardBaumrock/RockFinder/blob/master/readme.md#joins

Joins are great, because you can split the queries into easy finders, see how it works and then join everything together in one result. Complex fields like page reference fields or repeaters might mess up some queries or might need some extra work on the module.

[dragan]

I am getting ERR_RESPONSE_HEADERS_TOO_BIG in Chrome with the RockFinder Tester. After googling, I tried to add this line at the top:

header_remove('Set-Cookie');

which doesn't change anything. Did you ever encounter this as well?

Problem is, this even happens for really small datasets like limit=10 :-|

In Firefox there are no such problems, even with huge datasets.

Edited August 6, 2019 by dragan
Firefox

[bernhard]

I get this error on one site using tracy debugger. Are you using tracy?

[dragan]

This happens when I run setup/rockfindertester/, but Tracy is enabled. When I use RF code inside Tracy console in Chrome, everything's fine.

 

[dragan]

Two small questions (and I guess I know the answers already):

a) I can't get any RF output (arrays, objects) to play nice with PW's pagination / renderPager(). I guess pagination expects a PageArray, not just any kind of array. I know RF was never meant to be used in the frontend, but I tried anyway (and of course, I know building my own pagination with something like Tabulator is quite easy). I just wanted to compare an existing page (how much speed I would gain using RF instead of native PF find).

b) I tried queries like 

$finder = new \ProcessWire\RockFinder('parent=1041, include=all, template=project, sort=-year', ['title', 'year', 'modified', 'url']);

url is not recognized. I guess because a PW page URL is not stored inside the DB as-is, but is created when calling the $page->url() method. Am I correct? (PW has to assemble parents(s), page-names etc.)