Jump to content

RockFinder - Highly Efficient and Flexible SQL Finder Module

Recommended Posts


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



Docs & Download





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


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



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


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', [
  // fields

$projects = new RockFinder('template=project', [
  // fields

$sql = 'SELECT
    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 */


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


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


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


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

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


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:


the residents can assign a house with a single pagefield:


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

The screenshots of the comments are here:











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']);



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


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

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 dimitrios
      this module can publish content of a Processwire page on a Facebook page, triggered by saving the Processwire page.
      To set it up, configure the module with a Facebook app ID, secret and a Page ID. Following is additional configuration on Facebook for developers:
      Minimum Required Facebook App configuration:
      on Settings -> Basics, provide the App Domains, provide the Site URL, on Settings -> Advanced, set the API version (has been tested up to v3.3), add Product: Facebook Login, on Facebook Login -> Settings, set Client OAuth Login: Yes, set Web OAuth Login: Yes, set Enforce HTTPS: Yes, add "http://www.example.com/processwire/page/" to field Valid OAuth Redirect URIs. This module is configurable as follows:
      Templates: posts can take place only for pages with the defined templates. On/Off switch: specify a checkbox field that will not allow the post if checked. Specify a message and/or an image for the post.
      edit the desired PW page and save; it will post right after the initial Facebook log in and permission granting. After that, an access token is kept.
      PW module directory: http://modules.processwire.com/modules/auto-fb-post/ Github: https://github.com/kastrind/AutoFbPost   Note: Facebook SDK for PHP is utilized.

    • By kongondo
      FieldtypeRuntimeMarkup and InputfieldRuntimeMarkup
      Modules Directory: http://modules.processwire.com/modules/fieldtype-runtime-markup/
      GitHub: https://github.com/kongondo/FieldtypeRuntimeMarkup
      As of 11 May 2019 ProcessWire versions earlier than 3.x are not supported
      This module allows for custom markup to be dynamically (PHP) generated and output within a page's edit screen (in Admin).
      The value for the fieldtype is generated at runtime. No data is saved in the database. The accompanying InputfieldRuntimeMarkup is only used to render/display the markup in the page edit screen.
      The field's value is accessible from the ProcessWire API in the frontend like any other field, i.e. it has access to $page and $pages.
      The module was commissioned/sponsored by @Valan. Although there's certainly other ways to achieve what this module does, it offers a dynamic and flexible alternative to generating your own markup in a page's edit screen whilst also allowing access to that markup in the frontend. Thanks Valan!
      Although access to ProcessWire's Fields' admin pages is only available to Superusers, this Fieldtype will evaluate and run the custom PHP Code entered and saved in the field's settings (Details tab). Utmost care should therefore be taken in making sure your code does not perform any CRUD operations!! (unless of course that's intentional) The value for this fieldtype is generated at runtime and thus no data is stored in the database. This means that you cannot directly query a RuntimeMarkup field from $pages->find(). Usage and API
      Enter your custom PHP snippet in the Details tab of your field (it is RECOMMENDED though that you use wireRenderFile() instead. See example below). Your code can be as simple or as complicated as you want as long as in the end you return a value that is not an array or an object or anything other than a string/integer.
      FieldtypeRuntimeMarkup has access to $page (the current page being edited/viewed) and $pages. 
      A very simple example.
      return 'Hello'; Simple example.
      return $page->title; Simple example with markup.
      return '<h2>' . $page->title . '</h2>'; Another simple example with markup.
      $out = '<h1>hello '; $out .= $page->title; $out .= '</h1>'; return $out; A more advanced example.
      $p = $pages->get('/about-us/')->child('sort=random'); return '<p>' . $p->title . '</p>'; An even more complex example.
      $str =''; if($page->name == 'about-us') { $p = $page->children->last(); $str = "<h2><a href='{$p->url}'>{$p->title}</a></h2>"; } else { $str = "<h2><a href='{$page->url}'>{$page->title}</a></h2>"; } return $str; Rather than type your code directly in the Details tab of the field, it is highly recommended that you placed all your code in an external file and call that file using the core wireRenderFile() method. Taking this approach means you will be able to edit your code in your favourite text editor. It also means you will be able to type more text without having to scroll. Editing the file is also easier than editing the field. To use this approach, simply do:
      return wireRenderFile('name-of-file');// file will be in /site/templates/ If using ProcessWire 3.x, you will need to use namespace as follows:
      return ProcessWire\wireRenderFile('name-of-file'); How to access the value of RuntimeMarkup in the frontend (our field is called 'runtime_markup')
      Access the field on the current page (just like any other field)
      echo $page->runtime_markup; Access the field on another page
      echo $pages->get('/about-us/')->runtime_markup; Screenshots



    • By kongondo
      Media Manager
      Released 31 March 2016
      As of 10 May 2019 ProcessWire versions earlier than 3.x are not supported
      *******************************************************   ORIGINAL POST   *******************************************************
      API Example (frontend; will be added to documentation site)
      Accessing and outputting the contents of the MediaManager field(s) in your template is quite simple. The fields are accessed like many other ProcessWire fields. The fields return an array of type MediaManagerArray that need to be looped to output each media within. Assuming you created a field of type MediaManager named 'media', you can loop through it for a given page as shown below.
        @note: Each MediaManager object has the following 5 basic properties:   DATABASE (saved properties) 1. id => pageID of the page where the media lives (hidden in admin and not important to know about) 2. type => integer denoting media type (1=audio; 2=document; 3=image [for variations this will be 3x, where x is the number of the variation of an original image]; 4=video)   RUNTIME 3. typeLabel => user friendly string denoting media type (audio, document, image, video) 4. media => a ProcessWire Image/File Object including all their properties (ext, filesizeStr, height, width, description, tags, filename, basename, etc.) 5. title => title of media (@note: this is the title of the page where the media lives; may or may not be the same as the name of the media file itself). This can be used as a user-friendly name for your media $media = $page->media;// returns a MediaManagerArray. Needs to be looped through foreach ($media as $m) { echo $m->id;// e.g. 1234 (hidden page in /admin/media-manager/media-parent/) echo $m->type;// e.g. 3 (a media of type image) OR 1 (a media of type audio) echo $m->typeLabel;// e.g. 'document' (i.e. type would be 2) echo $m->title;// e.g. 'My Nice Trip' (whose media file could be my-nice-trip.mp4) /* @note: - $m->media returns an object; either a ProcessWire Image (for image media) or File object (for audio, document and video media) - This means you have access to all the properties of that object, e.g. ext, tags, description, url, filename, basename, width, height, modified, created, filesize, filesizeStr, etc as well as associated methods, e.g. size() */ echo $m->media->tags; } // only output images foreach ($media as $m) { if($m->typeLabel =='image') { echo "<img src='" . $m->media->size(100,75)->url . "'><br>"; } } // There's also a toString() method so you can do: echo $page->media; /* All your media will be output wrapped in appropriate HTML tags, i.e.: audio: <audio></audio>; document: <a></a>; image: <img>; video: <video></video>; */  
      *******************************************************   ORIGINAL POST   *******************************************************
      The topic of a central media manager feature for ProcessWire has come up several times:
      More recently, regarding my Visual Page Selector module, I have been asked several times why the module does not have an in-built feature to upload images.
      There's two camps on the topic of a central media manager: those who like them (especially those coming in to PW from other CMSes) and those who don't like them (primarily because of the chaotic way some CMSes (dis)organise their media management) . I think that we can have our cake and eat it too! If done the right way, closely following the principles of and harnessing the power of ProcessWire, we can have a well-implemented, organised, feature-rich, site-wide media manager.
      Introducing Media Manager: (a commercial module)
      Alongside a number of modules I am currently working on (both free and commercial), I have been developing  a centralised Media Manager for ProcessWire. Before you cast the first stone, no, this is not going to be a one-large-media-bucket as in other CMS where it gets very messy very quickly . In the backend things are neatly stored away, yes, in pages. However, those are pages you will not see (just like repeater pages). Before anyone has a go at pages, remember a page is not that thing you see on the ProcessWire Tree (that's just its visual representation); A page is a record/row in the database    . For the end-user of Media Manager, all they will see is the 'familiar media bucket' to select their media from. As long as it works efficiently, I don't think they care about the wizardry behind the scenes  . 
      The module allows for the comprehensive management of several media types:
      Audio Video Images Documents Each media type will be handled by its own sub-module so the user can pick and install/choose the type of media management they want.
      Features include:
      Access controls Centralized uploads of media Bulk management of media: tag, delete, describe, replace, etc. Bulk upload: zip; scan, single Quick upload in page edit mode Usage stats across pages (maybe?) Etc.. Would love to hear your thoughts and any feature suggestions. I think there's enough demand for such a module. If not, please let me know so that I can instead focus on other things  , thanks.
      How other CMS do it

      The more efficient (PW) way of doing it

    • By Robin S
      I have had this module sitting in a 95% complete state for a while now and have finally made the push to get it out there. Thanks to @teppo for his Hanna Code Helper module which I referred to and borrowed from during development.
      A module for ProcessWire CMS/CMF. Provides a number of enhancements for working with Hanna Code tags in CKEditor. The main enhancement is that Hanna tags in a CKEditor field may be double-clicked to edit their attributes using core ProcessWire inputfields in a modal dialog.
      Requires the Hanna Code module.
      Install the HannaCodeDialog module using any of the normal methods.
      For any CKEditor field where you want the "Insert Hanna tag" dropdown menu to appear in the CKEditor toolbar, visit the field settings and add "HannaDropdown" to the "CKEditor Toolbar" settings field.
      Module configuration
      Visit the module configuration screen to set any of the following:
      Exclude prefix: Hanna tags named with this prefix will not appear in the CKEditor toolbar dropdown menu for Hanna tag insertion. Exclude Hanna tags: Hanna tags selected here will not appear in the CKEditor toolbar dropdown menu for Hanna tag insertion. Background colour of tag widgets: you can customise the background colour used for Hanna tags in CKEditor if you like. Dialog width: in pixels Dialog height: in pixels Features
      Insert tag from toolbar dropdown menu
      Place the cursor in the CKEditor window where you want to insert your Hanna tag, then select the tag from the "Insert Hanna tag" dropdown.

      Advanced: if you want to control which tags appear in the dropdown on particular pages or templates you can hook HannaCodeDialog::getDropdownTags. See the forum support thread for examples .
      Edit tag attributes in modal dialog
      Insert a tag using the dropdown or double-click an existing tag in the CKEditor window to edit the tag attributes in a modal dialog.

      Tags are widgets
      Hanna tags that have been inserted in a CKEditor window are "widgets" - they have a background colour for easy identification, are protected from accidental editing, and can be moved within the text by drag-and-drop.
      ![Hanna tag widget]
      Options for tag attributes may be defined
      You can define options for a tag attribute so that editors must choose an option rather than type text. This is useful for when only certain strings are valid for an attribute and also has the benefit of avoiding typos.
      Add a new attribute for the Hanna tag, named the same as the existing attribute you want to add options for, followed by "__options". The options themselves are defined as a string, using a pipe character as a delimiter between options. Example for an existing attribute named "vegetables":
      vegetables__options=Spinach|Pumpkin|Celery|Tomato|Brussels Sprout|Potato You can define a default for an attribute as normal. Use a pipe delimiter if defining multiple options as the default, for example:
      vegetables=Tomato|Potato Dynamic options
      Besides defining static options as above, you can use one Hanna tag to dynamically generate options for another. For instance, you could create a Hanna tag that generates options based on images that have been uploaded to the page, or the titles of children of the page.
      Your Hanna tag that generates the options should echo a string of options delimited by pipe characters (i.e. the same format as a static options string).
      You will probably want to name the Hanna tag that generates the options so that it starts with an underscore (or whatever prefix you have configured as the "exclude" prefix in the module config), to avoid it appearing as an insertable tag in the HannaCodeDialog dropdown menu.
      Example for an existing attribute named "image":
      image__options=[[_images_on_page]] And the code for the _images_on_page tag:
      <?php $image_names = array(); $image_fields = $page->fields->find('type=FieldtypeImage')->explode('name'); foreach($image_fields as $image_field) { $image_names = array_unique( array_merge($image_names, $page->$image_field->explode('name') ) ); } echo implode('|', $image_names); Advanced: define or manipulate options in a hook
      You can hook HannaCodeDialog::prepareOptions to define or manipulate options for a Hanna tag attribute. Your Hanna tag must include a someattribute__options attribute in order for the hook to fire. The prepareOptions method receives the following arguments that can be used in your hook:
      options_string Any existing string of options you have set for the attribute attribute_name The name of the attribute the options are for tag_name The name of the Hanna tag page The page being edited If you hook after HannaCodeDialog::prepareOptions then your hook should set $event->return to an array of option values, or an associative array in the form of $value => $label.
      Choice of inputfield for attribute
      You can choose the inputfield that is used for an attribute in the dialog.
      For text attributes the supported inputfields are text (this is the default inputfield for text attributes so it isn't necessary to specify it if you want it) and textarea. Note: any manual line breaks inside a textarea are removed because these will break the CKEditor tag widget.
      Inputfields that support the selection of a single option are select (this is the default inputfield for attributes with options so it isn't necessary to specify it if you want it) and radios.
      Inputfields that support the selection of multiple options are selectmultiple, asmselect and checkboxes.
      You can also specify a checkbox inputfield - this is not for attributes with defined options but will limit an attribute to an integer value of 1 or 0.
      The names of the inputfield types are case-insensitive.
      Example for an existing attribute named "vegetables":
      vegetables__type=asmselect Descriptions and notes for inputfields
      You can add a description or notes to an attribute and these will be displayed in the dialog.
      Example for an existing attribute named "vegetables":
      vegetables__description=Please select vegetables for your soup. vegetables__notes=Pumpkin and celery is a delicious combination. Notes
      When creating or editing a Hanna tag you can view a basic cheatsheet outlining the HannaCodeDialog features relating to attributes below the "Attributes" config inputfield.
      HannaCodeDialog includes and automatically loads the third-party CKEditor plugins Line Utilities and Widget. If you have added these plugins to your CKEditor field already for some purpose and experience problems with HannaCodeDialog try deactivating those plugins from the CKEditor field settings.
  • Create New...