bernhard

RockFinder - Highly Efficient and Flexible SQL Finder Module

Recommended Posts

@bernhard I've finally had a chance to try out your module tonight for a project where we're loading pages into a large data table (200+ rows) and were hitting a wall.

Using RockFinder I now have the initial page load down to ~2 seconds, down from ~7+ seconds! This is a fantastic module, Bernhard. It looks like it's really well thought out and has some really powerful features for building queries. I love how it extends PW's native selectors and allows you to return standard objects, making it easier to substitute this in for a regular $pages->find. Thank you for making this!

I think I can answer my own question now... The main issue with creating Page objects is that page instantiation requires a trip back to the database. The initial $pages->find converts a selector into a SQL query which returns an array of matching page IDs. Then those IDs are used to go back to the database and get the pages (or pull them from cache if they're already loaded). Then for any page field requested that isn't auto-join, an additional database query is required. If you're looping through a lot of pages, that's a lot of DB queries!

It seems like there might be a way to provide the functionality of RockFinder in the native PW core, as an option when loading pages. You would still end up with Page objects in the end (which in my case would be a huge boon since I like to extend the Page class with a custom class and methods for each template), but we could skip that second trip to the database (getting pages by IDs) if we could just tell PW which fields we wanted it to get up front. After that, any additional fields we didn't specify could be loaded with another trip to the DB, as they are now.

That being said, I'm sure @ryan has a good reason for that second trip to the DB. But it seems like there must be a way that we could improve the speed of native pages, even if it is a hidden/advanced option with some caveats.

 

One minor complaint: I noticed is that the module seems to fail silently and return nothing when it can't find one of the fields. It would be good to throw an exception to make this easier to debug.

Edit: Another thought... Is there a reason not to use WireData and WireArray for the objects returned from RockFinder, in place of an StdObject? This would allow you to use WireArray's built in sorting and selecting features on the result set:

$results = $this->database->query($this->getSql());
if($array) {
	$objects = $results->fetchAll(\PDO::FETCH_ASSOC);
}
else {
	$objects = $results->fetchAll(\PDO::FETCH_CLASS, '\ProcessWire\WireData');
	$objects = (new WireArray())->import($objects);
}

 

  • Like 4
  • Thanks 1

Share this post


Link to post
Share on other sites
6 hours ago, thetuningspoon said:

Using RockFinder I now have the initial page load down to ~2 seconds, down from ~7+ seconds! This is a fantastic module, Bernhard. It looks like it's really well thought out and has some really powerful features for building queries. I love how it extends PW's native selectors and allows you to return standard objects, making it easier to substitute this in for a regular $pages->find. Thank you for making this!

Great to hear that. I wouldn't call it well thought out, though. When I started building it I had far too less knowledge about what's going on on the regular page finder. I think the module could be improved a lot on several sides. But it get's all the jobs done for me and I can build RockGrids with thousands of pages that load in some ms compared to loading times above 30s that I had before.

6 hours ago, thetuningspoon said:

It seems like there might be a way to provide the functionality of RockFinder in the native PW core, as an option when loading pages.

I'd love to have a better version of RockFinder in the core 🙂 

6 hours ago, thetuningspoon said:

Another thought... Is there a reason not to use WireData and WireArray for the objects returned from RockFinder, in place of an StdObject? This would allow you to use WireArray's built in sorting and selecting features on the result set:

Sounds like a good idea, I'll have a look 🙂 

 

PS: 2 seconds still sounds very slow for 200 rows. May I see your finder setup?

Share this post


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

PS: 2 seconds still sounds very slow for 200 rows. May I see your finder setup?

You're right... I was not counting some of the pages involved. There are at least 2 to 3 times that many.

Also, I am counting the entire time from request to first response (not using a debug timer)

  • Like 1

Share this post


Link to post
Share on other sites

A bit of an update after some more experimentation tonight. 

I added pages.templates_id as a field always selected in the RockFinder results, and then attempted to use the $pages->newPage() method to take the results of the SQL query and convert them into a PageArray of Page objects. This worked, thus eliminating that second trip to the database I mentioned (and also bypassing the page cache and probably some other nice features of the normal page creation process).

Unfortunately, this slowed the whole thing way down again. So I'm thinking now that it is something else about constructing Pages that is slow. Maybe generating the page path or some other properties are the problem. Perhaps I need to load up some additional fields up front. Will have to test more.

WireData/WireArray works great, though.

  • Like 3
  • Thanks 1

Share this post


Link to post
Share on other sites

Hey @thetuningspoon I just tried your suggestion about the WireArray/WireData... It's awesome 🙂 

I did it a little differently though, because my RockGrids don't work the way you implemented it. v1.0.9 introduces a new method: $finder->getWireArray(); This will return a WireArray containing WireData objects just as you suggested:

LNNrbFq.png

For anybody wondering what this update could be used for: You can use all the pw api magic on that objects:

QTuCyqf.png

 

New version is on GitHub - I'll change my public projects to github to have the PW modules directory automatically in sync: https://github.com/BernhardBaumrock/RockFinder/commit/46377ba6ea399f8557893684da3c3dd486b2c44e

  • Like 7

Share this post


Link to post
Share on other sites

Just for reference, I think the easiest way to do aggregations at the moment is creating a regular finder and taking the resulting SQL as subquery and modifying it to your needs:

euOeXd2.png

0lbHxb8.png 

 

And if you need it in your code (not in the RockFinder Tester) you need to set the SQL via $finder->sql:

rqeNQv9.png

  • Like 4

Share this post


Link to post
Share on other sites

Here's a little Tutorial of how to get pages and their parent's titles requested by @mel47

The page structure:

Yq3bOyg.png

The initial basic Finder:

sgUdo58.png

The easy and inefficient way:

8W17HzH.png

This will load all pages in memory and be slow when you have lots of pages!

The a little more complicated but far more efficient way. First, we prepare the finder to join:

4UaeZpm.png

Then we join that finder to the initial finder:

yB2UNpT.png

Then just hide those two unnecessary columns in your final grid:

document.addEventListener('RockGridItemBeforeInit', function(e) {
  if(e.target.id != 'RockGridItem_yourgrid') return;
  var grid = RockGrid.getGrid(e.target.id);
  var colDefs = grid.gridOptions.columnDefs;
  var col;
  
  // all your grid's frontend settings

  grid.getColDef('parent_id').hide = true;
  grid.getColDef('cat_id').hide = true;
});

 

  • Like 2

Share this post


Link to post
Share on other sites

Thanks!

Was able to successfully display children/parent pages. Looks like this :

$contact = new RockFinder('template=contact',  ['title', 'parent_id']);

$org = new RockFinder('template=organisme', ['title']);
$contact->join($org, 'contact', ['id' => 'parent_id']);
return $contact;

However, I can't add any other fields like I normally do:

$contact = new RockFinder('template=contact, include=all',  ['title', 'parent_id', 'nom', 'prenom']);

I guess it have to be done after the join or have to be written in a different way? I couldn't find any example on your documentation similar to that.

Thanks again!

Mel

Share this post


Link to post
Share on other sites

Thanks. Effectively working, I messed up titles of fields. Feel a little bit noob... 🤫

  • Like 1

Share this post


Link to post
Share on other sites

I had a quite some trouble getting this to work with the content from a ProFields Table, as this does not work the same as with a Repeater or PageTable.
But I am happy to say that treating those fields as a 'file' works, by setting this in the options array.  Ie: 

<?php 
	$finder = new RockFinder("template=template_name,limit=10", ["title", "some_field_name"]);
	$field = $finder->addField("my_table_field", ["column_name", "other_column"], ["type" => "file"]);
	$field->seperator = ", ";

 

  • 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 Robin S
      Another little admin helper module...
      Template Field Widths
      Adds a "Field widths" field to Edit Template that allows you to quickly set the widths of inputfields in the template.

      Why?
      When setting up a new template or trying out different field layouts I find it a bit slow and tedious to have to open each field individually in a modal just to set the width. This module speeds up the process.
      Installation
      Install the Template Field Widths module.
      Config options
      You can set the default presentation of the "Field widths" field to collapsed or open. You can choose Name or Label as the primary identifier shown for the field. The unchosen alternative will become the title attribute shown on hover. You can choose to show the original field width next to the template context field width.  
      https://github.com/Toutouwai/TemplateFieldWidths
      https://modules.processwire.com/modules/template-field-widths/
    • By horst
      Croppable Image 3
      for PW 3.0.20+
      Module Version 1.1.16
      Sponsored by http://dreikon.de/, many thanks Timo & Niko!
      You can get it in the modules directory!
      Please refer to the readme on github for instructions.
       
      -------------------------------------------------------------------------
       
      Updating from prior versions:
       
      Updating from Croppable Image 3 with versions prior to 1.1.7, please do this as a one time step:
      In the PW Admin, go to side -> modules -> new, use "install via ClassName" and use CroppableImage3 for the Module Class Name. This will update your existing CroppableImage3 module sub directory, even if it is called a new install. After that, the module will be recogniced by the PW updater module, what makes it a lot easier on further updates.
      -------------------------------------------------------------------------
       
      For updating from the legacy Thumbnail / CropImage to CroppableImage3 read on here.
       
      -------------------------------------------------------------------------
       
    • By MoritzLost
      UPDATE: I have published a stable version of this module!
      Discussion thread:
      Github: https://github.com/MoritzLost/TextformatterPageTitleLinks
      ---
      Hello there,
      I'm working on a tiny textformatter module that searches the text for titles of other pages on your site and creates hyperlinks to them. I'm not sure if something like this exists already, but I haven't found anything in the module directory, so I wrote my own solution 🙂
      It's not properly tested yet and is still missing some functionality I would like to implement, so at the moment it should be considered in BETA. Features include limiting the pages that will get searched by template, and adding a custom CSS class to the generated hyperlinks. As I'm writing this I noticed that it will probably include unpublished and hidden pages at the moment, so yeah ... it's still in development alright 😅
      You can download the module from Github:
      https://github.com/MoritzLost/TextformatterPageTitleLinks
      There's some more information in the readme as well.
      Anyway, let me know what you think! I'm happy about any feedback, possible improvements or ideas on how to improve the module. Cheers.
    • By blad
      Hi guys!
      I just uploaded a module to explore files based on elFinder. By default it will show the "Files" folder.
      Screenshots:

      Video:
       
      To do:
       More options To fix:
       The function of rotating or scaling an image fails  Image editors V 1.01 (view issue)
      Fixed the bug working with the Multi-Language support ( translation of folders ). Fixed the name of elfinder.en  Github:
      https://github.com/LuisSantiago/ProcessElFinder/
      I hope you like it.
    • By BitPoet
      I'm really in love with FormBuilder, but the one thing missing to match all my end users' expectations were repeatable field groups. Think repeaters, in ProcessWire terms. Our primary application of PW is our corporate intranet, so "lines" of fields are quite common in the forms I build. We have all kinds of request forms where the information for a varying number of colleagues needs to be entered (from meal order to flight booking request) and where it is simply impractical to send a form for each, and I don't want to clutter my forms with multiple instances of fields that may only get used ten percent of the time.
      That's why I started to build FormBuilderMultiplier (link to GitHub).
      What it does:
      Adds an option to make a regular Fieldgroup repeatable Lets you limit the number of instances of a Fieldgroup on the form Adds an "Add row" button the form that adds another instance of the Fieldgroup's fields Adds a counter suffix at the end of every affected field's label Stores the entered values just like regular fields Makes the entered values available in preview and email notifications Supports most text based fields, textareas and selects (really, I haven't had enough time to test all the available choices yet) What it doesn't do (yet):
      Support saving to ProcessWire pages (i.e. real Repeaters) I haven't tested all the validation stuff, Date/Time inputs etc. yet, but since I'm utterly swamped with other stuff at work, I didn't want to wait until I have it polished. Any feedback is welcome. There might also be some issues with different output frameworks that I haven't encountered yet. The forms I work with mostly use UIKit.
      Status:
      Still alpha, so test well before using it in the field.
      Known issues:
      When rows are added, the form's iframe needs to be resized, which isn't completely clean yet.
      How it works:
      The Fieldgroup settings are added through regular hooks, as is the logic that adds the necessary field copies for processing the form and displaying previews.
      "Multiplied" field instances are suffixed with _NUM, where NUM is an incremental integer starting from 1. So if you have add two fields named "surname" and "givenname" to a fieldgroup and check the "multiply" checkbox, the form will initially have "surname_1" and "givenname_1" field (I'm still considering changing that to make the risk to shoot oneself into the foot by having a regular "surname_1" field somewhere else in the form less likely).
      When a "row" is added, the first row is cloned through JS and the counter in the fields' IDs, names and "for" attributes as well as the counter in the label are incremented before appending the copies to the Fieldset container in the form.
      To keep backend and frontend in sync, a hidden field named [name of the fieldset]__multiplier_rows is added to the form. Both the backend and the frontend script use this to store and retrieve the number of "rows".
      ToDo:
      Naturally, add the option to store the data in real repeaters when saving to pages. Do a lot of testing (and likely fixing). Make a few things (like the "Add row" button label etc.) configurable in field(set) context. Add a smooth API to retrieve the multiplied values as WireArrays. The mandatory moving screenshot: