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

@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 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 Mike Rockett
      Jumplinks for ProcessWire
      Release: 1.5.56
      Composer: rockett/jumplinks
      Jumplinks is an enhanced version of the original ProcessRedirects by Antti Peisa.
      The Process module manages your permanent and temporary redirects (we'll call these "jumplinks" from now on, unless in reference to redirects from another module), useful for when you're migrating over to ProcessWire from another system/platform. Each jumplink supports wildcards, shortening the time needed to create them.
      Unlike similar modules for other platforms, wildcards in Jumplinks are much easier to work with, as Regular Expressions are not fully exposed. Instead, parameters wrapped in curly braces are used - these are described in the documentation.
      Under Development: 2.0, to be powered by FastRoute
      As of version 1.5.0, Jumplinks requires at least ProcessWire 2.6.1 to run.
      View on GitLab
      Download via the Modules Directory
      Read the docs
      Features
      The most prominent features include:
      Basic jumplinks (from one fixed route to another) Parameter-based wildcards with "Smart" equivalents Mapping Collections (for converting ID-based routes to their named-equivalents without the need to create multiple jumplinks) Destination Selectors (for finding and redirecting to pages containing legacy location information) Timed Activation (activate and/or deactivate jumplinks at specific times) 404-Monitor (for creating jumplinks based on 404 hits) Additionally, the following features may come in handy:
      Stale jumplink management Legacy domain support for slow migrations An importer (from CSV or ProcessRedirects) Feedback & Feature Requests
      I’d love to know what you think of this module. Please provide some feedback on the module as a whole, or even regarding smaller things that make it whole. Also, please feel free to submit feature requests and their use-cases.
      Note: Features requested so far have been added to the to-do list, and will be added to 2.0, and not the current dev/master branches.
      Open Source

      Jumplinks is an open-source project, and is free to use. In fact, Jumplinks will always be open-source, and will always remain free to use. Forever. If you would like to support the development of Jumplinks, please consider making a small donation via PayPal.
      Enjoy! :)
    • By BitPoet
      As threatened in Ryan's announcement for 3.0.139, I built a little module for sliding toggles as a replacement for checkboxes. Styling of the input is CSS3 only (with all the usual caveats about older browsers), no JS necessary, and may still be a bit "rough around the edges", so to speak, since I didn't have much time for testing on different devices or brushing things up enough so I'd feel comfortable pushing it to the module directory. But here's the link to the GitHub repo for now:
      InputfieldSlideToggle
      Fieldtype and Inputfield that implements smartphone-style toggles as replacement for checkbox inputs. The visualization is CSS-only, no additional JS necessary.
      Status
      Still very alpha, use with caution!
      Features / Field Settings
      Size
      You can render the toggles in four different sizes: small, medium, large and extra large.
      Off Color
      Currently, "unchecked" toggles can be displayed either in grey (default) or red.
      On Color
      "Checked" toggles can be rendered in one of these colors: blue (default), black, green, grey, orange or red.
      Screenshots

      Some examples with checkbox label


      View all Size and Color Combinations
      Small toggles Medium toggles Big toggles Extra big toggles  









    • By Orkun
      Hi Guys
      I needed to add extended functionalities for the InputfieldDatetime Module (module is from processwire version 2.7.3) because of a Request of Customer.
      So I duplicated the module and placed it under /site/modules/.
      I have added 3 new Settings to the InputfieldDatetime Module.
      1. Day Restriction - Restrict different days based on weekdays selection (e.g. saturday, sunday) - WORKING

       
      2. Time Slots - Define Time slots based on custom Integer Value (max is 60 for 1 hour) - WORKING

       
      3. Time Range Rules per Weekday - Define a minTime and MaxTime per Weekday (e.g. Opening Hours of a Restaurant) - NOT WORKING PROPERLY

       
      The Problem
      Time Slots and Day Restriction working fine so far. But the Time Range Rules per Weekday doesn't work right.
      What should happen is, that when you click on a date, it should update the minTime and maxTime of the Time Select.
      But the change on the select only happens if you select a date 2 times or when you select a date 1 time and then close the datepicker and reopen it again.
      The time select doesn't get change when you select a date 1 time and don't close the picker.
      Here is the whole extended InputfieldDatetime Module.
      The Files that I have changed:
      InputfieldDatetime.module InputfieldDatetime.js jquery-ui-timepicker-addon.js (https://trentrichardson.com/examples/timepicker/) - updated it to the newest version, because minTime and maxTime Option was only available in the new version  
      Thats the Part of the JS that is not working correctly:
      if(datetimerules && datetimerules.length){ options.onSelect = function(date, inst) { var day = $(this).datetimepicker("getDate").getDay(); day = day.toString(); var mintime = $(this).attr('data-weekday'+day+'-mintime'); var maxtime = $(this).attr('data-weekday'+day+'-maxtime'); console.log("weekday: "+day); console.log("minTime: "+mintime); console.log("maxTime: "+maxtime); var optionsAll = $(this).datetimepicker( "option", "all" ); optionsAll.minTime = mintime; optionsAll.maxTime = maxtime; $(this).datetimepicker('destroy'); $(this).datetimepicker(optionsAll); $(this).datetimepicker('refresh'); //$.datepicker._selectDate($(this).attr("id"),date); //$.datepicker._base_getDateDatepicker(); // var inst = $.datepicker._getInst($(this)); // $.datepicker._updateDatepicker(inst); /*$(this).datetimepicker('destroy'); InputfieldDatetimeDatepicker($(this), mintime, maxtime); $(this).datetimepicker('refresh'); */ // $(this).datetimepicker('option', {minTime: mintime, maxTime: maxtime}); } } Can you have a look and find out what the Problem is?
      InputfieldDatetime.zip
       
      Kind Regards
      Orkun
    • By teppo
      This module tracks changes, additions, removals etc. of public (as in "not under admin") pages of your site. Like it's name says, it doesn't attempt to be a version control system or anything like that - just a log of what's happened.
      At the moment it's still a work in progress and will most likely be a victim of many ruthless this-won't-work-let's-try-that-instead cycles, but I believe I've nailed basic functionality well enough to post it here.. so, once again, I'll be happy to hear any comments you folks can provide
      https://modules.processwire.com/modules/process-changelog/
      https://github.com/teppokoivula/ProcessChangelog
      How does it work?
      Exactly like it's (sort of) predecessor, Process Changelog actually consists of two modules: Process Changelog and Process Changelog Hooks. Hooks module exists only to serve main module by hooking into various functions within Pages class, collecting data of performed operations, refining it and keeping up a log of events in it's own custom database table (process_changelog.) Visible part is managed by Process Changelog, which provides users a (relatively) pretty view of the contents of said log table.
      How do you use it?
      When installed this module adds new page called Changelog under Admin > Setup which provides you with a table view of collected data and basic filtering tools See attached screenshots to get a general idea about what that page should look like after a while.
      For detailed installation instructions etc. see README.md.
       


    • By Gadgetto
      Status update links (inside this thread) for SnipWire development will be always posted here:
      2019-08-08
      2019-06-15
      2019-06-02
      2019-05-25
      If you are interested, you can test the current state of development:
      https://github.com/gadgetto/SnipWire
      Please note that the software is not yet intended for use in a production system (alpha version).
      If you like, you can also submit feature requests and suggestions for improvement. I also accept pull requests.
      ---- INITIAL POST FROM 2019-05-25 ----
      I wanted to let you know that I am currently working on a new ProcessWire module that fully integrates the Snipcart Shopping Cart System into ProcessWire. (this is a customer project, so I had to postpone the development of my other module GroupMailer).
      The new module SnipWire offers full integration of the Snipcart Shopping Cart System into ProcessWire.
      Here are some highlights:
      simple setup with (optional) pre-installed templates, product fields, sample products (quasi a complete shop system to get started immediately) store dashboard with all data from the snipcart system (no change to the snipcart dashboard itself required) Integrated REST API for controlling and querying snipcart data webhooks to trigger events from Snipcart (new order, new customer, etc.) multi currency support self-defined/configurable tax rates etc. Development is already well advanced and I plan to release the module in the next 2-3 months.
      I'm not sure yet if this will be a "Pro" module or if it will be made available for free.
      I would be grateful for suggestions and hints!
      (please have a look at the screenshots to get an idea what I'm talking about)
       




×
×
  • Create New...