bernhard

RockFinder - Highly Efficient and Flexible SQL Finder Module

Recommended Posts

WHY?

This module was built to fill the gap between simple $pages->find() operations and complex SQL queries.

The problem with $pages->find() is that it loads all pages into memory and that can be a problem when querying multiple thousands of pages. Even $pages->findMany() loads all pages into memory and therefore is a lot slower than regular SQL.

The problem with SQL on the other hand is, that the queries are quite complex to build. All fields are separate tables, some repeatable fields use multiple rows for their content that belong to only one single page, you always need to check for the page status (which is not necessary on regular find() operations and therefore nobody is used to that).

In short: It is far too much work to efficiently and easily get an array of data based on PW pages and fields and I need that a lot for my RockGrid module to build all kinds of tabular data.


Basic Usage

getObjects.png.33c56b6030f7c49cfbd88591c016463d.png

 

Docs & Download

https://modules.processwire.com/modules/rock-finder/

https://github.com/BernhardBaumrock/RockFinder

 

Changelog

  • Like 11
  • Thanks 2

Share this post


Link to post
Share on other sites

If anybody is already using RockFinder make sure you have the latest version (1.0.0). Just pushed an update because the previous version didn't work with checkbox selectors because I only used the WHERE part of the original query. I switched now to a join, that should work in 100% of the cases:

screencapture-360-test-admin-setup-rockfindertester-2018-05-16-17_22_57.thumb.png.c7b37ee8d866377947bfe6e414a05999.png

  • Like 1

Share this post


Link to post
Share on other sites

Please submit this gold module to the modules directory so we can update it in one click 🤩

 

Edit:  Just re-installed the module from scratch, I got an error when I try to access the RockFinder tester, see the attached screenshot :

rockfindertrace.thumb.png.4b147201aef348e4bbb224ca5da2c227.png

 

Share this post


Link to post
Share on other sites

Hi @flydev sorry but I cannot reproduce this bug. I need step by step instructions, pw version etc...

Quote

Please submit this gold module to the modules directory so we can update it in one click 🤩

It is already there: https://modules.processwire.com/modules/rock-finder/ not sure when it updates, though (it's still version 7 instead of 1.0.0). 

Share this post


Link to post
Share on other sites
  • ProcessWire 3.0.98
  • PHP 7.0.10

I just installed the module from your Gitlab repo then I clicked Setup > RockFinder tester and the error happen.

I think you can't reproduce this error because you have InputfieldRockGrid (called at line 50 in ProcessRockFinder.module.php) but its not shipped with module. That explain the warning.

 

wow those smileys are big lol

 

Share this post


Link to post
Share on other sites

please try to install the regular module before the process module

1 minute ago, flydev said:

I think you can't reproduce this error because you have InputfieldRockGrid (called at line 50 in ProcessRockFinder.module.php) but its not shipped with module.

No, I have a clean installation only with AOS + Tracy and it worked

Share this post


Link to post
Share on other sites
  1. Installed a stock ProcessWire 3.0.102
  2. I Downloaded the module RockFinder 1.0.0
  3. I installed RockFinder
  4. I installed ProcessRockFinder
  5. I clicked Setup > RockFinder tester

I have the same warning / error.

Just for testing I replaced InputfieldRockGrid on line 50 by InputfieldTextarea, I can access the process module.

  • Like 1

Share this post


Link to post
Share on other sites

Ah, sorry @flydev I missed that this happens when clicking on the module's page and not during installation.

I pushed an update that does the check if RockGrid is available, otherwise it does not add this field (and shows only SQL). The process module is not really helpful without RockGrid, I know, but the day will come where I can release RockGrid 😉

  • Like 1

Share this post


Link to post
Share on other sites

okay 🙂

 

PS: to update the modules directory, just re-submit the module and the module will be updated automatically in a second.

  • Like 2

Share this post


Link to post
Share on other sites
On 5/16/2018 at 6:56 PM, flydev said:

PS: to update the modules directory, just re-submit the module and the module will be updated automatically in a second.

this does not seem to work. current module version is 1.0.2 and https://modules.processwire.com/modules/rock-finder/ still shows 1.0.0

Share this post


Link to post
Share on other sites
1 hour ago, kongondo said:

Maybe it only works with GitHub hosted modules? 

That what I was thinking.

Share this post


Link to post
Share on other sites

Yeah, coz it's not even picking up the README.

Share this post


Link to post
Share on other sites

