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

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

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 5

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 Macrura
      PrevNextTabs Module
      Github: https://github.com/outflux3/PrevNextTabs
      Processwire helper modules for adding page navigation within the editor.
      Overview
      This is a very simple module that adds Previous and Next links inline with the tabs on the page editor. Hovering over the tab shows the title of the previous or next page (using the admin's built in jqueryUI tooltips.)
      Usage
      This module is typically used during development where you or your editors need to traverse through pages for the purpose of proofing, flagging and/or commenting. Rather than returning to the page tree or lister, they can navigate with these links.
      Warnings
      If you are using PW version 2.6.1 or later, the system will prevent you from leaving the page if you have unsaved edits.
      For earlier versions, to avoid accidentally losing changes made to a page that might occur if a user accidentally clicks on one of these, make sure to have the Form Save Reminder module installed.
      http://modules.processwire.com/modules/prev-next-tabs/
    • By Gadgetto
      SnipWire - Snipcart integration for ProcessWire
      Snipcart is a powerful 3rd party, developer-first HTML/JavaScript shopping cart platform. SnipWire is the missing link between Snipcart and the content management framework ProcessWire.
      With SnipWire, you can quickly turn any ProcessWire site into a Snipcart online shop. The SnipWire plugin helps you to get your store up and running in no time. Detailed knowledge of the Snipcart system is not required.
      SnipWire is free and open source licensed under Mozilla Public License 2.0! A lot of work and effort has gone into development. It would be nice if you could donate an amount to support further development:

      Status update links (inside this thread) for SnipWire development
      2020-03-21 -- SnipWire 0.8.5 (beta) released! Improves SnipWires webhooks interface and provides some other fixes and additions 2020-03-03 -- SnipWire 0.8.4 (beta) released! Improves compatibility for Windows based Systems. 2020-03-01 -- SnipWire 0.8.3 (beta) released! The installation and uninstallation process has been heavily revised. 2020-02-08 -- SnipWire 0.8.2 (beta) released! Added a feature to change the cart and catalogue currency by GET, POST or SESSION param 2020-02-03 -- SnipWire 0.8.1 (beta) released! All custom classes moved into their own namespaces. 2020-02-01 -- SnipWire is now available via ProcessWire's module directory! 2020-01-30 -- SnipWire 0.8.0 (beta) first public release! (module just submitted to the PW modules directory) 2020-01-28 -- added Custom Order Fields feature (first SnipWire release version is near!) 2020-01-21 -- Snipcart v3 - when will the new cart system be implemented? 2020-01-19 -- integrated taxes provider finished (+ very flexible shipping taxes handling) 2020-01-14 -- new date range picker, discount editor, order notifiactions, order statuses, and more ... 2019-11-15 -- orders filter, order details, download + resend invoices, refunds 2019-10-18 -- list filters, REST API improvements, new docs platform, and more ... 2019-08-08 -- dashboard interface, currency selector, managing Orders, Customers and Products, Added a WireTabs, refinded caching behavior 2019-06-15 -- taxes provider, shop templates update, multiCURL implementation, and more ... 2019-06-02 -- FieldtypeSnipWireTaxSelector 2019-05-25 -- SnipWire will be free and open source Plugin Key Features
      Fast and simple store setup Full integration of the Snipcart dashboard into the ProcessWire backend (no need to leave the ProcessWire admin area) Browse and manage orders, customers, discounts, abandoned carts, and more Multi currency support Custom order and cart fields Process refunds and send customer notifications from within the ProcessWire backend Process Abandoned Carts + sending messages to customers from within the ProcessWire backend Complete Snipcart webhooks integration (all events are hookable via ProcessWire hooks) Integrated taxes provider (which is more flexible then Snipcart own provider) Useful Links
      SnipWire in PW modules directory SnipWire Docs (please note that the documentation is a work in progress) SnipWire @GitHub (feature requests and suggestions for improvement are welcome - I also accept pull requests) Snipcart Website  
      ---- INITIAL POST FROM 2019-05-25 ----
       
    • By horst
      Croppable Image 3
      for PW 3.0.20+
      Module Version 1.2.0
      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.
       
      - + - + - + - + - + - + - + - + - + - NEWS - 2020/03/19 - + - + - + - + - + - + - + - + - + - 
      There is a new Version in the pipe, that supports WebP too: 
       
      - + - + - + - + - + - + - + - + - + - NEWS - 2020/03/19 - + - + - + - + - + - + - + - + - + - 
       
       
      -------------------------------------------------------------------------
       
      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 Robin S
      Inspired by a recent question.
      Image Crop Ratios
      Allows preset aspect ratios to be defined per image field for the ProcessWire image crop tool.
      The module adds a select dropdown to the crop tool. Choose an aspect ratio and the crop area will be fixed to that ratio.
      Screencast

      Installation
      Install the Image Crop Ratios module.
      Configuration
      Default aspect ratios for all image fields can be defined in the module config. Aspect ratios for specific image fields can be defined on the Input tab of the field settings. You can override the ratio settings in template context if needed. Insert a hyphen as the first item in the ratio settings unless you want to force a ratio to be applied to the crop tool. The hyphen represents a blank option that allows a free crop area to be drawn. Usage
      Click the "Crop" link on the details view of an image thumbnail. Click the "Crop" icon at the top of the editor window. Choose an option from the "Ratio" select dropdown.  
      https://github.com/Toutouwai/ImageCropRatios
      https://modules.processwire.com/modules/image-crop-ratios/
×
×
  • Create New...