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

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

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

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 eelkenet
      Hi! I've created a small Inputfield module called InputfieldFloatRange which allows you to use an HTML5 <input type="range" ../> slider as an InputField. I needed something like this for a project where the client needs to be able to tweak this value more based on 'a feeling' than just entering a boring old number. Maybe more people can use this so I'm hereby releasing it into the wild.  
       
      What is it?
      The missing range slider Inputfield for Processwire. 
      What does it do?
      This module extends InputfieldFloat and allows you to use HTML5 range sliders for number fields in your templates.
      It includes a visible and editable value field, to override/tweak the value if required.  
      Features
      Min/max values Precision (number of decimals) Steps (Read more) Manual override of the selected value (will still adhere to the rules above) Usage
      Clone / zip repo Install FieldtypeFloatRange, this automatically installs the Inputfield Create new field of type `Float (range)` or convert an existing `Float`, `Integer` or `Text` field. To render the field's value simply echo `$page->field` Demo
      A field with Min=0, Max=1, Step=0.2, Precision=2

      Field with settings Min=0, Max=200, Step=0.25, Precision=2

       
      Todo
      Make the display-field's size configurable (will use the Input Size field setting)  Hopefully become redundant If it's usable for others I'll add it to the Modules list  
      Changelog
      v002
      - Fix issue where setting the step value to an empty value created problem with validation
      - Make the display-field optional 
      v001
      - Initial release
       
      Thanks!
       
       
    • 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. Field widths entered into the Template Field Widths inputfield are only applied if the Edit Template form is submitted with the Template Field Widths inputfield in an opened state. "Collapsed" is the recommended setting if you think you might also use core inputs for setting field widths in a template context. 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 adrian
      Tracy Debugger for ProcessWire
      The ultimate “swiss army knife” debugging and development tool for the ProcessWire CMF/CMS

       
      Integrates and extends Nette's Tracy debugging tool and adds 35+ custom tools designed for effective ProcessWire debugging and lightning fast development
      The most comprehensive set of instructions and examples is available at: https://adrianbj.github.io/TracyDebugger
      Modules Directory: http://modules.processwire.com/modules/tracy-debugger/
      Github: https://github.com/adrianbj/TracyDebugger
      A big thanks to @tpr for introducing me to Tracy and for the idea for this module and for significant feedback, testing, and feature suggestions.
    • By adrian
      This module allows you to automatically rename file (including image) uploads according to a configurable format
      This module lets you define as many rules as you need to determine how uploaded files will be named and you can have different rules for different pages, templates, fields, and file extensions, or one rule for all uploads. Renaming works for files uploaded via the admin interface and also via the API, including images added from remote URLs.   Github: https://github.com/adrianbj/CustomUploadNames
      Modules Directory: http://modules.processwire.com/modules/process-custom-upload-names/
      Renaming Rules
      The module config allows you to set an unlimited number of Rename Rules. You can define rules to specific fields, templates, pages, and file extensions. If a rule option is left blank, the rule with be applied to all fields/templates/pages/extensions. Leave Filename Format blank to prevent renaming for a specific field/template/page combo, overriding a more general rule. Rules are processed in order, so put more specific rules before more general ones. You can drag to change the order of rules as needed. The following variables can be used in the filename format: $page, $template, $field, and $file. For some of these (eg. $field->description), if they haven't been filled out and saved prior to uploading the image, renaming won't occur on upload, but will happen on page save (could be an issue if image has already been inserted into RTE/HTML field before page save). Some examples: $page->title mysite-{$template->name}-images $field->label $file->description {$page->name}-{$file->filesize}-kb prefix-[Y-m-d_H-i-s]-suffix (anything inside square brackets is is considered to be a PHP date format for the current date/time) randstring[n] (where n is the number of characters you want in the string) ### (custom number mask, eg. 001 if more than one image with same name on a page. This is an enhanced version of the automatic addition of numbers if required) If 'Rename on Save' is checked files will be renamed again each time a page is saved (admin or front-end via API). WARNING: this setting will break any direct links to the old filename, which is particularly relevant for images inserted into RTE/HTML fields. The Filename Format can be defined using plain text and PW $page variable, for example: mysite-{$page->path} You can preserve the uploaded filename for certain rules. This will allow you to set a general renaming rule for your entire site, but then add a rule for a specific page/template/field that does not rename the uploaded file. Just simply build the rule, but leave the Filename Format field empty. You can specify an optional character limit (to nearest whole word) for the length of the filename - useful if you are using $page->path, $path->name etc and have very long page names - eg. news articles, publication titles etc. NOTE - if you are using ProcessWire's webp features, be sure to use the useSrcExt because if you have jpg and png files on the same page and your rename rules result in the same name, you need to maintain the src extension so they are kept as separate files.
      $config->webpOptions = array(     'useSrcExt' => false, // Use source file extension in webp filename? (file.jpg.webp rather than file.webp) ); Acknowledgments
      The module config settings make use of code from Pete's EmailToPage module and the renaming function is based on this code from Ryan: http://processwire.com/talk/topic/3299-ability-to-define-convention-for-image-and-file-upload-names/?p=32623 (also see this post for his thoughts on file renaming and why it is the lazy way out - worth a read before deciding to use this module). 
       
       
      NOTE:
      This should not be needed on most sites, but I work with lots of sites that host PDFs and photos/vectors that are available for download and I have always renamed the files on upload because clients will often upload files with horrible meaningless filenames like:
      Final ReportV6 web version for John Feb 23.PDF

×
×
  • Create New...