bernhard

RockFinder - Highly Efficient and Flexible SQL Finder Module

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

AxdiArx.png4dvtm4D.png

  • Like 7

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
4 hours ago, flydev said:

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

How do you generate these nice logs/speed tests?

Share this post


Link to post
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.

Share this post


Link to post
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 ?

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Sebi
      I've created a small module which lets you define a timestamp after which a page should be accessible. In addition you can define a timestamp when the release should end and the page should not be accessable any more.
      Github: https://github.com/Sebiworld/PageAccessReleasetime
      Usage
      PageAccessReleasetime can be installed like every other module in ProcessWire. Check the following guide for detailed information: How-To Install or Uninstall Modules
      After that, you will find checkboxes for activating the releasetime-fields at the settings-tab of each page. You don't need to add the fields to your templates manually.
      Check e.g. the checkbox "Activate Releasetime from?" and fill in a date in the future. The page will not be accessable for your users until the given date is reached.
      If you have $config->pagefileSecure = true, the module will protect files of unreleased pages as well.
      How it works
      This module hooks into Page::viewable to prevent users to access unreleased pages:
      public function hookPageViewable($event) { $page = $event->object; $viewable = $event->return; if($viewable){ // If the page would be viewable, additionally check Releasetime and User-Permission $viewable = $this->canUserSee($page); } $event->return = $viewable; } To prevent access to the files of unreleased pages, we hook into Page::isPublic and ProcessPageView::sendFile.
      public function hookPageIsPublic($e) { $page = $e->object; if($e->return && $this->isReleaseTimeSet($page)) { $e->return = false; } } The site/assets/files/ directory of pages, which isPublic() returns false, will get a '-' as prefix. This indicates ProcessWire (with activated $config->pagefileSecure) to check the file's permissions via PHP before delivering it to the client.
      The check wether a not-public file should be accessable happens in ProcessPageView::sendFile. We throw an 404 Exception if the current user must not see the file.
      public function hookProcessPageViewSendFile($e) { $page = $e->arguments[0]; if(!$this->canUserSee($page)) { throw new Wire404Exception('File not found'); } } Additionally we hook into ProcessPageEdit::buildForm to add the PageAccessReleasetime fields to each page and move them to the settings tab.
      Limitations
      In the current version, releasetime-protected pages will appear in wire('pages')->find() queries. If you want to display a list of pages, where pages could be releasetime-protected, you should double-check with $page->viewable() wether the page can be accessed. $page->viewable() returns false, if the page is not released yet.
      If you have an idea how unreleased pages can be filtered out of ProcessWire selector queries, feel free to write an issue, comment or make a pull request!
    • By David Karich
      Thanks to the great Pro module "RepeaterMatrix" I have the possibility to create complex repeater items. With it I have created a quite powerful page builder. Many different content modules, with many more possible design options. The RepeaterMatrix module supports the cloning of items, but only within the same page. Now I often have the case that very design-intensive pages and items are created. If you want to use this module on a different page (e.g. in the same design), you have to rebuild each item manually every time.
      With this proof of concept I have created a module which adds the feature to copy a repeater item to the clipboard so that you can paste this item to another page with the same repeater field. The module has been developed very rudimentarily so far. It is currently not possible to copy nested items. There is also no check of Min/Max. You can also only copy items that have the same field on different pages. And surely you can solve all this more elegantly with AJAX. But personally I lack the deeper understanding of the repeaters. Also missing on the Javascript side are event triggers for the repeaters, which would make it easier. Like e.g. RepeaterItemInitReady or similar.
      it would be great if @ryan would implement this functionality in the core of RepeaterMatrix. I think he has better ways to implement this. Or what do you think, Ryan?
      Everybody is welcome to work on this module and improve it, if it should not be integrated into the matrix core. Therefore I put it for testing and as download on GitHub: https://github.com/FlipZoomMedia/InputfieldRepeaterMatrixDublicate
      You can best see the functionality in the screencast: 
       
    • By anderson
      Hi,
      Please take a look at this:
      https://templatemag.com/demo/Good/
      The upper nav bar, including dropdowns like "pages" and "portfolios", what do you call this whole thing? At first I guess it's called "dropdown nav bar", but seems not.
      AND of course, what's the simplest way/module to achieve this in PW?
      Thanks in advance.
    • By Sebi2020
      Hey, I'm new and I created a simple module for tagging pages because I didn't found a module for it (sadly this is not a core feature). This module is licensed under the GPL3 and cames with absolutly no warranty at all. You should test the module before using it in production environments. Currently it's an alpha release. if you like the module or have ideas for improvements feel free to post a comment. Currently this fieldtype is only compatible with the Inputfield I've created to because I haven't found  an Inputfield yet, that returns arrays from a single html input.
      Greetings Sebi2020
      FieldtypeTags.zip.asc
      InputfieldTagify.zip
      InputfieldTagify.zip.asc
      FieldtypeTags.zip
    • By psy
      Background
      I'm creating a module to integrate https://pushalert.co/ into ProcessWire. You actually don't even need a module. You could just use the "Other Websites" javascript provided by PushAlert for basic functionality, ie send a broadcast notification to all subscribers. This is essentially what all the other integrations, including WordPress, do. The WP integration installs a widget with a form enabling the admin to enter details such as title, message, etc from a blog post. It does not:
      collect any statistics within the CMS about the notification enable audience fine tuning to eg a particular subscriber or subscriber segment within WP. The admin needs to use the PA dashboard for that functionality PushAlert has a javascript and REST API. It's intended that this module will use both. https://pushalert.co/documentation 
      What my module does so far:
      associate a subscription with a user. FE user clicks a button on the website front end to subscribe and/or agrees to the browser popup to accept notifications from this site send broadcast push alerts from a page within admin It doesn't have a 'widget' but easy enough to create a fieldsetpage with the relevant fields and add that fs page to any appropriate templates, then with a hook, send the notification. Need to be careful that once published/sent, the notification is not automatically re-sent on subsequent page edits.
      Looking for help/collaboration on how best:
      to send a notification, eg from a blog post, then track the statistics. Dilemma is that the push notification must come from the admin page. Responses go to the sending page which, as it's an admin page, is restricted and will not accept the https response. This is where the other CMS integrations stop. The only json response from PushAlert is the status, eg 'success', and the notification id. There is no opportunity at this point to capture the sending page id. handle, 'once sent on page publish', do not automatically resend on future page edits Am thinking along the lines that FS Page will have a @kongondo runtime markup field https://modules.processwire.com/modules/fieldtype-runtime-markup/ to pull the stats from PushAlert. Every time an admin visits the page, the stats will update.
      Once an admin checks the 'Send notification on page publish' checkbox, a hook creates new front end page that records the 'sender page', sends the notification request to PA, which then uses that newly created frontend page, as the response endpoint. Another rook re-associates the front end page with the admin page (eg blog post), to update the stats.
      Potential use cases:
      Notify individual and/or users with a particular role of an event, eg "New work opportunity" for job seekers; new blog post published; entries now open, etc...
      Looking for help/ideas/collaboration on this module. Please let me know if you're interested and as I do, believe this would be a great addition to ProcessWire