just added the possibility to add custom sql statements easily. that way you can easily do "reverse queries", for example show all projects that have the current page selected in a page-reference-field:2018-05-28--11-00-32.png.435e4e37d618f136e6ea6e276dd939a4.png

 

Aggregations like sum(), min(), max() should also be easily possible like this, though they might not be as efficient as doing a group_by manually on the resulting sql.

  • Like 2

Share this post


Link to post
Share on other sites

Just released RockGrid, so RockFinder Tester is now usable in this module 🙂 

  • Like 1

Share this post


Link to post
Share on other sites

Pushed a little Update for basic support of options fields (using the same sql queries as file fields).

But I want to share something I needed today to keep my requests simple and my code clean: You can quite easily join two finders where manipulating the SQL would maybe quite complex:

$this->rg->assets->add($this->config->paths->siteModules . 'FieldtypeRockGrid/lib/moment.min.js');
$this->rg->assets->add($this->config->paths->siteModules . 'FieldtypeRockGrid/lib/currency.min.js');

$efforts = new RockFinder('template=effort', [
  'title',
  // fields
]);

$projects = new RockFinder('template=project', [
  'title',
  // fields
]);

$sql = 'SELECT
    efforts.*,
    projects.id AS projectid,
    projects.title AS projectname,
    projects.project_status AS projectstatus
  FROM (' . $efforts->getSQL() . ') AS efforts
  LEFT JOIN field_project_efforts AS ef ON ef.data = efforts.id
  LEFT JOIN (' . $projects->getSQL() . ') AS projects ON projects.id = ef.pages_id /* projectid */
  ';

$this->setData($sql);

Result (with RockGrid and some JavaScript, of course):

2018-08-10--23-36-10.thumb.png.ea0078fd11b2d93526a8122fe249217d.png

  • Like 3

Share this post


Link to post
Share on other sites

Added support for joining multiple finders. You can even join finders on columns of previously joined finders 🙂

In this example I'm joining projects (finder1) and their related clients (single page reference field of project) and then I join the referral contact of the project's client based on the "camefrom" id of finder2:

join.png 

  • Like 2

Share this post


Link to post
Share on other sites

Hi @bernhard. I try to get the path to the result pages but also want a fast query, so I am using

 $found_in_product_title = new \ProcessWire\RockFinder("title|subheadline%=$qs, limit=25, has_parent!=2", ['title', 'name', 'path' => function ($page) {return $page->path;}]);
    $found_in_product_title = $found_in_product_title->getArrays();

but the path is empty.

image.png.04c7a27f79bd438342c352be6d4787d5.png

If I use

$found_in_product_title = $found_in_product_title->getObjects();

instead, the path is returned.

How can I get an array returned which includes the path to the page?

Thanks in advance.

  • Thanks 1

Share this post


Link to post
Share on other sites

thanks @jmartsch that was a bug and I fixed it in version 1.0.7

edit: note that as soon as you are using callback functions like you do in your example the find will not be more performant than any other $pages->find() command. To make it more performant you could populate a hidden field with the page's path on every save and then just list this field in the finder call.

Share this post


Link to post
Share on other sites

Hi,
Still digging in the module. I have 2 questions:

  1. Does it have a limit of columns to be added? For now, I have 22 columns + 17 columns added with ->addField. It's probably too much: it works but it's really, really slow (minutes) and freeze everything else. Strangely I just remove a couple of fields (~5-10) and it's appear rather quickly. All those fields belongs to 1 single template (so just left joins). I have ~200 rows.
  2. In fieldtypes supported there is a file named "pagestable". I guess it's not "Profields: Page table"? Anyway it doesn't work with ->addField, it just print id. I search for another syntax, but I couldn't find it.

Thanks again

Mel

Share this post


Link to post
Share on other sites

Hi mel,

  1. MySQL has a maximum number of 61 joined tables: https://dev.mysql.com/doc/refman/5.7/en/joins-limits.html
    Why are you adding those fields via ->addField and not just pass them in the fields array of the finder? Can you show your code and the resulting sql please?
  2. I'm not sure. Any chance you can send me a site profile to help you?

Share this post


Link to post
Share on other sites

I created a demo-installation of RockGrid/RockFinder that I want to share soon that shows several examples of RockFinder/RockGrid

Here's one example that @jmartsch requested support for - a grid listing the sum of all pages that reference this page:

UgzNqDD.png

the residents can assign a house with a single pagefield:

P4c9XLn.png  

The php file for the grid (returning the final finder's sql, that's why I post it here) looks like this:

