Jump to content

[deprecated] RockFinder1


bernhard

Recommended Posts

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

Link to comment
Share on other sites

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.

 

611595178_Capturedecran2019-01-23a10_10_18.png.774b36454624f6614a9b1375c018d1ca.png

 

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

2054576922_Capturedecran2019-01-23a11_45_33.png.b6918465764978dec8891934bc6090bd.png

Edited by flydev
The big query
  • Like 1
Link to comment
Share on other sites

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?

  • Thanks 1
Link to comment
Share on other sites

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 :

203214322_Capturedecran2019-01-23a15_20_33.png.b395054a1ee51080f4aad4f511ab826c.png

 

From 96ms to ˜6ms :

1190313192_Capturedecran2019-01-23a15_21_01.png.f4d139e1934473f84adc0aa8c8672ea1.png

 

? ?

  • Like 2
Link to comment
Share on other sites

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

yNdQaD5.png

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

 

  • Like 2
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

KfPqOYn.png

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 ?

Link to comment
Share on other sites

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

image.png.a1479394400af0f8aee8ae930dac6241.png

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 ! ?

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

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 ? 

  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...

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

Link to comment
Share on other sites

  • 4 weeks later...

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 ? 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 months later...

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 by dragan
Firefox
Link to comment
Share on other sites

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

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
×
×
  • Create New...