Jump to content
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

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

AxdiArx.png4dvtm4D.png

  • Like 8

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

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

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By gebeer
      I am happy to present my new fieldtype FieldtypeImageFromPage. It is made up of 2 modules:
      Fieldtype Image Reference From Another Page is a Fieldtype that stores a reference to a single image from another page. The image can be selected with the associated Inputfield.
      Inputfield Select Image From Page is an Inputfield to select a single image from images on a predefined page and it's children.
      And there also is a helper module that takes care of cleanup tasks.
      This module evolved out of a discussion about my other Module FieldtypeImagePicker.  It caters for use cases where a set of images is being reused multiple times across a site. With this fieldtype these images can be administered through a chosen page. All images uploaded to that page will be available in the inputfield.
      When to use ?
      Let editors choose an image from a set of images that is being used site-wide. Ideal for images that are being re-used across the site.
      Suited for images that are used on multiple pages throughout the site (e.g. icons).
      Other than the native ProcessWire images field, the images here are not stored per page. Only references to images on another page are stored. This has several advantages:
      one central place to organize images when images change, you only have to update them in one place. All references will be updated, too. (Provided the name of the image that has changed stays the same) Features
      Images can be manipulated like native ProcessWire images (resizing, cropping etc.) Image names are fully searchable through the API Accidental image deletion is prevented. When you want to delete an image from one of the pages that hold your site-wide images, the module searches all pages that use that image. If any page contains a reference to the image you are trying to delete, deletion will be prevented. You will get an error message to help you edit those pages and remove references there before you can finally delete the image. How to install and setup
      Download and install this module like any other modules in ProcessWire Create a page in the page tree that will hold your images. This page's template must have an images field Upload some images to the page you created in step 2 Create a new field. As type choose 'Image Reference From Another Page'. Save the field. In 'Details' Tab of the field choose the page you created in step 2 Click Save button Choose the images field name for the field that holds your images (on page template from step 2) Click Save button again Choose whether you want to include child pages of page from step 2 to supply images Add the field to any template You are now ready to use the field View of the inputfield on the page edit screen:

      View of the field settings

      The module can be installed from this github repo. Some more info in the README there, too.
      In my tests it was fairly stable. After receiving your valued feedback, I will eventually add it to the modules directory.
      My ideas for further improvement:
      - add ajax loading of thumbnails
      Happy to hear your feedback!
       
    • By gebeer
      Although the PW backend is really intuitive, ever so often my clients need some assistance. Be it they are not so tech savvy or they are not working in the backend often.
      For those cases it is nice to make some help videos available to editors. This is what this module does.
      ProcessHelpVideos Module
      A Process module to display help videos for the ProcessWire CMS. It can be used to make help videos (screencasts) available to content editors.
      This module adds a 'Help Videos" section to the ProcessWire backend. The help videos are accessible through an automatically created page in the Admin page tree. You can add your help videos as pages in the page tree. The module adds a hidden page to the page tree that acts as parent page for the help video pages. All necessary fields and templates will be installed automatically. If there are already a CKEditor field and/or a file field for mp4 files installed in the system, the module will use those. Otherwise it will create the necessary fields. Also the necessary templates for the parent help videos page and it's children are created on module install. The module installs a permission process-helpvideos. Every user role that should have access to the help video section, needs this permission. I use the help video approach on quite a few production sites. It is stable so far and well received by site owners/editors. Up until now I installed required fields, templates and pages manually and then added the module. Now I added all this logic to the install method of the module and it should be ready to share.
      The module and further description on how to use it is available on github: https://github.com/gebeer/ProcessHelpVideos
      If you like to give it a try, I am happy to receive your comments/suggestions here.
    • By Robin S
      A module created in response to the topic here:
      Page List Select Multiple Quickly
      Modifies PageListSelectMultiple to allow you to select multiple pages without the tree closing every time you select a page.
      The screencast says it all:

       
      https://github.com/Toutouwai/PageListSelectMultipleQuickly
      https://modules.processwire.com/modules/page-list-select-multiple-quickly/
    • By gebeer
      Hello all,
      sharing my new module FieldtypeImagePicker. It provides a configurable input field for choosing any type of image from a predefined folder.
      The need for it came up because a client had a custom SVG icon set and I wanted the editors to be able to choose an icon in the page editor.
      It can also be used to offer a choice of images that are used site-wide without having to upload them to individual pages.
      There are no image manipulation methods like with the native PW image field.
      Module and full description can be found on github https://github.com/gebeer/FieldtypeImagePicker
      Kudos to @Martijn Geerts. I used his module FieldTypeSelectFile as a base to build upon.
      Here's how the input field looks like in the page editor:

      Hope it can be of use to someone.
      If you like to give it a try, I'm happy to hear your comments or suggestions for improvement. Eventually this will go in the module directory soon, too.
    • By bernhard
      @Sergio asked about the pdf creation process in the showcase thread about my 360° feedback/survey tool and so I went ahead and set my little pdf helper module to public.
      Description from PW Weekly:
       
      Modules Directory: https://modules.processwire.com/modules/rock-pdf/
      Download & Docs: https://github.com/BernhardBaumrock/RockPDF
       
      You can combine it easily with RockReplacer: 
      See also a little showcase of the RockPdf module in this thread:
       
×
×
  • Create New...