<?php namespace ProcessWire;

// the base table contains all houses
// https://i.imgur.com/e5bC4sA.png
$houses = new RockFinder('template=house', ['title']);

// then we create another table containing all residents that have a living assigned
// https://i.imgur.com/zYXEVIL.png
$residents = new RockFinder('template=person|cat|dog,livesin.count>0', ['title', 'livesin']);

// we join both tables and get a combined table
// https://i.imgur.com/TE0vEPd.png
$houses->join($residents, 'resident', ['livesin' => 'id']);

// then we can modify the resulting SQL to our needs
// https://i.imgur.com/UgzNqDD.png
$sql = $houses->getSQL();
$sql = "SELECT id, title, count(resident_id) as numresidents FROM ($sql) as tmp GROUP BY resident_livesin";

// set the data
// to check everything i created a test-grid where we can filter for the house title
// https://i.imgur.com/9pdlYVz.png
$this->setData($sql);

The screenshots of the comments are here:

e5bC4sA.png

--

zYXEVIL.png

--

TE0vEPd.png

--

UgzNqDD.png

--

9pdlYVz.png

--

The grid in the last example called "residents" is as simple as that:

<?php namespace ProcessWire;
$residents = new RockFinder('template=person|cat|dog,livesin.count>0', ['title']);
$residents->addField('livesin', ['title']);

$this->setData($residents);

 

As you can see this somewhat complex example can be done in only 6 lines of codes (same example as above, just without comments):

$houses = new RockFinder('template=house', ['title']);
$residents = new RockFinder('template=person|cat|dog,livesin.count>0', ['title', 'livesin']);
$houses->join($residents, 'resident', ['livesin' => 'id']);
$sql = $houses->getSQL();
$sql = "SELECT id, title, count(resident_id) as numresidents FROM ($sql) as tmp GROUP BY resident_livesin";
$this->setData($sql);

 

  • Like 3

Share this post


Link to post
Share on other sites

@bernhard Du bist der Beste 🙂

I will try to optimize my queries tomorrow based on your post.

Also some more questions are coming up as I dive in further.

  • Like 1

Share this post


Link to post
Share on other sites

