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 Gadgetto
      Status update links (inside this thread) for SnipWire development will be always posted here:
      2019-08-08
      2019-06-15
      2019-06-02
      2019-05-25
      If you are interested, you can test the current state of development:
      https://github.com/gadgetto/SnipWire
      Please note that the software is not yet intended for use in a production system (alpha version).
      If you like, you can also submit feature requests and suggestions for improvement. I also accept pull requests.
      ---- INITIAL POST FROM 2019-05-25 ----
      I wanted to let you know that I am currently working on a new ProcessWire module that fully integrates the Snipcart Shopping Cart System into ProcessWire. (this is a customer project, so I had to postpone the development of my other module GroupMailer).
      The new module SnipWire offers full integration of the Snipcart Shopping Cart System into ProcessWire.
      Here are some highlights:
      simple setup with (optional) pre-installed templates, product fields, sample products (quasi a complete shop system to get started immediately) store dashboard with all data from the snipcart system (no change to the snipcart dashboard itself required) Integrated REST API for controlling and querying snipcart data webhooks to trigger events from Snipcart (new order, new customer, etc.) multi currency support self-defined/configurable tax rates etc. Development is already well advanced and I plan to release the module in the next 2-3 months.
      I'm not sure yet if this will be a "Pro" module or if it will be made available for free.
      I would be grateful for suggestions and hints!
      (please have a look at the screenshots to get an idea what I'm talking about)
       




    • By apeisa
      Update 31.7.2019: AdminBar is now maintained by @teppo. Modules directory entry has been updated, as well as the "grab the code" link below.
      ***
      Latest screencast: http://www.screencas...73-ab3ba1fea30c
      Grab the code: https://github.com/teppokoivula/AdminBar
      ***
      I put this Adminbar thingy (from here: http://processwire.c...topic,50.0.html) to modules section and to it's own topic.
      I recorded quick and messy screencast (really, my first screencast ever) to show what I have made so far. You can see it from here: http://www.screencas...18-1bc0d49841b4
      When the modal goes off, I click on the "dark side". I make it so fast on screencast, so it might seem a little bit confusing. Current way is, that you can edit, go back to see the site (without saving anything), continue editing and save. After that you still have the edit window, but if you click "dark side" after saving, then the whole page will be reloaded and you see new edits live.
      I am not sure if that is best way: there are some strengths in this thinking, but it is probably better that after saving there shouldn't be a possibility to continue editing. It might confuse because then if you make edits, click on dark side -> *page refresh* -> You lose your edits.
      ***
      When I get my "starting module" from Ryan, I will turn this into real module. Now I had to make some little tweaks to ProcessPageEdit.module (to keep modal after form submits). These probably won't hurt anything:
      if($this->redirectUrl) $this->session->redirect($this->redirectUrl); if(!empty($_GET['modal'])) $this->session->redirect("./?id={$this->page->id}&modal=true"); // NEW LINE else $this->session->redirect("./?id={$this->page->id}");   and...
      if(!empty($_GET['modal'])) { $form->attr('action', './?id=' . $this->id . '&modal=true'); } else { $form->attr('action', './?id=' . $this->id); // OLD LINE }  
    • By Mike Rockett
      Jumplinks for ProcessWire
      Release: 1.5.54
      Composer: rockett/jumplinks
      Jumplinks is an enhanced version of the original ProcessRedirects by Antti Peisa.
      The Process module manages your permanent and temporary redirects (we'll call these "jumplinks" from now on, unless in reference to redirects from another module), useful for when you're migrating over to ProcessWire from another system/platform. Each jumplink supports wildcards, shortening the time needed to create them.
      Unlike similar modules for other platforms, wildcards in Jumplinks are much easier to work with, as Regular Expressions are not fully exposed. Instead, parameters wrapped in curly braces are used - these are described in the documentation.
      Under Development: 2.0, to be powered by FastRoute
      As of version 1.5.0, Jumplinks requires at least ProcessWire 2.6.1 to run.
      View on GitLab
      Download via the Modules Directory
      Read the docs
      Features
      The most prominent features include:
      Basic jumplinks (from one fixed route to another) Parameter-based wildcards with "Smart" equivalents Mapping Collections (for converting ID-based routes to their named-equivalents without the need to create multiple jumplinks) Destination Selectors (for finding and redirecting to pages containing legacy location information) Timed Activation (activate and/or deactivate jumplinks at specific times) 404-Monitor (for creating jumplinks based on 404 hits) Additionally, the following features may come in handy:
      Stale jumplink management Legacy domain support for slow migrations An importer (from CSV or ProcessRedirects) Feedback & Feature Requests
      I’d love to know what you think of this module. Please provide some feedback on the module as a whole, or even regarding smaller things that make it whole. Also, please feel free to submit feature requests and their use-cases.
      Note: Features requested so far have been added to the to-do list, and will be added to 2.0, and not the current dev/master branches.
      Open Source

      Jumplinks is an open-source project, and is free to use. In fact, Jumplinks will always be open-source, and will always remain free to use. Forever. If you would like to support the development of Jumplinks, please consider making a small donation via PayPal.
      Enjoy! :)
    • By nbcommunication
      I've spent the last while experimenting with srcset implementation - and PageimageSrcset is the result:
      PageimageSrcset
      Provides configurable srcset and sizes properties/methods for Pageimage.
      Overview
      The main purpose of this module is to make srcset implementation as simple as possible in your template code. It does not handle images rendered in CKEditor or similar fields.
      For an introduction to srcset and sizes, please read this Mozilla article about responsive images.
      Pageimage::srcset()
      // The property, which uses the set rules in the module configuration $srcset = $image->srcset; // A method call, using a set rules string // Delimiting with a newline (\n) would also work, but not as readable $srcset = $image->srcset("320, 480, 640x480 768w, 1240, 2048 2x"); // The same as above but using an indexed/sequential array $srcset = $image->srcset([ "320", "480", "640x480 768w", "1240", "2048 2x", ]); // The same as above but using an associative array // No rule checking is performed $srcset = $image->srcset([ "320w" => [320], "480w" => [480], "768w" => [640, 480], "1240w" => [1240], "2x" => [2048], ]); // Use the default set rules with portrait images generated for mobile/tablet devices $srcset = $image->srcset(true); // Return the srcset using all arguments $srcset = $image->srcset("320, 480, 640x480 768w, 1240, 2048 2x", [ "portrait" => "320, 640", ]); // The set rules above are a demonstration, not a recommendation! Image variations are only created for set rules which require a smaller image than the Pageimage itself. On large sites this may still result in a lot of images being generated. If you have limited storage, please use this module wisely.
      Portrait Mode
      In many situations, the ratio of the image does not need to change at different screen sizes. However, images that cover the entire viewport are an exception to this and are often the ones that benefit most from srcset implementation.
      The main problem with cover images is that they need to display landscape on desktop devices and portrait when this orientation is used on mobile and tablet devices.
      You can automatically generate portrait images by enabling portrait mode. It is recommended that you use this in combination with Pageimage::focus() so that the portrait variations retain the correct subject.
      The generated variations are HiDPI/Retina versions. Their height is determined by the portrait ratio (e.g. 9:16). Variations are always generated, regardless of whether the original image is smaller. Upscaling is disabled though, so you may find that some variations are actually smaller than they say they are in their filename.
      The sizes attribute should be used when portrait mode is enabled. Pageimage::sizes will return (orientation: portrait) and (max-width: {maxWidth}px) 50vw by default, which handles the use of these images for retina devices. The maximum width used in this rule is the largest set width.
      Pageimage::sizes()
      There is no option to configure default sizes because in most cases 100vw is all you need, and you do not need to output this anyway as it is inferred when using the srcset attribute. You can use the method for custom sizes though:
      // The property $sizes = $image->sizes; // Returns 100vw in most cases // Returns '(orientation: portrait) and (max-width: {maxWidth}px)50vw' if portrait mode enabled // A method call, using a mixture of integer widths and media query rules // Integer widths are treated as a min-width media query rule $sizes = $image->sizes([ 480 => 50, "(orientation: portrait) and (max-width: 640px)" => 100, 960 => 25, ]); // (min-width: 480px) 50vw, (orientation: portrait) and (max-width: 640px) 100vw, (min-width: 960px) 25vw // Determine widths by UIkit 'child-width' classes $sizes = $image->sizes([ "uk-child-width-1-2@s", "uk-child-width-1-3@l", ]); // (min-width: 640px) 50vw, (min-width: 1200px) 33.33vw // Determine widths by UIkit 'width' classes $sizes = $image->sizes([ "uk-width-1-2@m", "uk-width-1-3@xl", ]); // (min-width: 960px) 50vw, (min-width: 1600px) 33.33vw // Return the portrait size rule $sizes = $image->sizes(true); // (orientation: portrait) and (max-width: {maxWidth}px) 50vw // The arguments above are a demonstration, not a recommendation! Pageimage::render()
      This module extends the options available to this method with:
      srcset: When the module is installed, this will always be added, unless set to false. Any values in the formats described above can be passed. sizes: Only used if specified. Any values in the formats described above can be passed. uk-img: If passed, as either true or as a valid uk-img value, then this attribute will be added. The srcset attribute will also become data-srcset. Please refer to the API Reference for more information about this method.
      // Render an image using the default set rules echo $image->render(); // <img src='image.jpg' alt='' srcset='{default set rules}'> // Render an image using custom set rules echo $image->render(["srcset" => "480, 1240x640"]); // <img src='image.jpg' alt='' srcset='image.480x0-srcset.jpg 480w, image.1240x640-srcset.jpg 1240w'> // Render an image using custom set rules and sizes // Also use the `markup` argument echo $image->render("<img class='image' src='{url}' alt='Image'>", [ "srcset" => "480, 1240", "sizes" => [1240 => 50], ]); // <img class='image' src='image.jpg' alt='Image' srcset='image.480x0-srcset.jpg 480w, image.1240x640-srcset.jpg 1240w' sizes='(min-width: 1240px) 50vw'> // Render an image using custom set rules and sizes // Enable uk-img echo $image->render([ "srcset" => "480, 1240", "sizes" => ["uk-child-width-1-2@m"], "uk-img" => true, ]); // <img src='image.jpg' alt='' data-uk-img data-srcset='image.480x0-srcset.jpg 480w, image.1240x640-srcset.jpg 1240w' sizes='(min-width: 960px) 50vw'> // Render an image using portrait mode // Default rule sets used: 320, 640, 768, 1024, 1366, 1600 // Portrait widths used: 320, 640, 768 // Original image is 1000px wide // Not possible to use portrait mode and custom sets or portrait widths in render() // Sizes attribute automatically added echo $image->render(["srcset" => true]); // <img src='image.jpg' alt='' srcset='image.320x569-srcset-hidpi.jpg 320w, image.640x1138-srcset-hidpi.jpg 640w, image.768x1365-srcset-hidpi.jpg 768w, image.jpg 1024w' sizes='(orientation: portrait) and (max-width: 768px) 50vw'> Configuration
      To configure this module, go to Modules > Configure > PageimageSrcset.
      Set Rules
      These are the default set rules that will be used when none are specified, e.g. when calling the property: $image->srcset.
      Each set rule should be entered on a new line, in the format {width}x{height} {inherentwidth}w|{resolution}x.
      Not all arguments are required - you will probably find that specifying the width is sufficient for most cases. Here's a few examples of valid set rules and the sets they generate:
      Set Rule Set Generated Arguments Used 320 image.320x0-srcset.jpg 320w {width} 480x540 image.480x540-srcset.jpg 480w {width}x{height} 640x480 768w image.640x480-srcset.jpg 768w {width}x{height} {inherentwidth}w 2048 2x image.2048x0-srcset.jpg 2x {width} {resolution}x How you configure your rules is dependent on the needs of the site you are developing; there are no prescriptive rules that will meet the needs of most situations. This article gives a good overview of some of the things to consider.
      When you save your rules, a preview of the sets generated and an equivalent method call will be displayed to the right. Invalid rules will not be used, and you will be notified of this.
      Portrait Mode
      Set Widths
      A comma limited list of widths to create HiDPI/Retina portrait variations for.
      Crop Ratio
      The portrait ratio that should be used to crop the image. The default of 9:16 should be fine for most circumstances as this is the standard portrait ratio of most devices. However, you can specify something different if you want. If you add a landscape ratio, it will be switched to portrait when used.
      Any crops in the set rules ({width}x{height}) are ignored for portrait mode variations as this ratio is used instead.
      UIkit Widths
      If your website theme uses UIkit, you can pass an array of UIkit width classes to Pageimage::sizes to be converted to sizes. The values stored here are used to do this. If you have customised the breakpoints on your theme, you should also customise them here.
      Please note that only 1- widths are evaluated by Pageimage::sizes, e.g. uk-width-2-3 will not work.
      Remove Variations
      If checked, the image variations generated by this module are cleared on Submit. On large sites, this may take a while. It makes sense to run this after you have made changes to the set rules.
      Image Suffix
      You will see this field when Remove Variations is checked. The value is appended to the name of the images generated by this module and is used to identify variations. You should not encounter any issues with the default suffix, but if you find that it conflicts with any other functionality on your site, you can set a custom suffix instead.
      Debug Mode
      When this is enabled, a range of information is logged to pageimage-srcset.
      PageimageSrcsetDebug.js is also added to the <head> of your HTML pages. This will console.log a range of information about the images and nodes using srcset on your page after a window.onresize event is triggered. This can assist you in debugging your implementation.
      The browser will always use the highest resolution image it has loaded or has cached. You may need to disable browser caching to determine whether your set rules are working, and it makes sense to work from a small screen size and up. If you do it the other way, the browser is going to continue to use the higher resolution image it loaded first.
      UIkit Features
      This module implements some additional features that are tailored towards UIkit being used as the front-end theme framework, but this is not required to use the module.
      Installation
      Download the zip file at Github or clone the repo into your site/modules directory. If you downloaded the zip file, extract it in your sites/modules directory. In your admin, go to Modules > Refresh, then Modules > New, then click on the Install button for this module. ProcessWire >= 3.0.123 is required to use this module.
×
×
  • Create New...