[deprecated] RockFinder1

[bernhard]

--- Please use RockFinder3 ---

[bernhard]

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:

[flydev]

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 :

 

[bernhard]

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

[flydev]

• 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

 

[bernhard]

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

[flydev]

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.

[bernhard]

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 ?

[flydev]

okay ?

 

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

[bernhard]

On 5/16/2018 at 6:56 PM, flydev said:

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

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

[kongondo]

1 hour ago, bernhard said:

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

Maybe it only works with GitHub hosted modules?

[flydev]

1 hour ago, kongondo said:

Maybe it only works with GitHub hosted modules? 

That what I was thinking.

[kongondo]

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

[adrian]

I am probably missing something, but https://gitlab.com/baumrock/RockFinder/blob/master/ProcessRockFinder.info.php shows: 'version' => 1,

Sorry, I see that: https://gitlab.com/baumrock/RockFinder/blob/master/RockFinder.info.php is 103

BTW - I would recommend string version numbers.

[bernhard]

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:

 

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.

[bernhard]

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

[bernhard]

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

[bernhard]

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:

 

[dotnetic]

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.

[bernhard]

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.

[mel47]

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

[bernhard]

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?

[bernhard]

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
$this->setData($sql);

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

$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);

 

[dotnetic]

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

[thetuningspoon]

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 ?