Why is it that creating page objects is so much slower/memory intensive than a multidimensional array? I could understand if the pages were loading all their fields, but most fields are not auto-join 🤔

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 MoritzLost
      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 adrian
      This module provides a way to rapidly generate Page fields and the required templates and pages for use as a drop down select (or any other Page field type).
      This module will let you create a full page field setup in literally a few seconds 
      To use, run Page Field Select Creator from the Setup Menu
      Enter a Field Title, eg: Room Types Select Options - These will become the child pages that will populate the page field select options. There are two different options.
       
      Option 1. TITLE FIELD ONLY - enter one option per line, eg:
       
      Single
      Double
      Suite
       
       
      Option 2. MULTIPLE FIELDS - the first line is used for the field names and the first field must be 'Title'. Subsequent lines are the values for the fields, eg:
       
      Title, Number of Beds, Number of People, Kitchen Facilities
      Single, 1, 1, Fridge Only
      Double, 2, 2, Fridge Only
      Suite, 3, 6, Full Kitchen
        Choose the parent where the page tree of options will be created, eg a hidden "Options" parent page Select a "Deference in API as" option depending on your needs Choose the input field type Check whether "Allow new pages to be created from field?" should be enabled. As an example, if you entered "Room Types" as the field title, you would end up with all of the following automatically created:
      a fully configured page field called: room_types MULTIPLE FIELDS OPTION - 3 additional fields - number_of_beds, number_of_people, kitchen a parent template called: room_types a child template called: room_types_items (with either just a title field, or with the 3 additional fields as well) a parent page called: Room Types a series of child pages named and titled based on the per line entries in the Select Options textarea The templates are configured such that the "room_types_items" child template can only have the main "room_types" template as a parent, and vice versa.

      Then all you have to do is add the newly created page field to any template you want and you're ready to go!
       
      You can grab it from:
       
      Modules directory: http://modules.processwire.com/modules/process-page-field-select-creator/
      Github: https://github.com/adrianbj/ProcessPageFieldSelectCreator
       

    • By BitPoet
      MediaLibrary
      Update: MediaLibrary can now be found in the official module list.
      Out of necessity, I've started to implement a simple media library module.
      The basic mechanism is that it adds a MediaLibrary template with file and image fields. Pages of this type can be added anywhere in the page tree.
      The link and image pickers in CKEditor are extended to allow quick selection of library pages from dropdowns. In the link picker this happens in the MediaLibrary tab, where you can also see a preview of the selected image. In the image picker, simply select a library from the dropdown at the top, everything else is handled by standard functionality.
      I've put the code onto github. This module is compatible with ProcessWire 3.
      Steps to usage:
      Download the module's zip from github (switch to the pw3 branche beforehand if you want to test on PW 3.x) and unpack it into site/modules Click "Modules" -> "Refresh" in the admin Click "Install" for MediaLibrary For testing, create a page with the MediaLibrary template under home (give it an expressive title like 'Global Media') and add some images and files Edit a differnt page with a CKEditor field and add a link and an image to see the MediaLibrary features in action (see the screencap for details) Optionally, go into the module settings for MediaLibrary Note: this module is far from being as elaborate as Kongondo's Media Manager (and doesn't plan to be). If you need a feature-rich solution for integrated media management, give it a look.
      Feel free to change the settings for MediaFiles and MediaImages fields, just keep the type as multiple.
      There are some not-so-pretty hacks for creating and inserting the correct markup, which could probably be changed to use standard input fields, though I'm a bit at a loss right now how to get it to work. I've also still got to take a look at error handling before I can call it fit for production. All feedback and pointers are appreciated (that's also why I post this in the development section).

      Edit 09.03.2016 / version 0.0.4: there's now also a "Media" admin page with a shortcut to quickly add a new library.

      Edit 01.05.2016:
      Version 0.0.8:
      - The module now supports nested media libraries (all descendants of eligible media libraries are also selectable in link/image picker).
      - There's a MediaLibrary::getPageMediaLibraries method you can hook after to modify the array of available libraries.
      - You can switch between (default) select dropdowns or radio boxes in the module configuration of MediaLIbrary to choose libraries.
      Edit 10.10.2018:
      Version 0.1.3:
      - Dropped compatibility for ProcessWire legacy versions by adding namespaces
      - Allow deletion of libraries from the Media overview admin page
      - Added an option to hide media libraries from the page tree (optionally also for superusers)
    • By Robin S
      This module corrects a few things that I find awkward about the "Add New Template" workflow in the PW admin. I opened a wishlist topic a while back because it would good to resolve some of these things in the core, but this module is a stopgap for now.
      Originally I was going to share these as a few standalone hooks, but decided to bundle them together in a configurable module instead.
      Add Template Enhancements
      A module for ProcessWire CMS/CMF. Adds some efficiency enhancements when adding or cloning templates via admin.

      Features
      Derive label from name when new template added: if you like to give each of your templates a label then this feature can save some time. The label can be added automatically when templates are added in admin, in admin/API, or not at all. There are options for underscore/hyphen replacement and capitalisation of the label. Edit template after add: when adding only a single template, the template is automatically opened for editing after it is added. Copy field contexts when cloning: this copies the field contexts (a.k.a. overrides such as column width, label and description) from the source template to the new template when using the "Duplicate/clone this template?" feature on the Advanced tab. Copy field contexts when duplicating fields: this copies the field contexts if you select the "Duplicate fields used by another template" option when adding a new template. Usage
      Install the Add Template Enhancements module.
      Configure the module settings according to what suits you.
       
      https://github.com/Toutouwai/AddTemplateEnhancements
      https://modules.processwire.com/modules/add-template-enhancements/
    • By Mike Rockett
      As I mentioned in this issue, I've create a new textformatter for ParsedownExtraPlugin, which adds some oomph to your markdown.
      Repo: Parsedown Extra Plugin
      Unlike the built-in textformatter for Parsedown and Parsedown Extra, this should be used when you want to use Extra with additional configuration/customisation.
      Some examples:
      ### Test {.heading} - A [external link](https://google.com/){.google} with `google` as a class that opens in a new tab if the config property is set. - [Another link](/page){target=_blank} that opens in a new tab even though it isn't external. ```html .html <p>Test</p> ``` There's some config options available to you, such as setting attributes on all/external images and links, setting table and table-cell alignment classes, adjusting footnote classes and IDs, adding <code> attributes to their parent <pre> elements, and changing the <code> class if your syntax highlighter does not use language-*.
      I was thinking about adding the ability to make links open in a new tab by appending a plus to the link syntax, but only external links should be opening in a new tab anyway. Further, this would add extra, unnecessary processing time.
      Please let me know if you bump into any problems. ☺️