RockFinder3 - Combine the power of ProcessWire selectors and SQL

[bernhard]

I'm not sure about the limit=12 in the initial find - you need to check that!

[bernhard]

Please dump your SQL and fire that manually to your DB. You can use whatever tool you like. Tracy comes with Adminer for example. You might get more helpful error messages then. Start with simple SQL statements and then go further.

[bernhard]

I was mentioned in this post by @MoritzLost and thought that would be a nice challenge and a nice little tutorial. I've updated the docs:

https://github.com/baumrock/rockfinder3#example-group-by-date

Example: Group by date

As an example we will create a list of the count of cats and dogs related to their page-creation day. We start with a simple list of all ids of cats and dogs:

$rf = $rockfinder->find("template=cat|dog");
$rf->dumpSQL();
$rf->dump();

We can't simply add the created column because this is a timestamp. We need a formatted date, so we add it as custom SQL:

$rf = $rockfinder->find("template=cat|dog");
$rf->query->select("DATE_FORMAT(pages.created, '%Y-%m-%d') as created");
$rf->dumpSQL();
$rf->dump();

Great! Now we need to group the result by the date string:

$rf = $rockfinder->find("template=cat|dog");
$rf->query->select("DATE_FORMAT(pages.created, '%Y-%m-%d') as created");
$rf->query->select("COUNT(id) as cnt");
$rf->query->groupby("DATE_FORMAT(pages.created, '%Y-%m-%d')");
$rf->dumpSQL();
$rf->dump();

Wait... That's not what we expected, right? That's because we still have the pages.id column in our SELECT and GROUP BY statement and therefore we end up with all the cats and dogs as unique rows. To get rid of that column we make one important change: Instead of adding the SELECT and GROUP BY statement to the query we overwrite it:

$rf = $rockfinder->find("template=cat|dog");
$rf->query->set('select', [
    "DATE_FORMAT(pages.created, '%Y-%m-%d') as created",
    "COUNT(id) as cnt",
]);
$rf->query->set('groupby', [
    "DATE_FORMAT(pages.created, '%Y-%m-%d')",
]);
$rf->dumpSQL();
$rf->dump();

Not too complicated, right? You want yearly stats? Easy! Simply change the date format string to %Y:

[bernhard]

1.0.4 adds a litte new helper to add all fields of a template as columns:

$rockfinder
  ->find("template=foo")
  ->addColumnsFromTemplate("foo");

1.0.5: add getJSON() method

[bernhard]

v1.0.6 improves the groupby() method

I have a survey tool where my clients can send emails to all persons participating in that project. Every person can give multiple "feedbacks" to other persons and every "feedback" has several "answers" that can be answered. On the mail-interface they see all persons of the project, but they did not see which of the persons had answered all questions. As they wanted to send a reminder to all that have open questions we needed an aggregated answer-count for each person. I've updated the project from using RockFinder1 to RockFinder3 and so the task got a lot easier ?

--- updated readme for groupby() ---

 

If you need the SQL statement instead of a PHP array you can set that as one of several options:

$sql = $finder->groupby('foo', [...], ['sql'=>true]);

For example you can then JOIN complex queries quite easily:

$foo = $rockfinder->find(...);
$foosql = $foo->getSQL();

$bar = $rockfinder->find(...);
$barsql = $bar->groupby('bar', [...], ['sql'=>true]);

$join = "SELECT
  foo.xx, foo.yy, bar.xx, bar.yy
  FROM ($foosql) AS foo
  LEFT JOIN ($barsql) AS bar ON foo.xx = bar.yy";
db($rockfinder->getObjects($sql));

-----

If anybody is interested in the real world code:

// setup main finder to find all persons
$persons = $rockfinder
  ->find("template=person, has_parent=$project")
  ->addColumns(['isFN', 'lang', 'forename', 'surname']);

// get results count for all FG (feedback giver)
$rf = $rockfinder
  ->find("template=feedback, has_parent=$project")
  ->addColumns(['fg', 'answercount']);

// add fixed number of questions asked per feedback
$questions = $project->competences->count();
$rf->query->select("'$questions' as 'questions'");

// get sql of grouped result (grouping by FG)
$answered = $rf->groupby('fg', [
  'IFNULL(sum(questions),0) AS asked',
  'IFNULL(sum(answercount),0) AS answered',
  'IFNULL(sum(questions),0) - IFNULL(sum(answercount),0) AS todo',
], ['sql' => true]);

// build final query
$persons = $persons->getSQL();
$sql = "SELECT
  persons.*, answered.asked, answered.todo
  FROM ($persons) AS persons
  LEFT JOIN ($answered) AS answered ON persons.id = answered.fg";

// set sql query as data for RockGrid
$grid->setData($sql);

Now they can easily sort and filter persons that they want to message ? 

Happy finding ? 

[bernhard]

v1.0.7 fixes an issue with non-lowercase fieldnames

I had a field called "isFN" and rockfinder tried to look for data in "field_isFN" in the DB, where the actual table name was "field_isfn" (all lowercase). Seems like an easy fix - if you find that makes any problems on another end please let me know! ? 

[bernhard]

For simple queries we now have findRaw() in the core! 

 

Seems like joining columns could be improved in terms of performance...

@adrian other than in your example I had to fire separate d() calls to get separate execution times and mem consumptions?! Am I missing anything?

[adrian]

1 hour ago, bernhard said:

@adrian other than in your example I had to fire separate d() calls to get separate execution times and mem consumptions?! Am I missing anything?

Not really, I ran them both separately without clearing the results of the previous run. I also used the ability of the Console panel to only run the selected code so I didn't need to comment out one and then the other. Make sense?

[bernhard]

Haha sorry, I got so used to ALT+ENTER and always reset the console that I totally forgot about that "feature" ? Thx for the reminder!

[adrian]

PS - it's definitely interesting to see that Rockfinder still seems much more efficient - wondering if you are planning on pointing out that comparison to Ryan to see if he can tweak findRaw()?

[bernhard]

It's only much more efficient as long as it only finds IDs and that's just using findIDs... as soon as I add a column of data the new findRaw is much quicker!

Actually finding 1000 pages with just the ID column taking almost 2 seconds seems quite long... But I don't have time to investigate further at the moment.

[teppo]

@bernhard, I'm feeling a little lazy so thought I'd just ask: does RockFinder support (template or field level) permissions? I haven't found any mention saying that it would, but neither did it state anywhere that it wouldn't (I think) ?

[bernhard]

Template: YES, Field: NO

It takes permissions from the very first page find operation (that is a $pages->findIDs) and then joins all raw data from the database to those page ids.

[bernhard]

RF3 got a very nice little update today ?

Aggregations

Often we need to calculate sums or averages of table data quickly and efficiently. RockFinder3 makes that easy as well:

$avg = $rockfinder->find("template=cat")
    ->addColumn('weight')
    ->getObject("SELECT AVG(weight)");
db($avg);

$cats = $rockfinder
  ->find("template=cat")
  ->addColumns(['title', 'weight']);
$cats->dump(); // dump finder data to tracy
$obj = $cats->getObject("SELECT SUM(`weight`) AS `total`, COUNT(`id`) AS `cats`, SUM(`weight`)/COUNT(`id`) AS `avg`");
db($obj); // dump result of aggregation

What happens behind the scenes is that RockFinder3 gets the current SQL query of the finder and adds that as FROM (...sql...) AS tmp to the query that you provide for aggregation.

This is the resulting SQL query of the example above:

SELECT SUM(`weight`) AS `total`, COUNT(`id`) AS `cats`, SUM(`weight`)/COUNT(`id`) AS `avg` FROM (
  SELECT
    `pages`.`id` AS `id`,
    `_field_title_605cab16f38ce`.`data` AS `title`,
    `_field_weight_605cab16f3993`.`data` AS `weight`
  FROM `pages`
  LEFT JOIN `field_title` AS `_field_title_605cab16f38ce` ON `_field_title_605cab16f38ce`.`pages_id` = `pages`.`id`
  LEFT JOIN `field_weight` AS `_field_weight_605cab16f3993` ON `_field_weight_605cab16f3993`.`pages_id` = `pages`.`id`
  WHERE (pages.templates_id=44)
  AND (pages.status<1024)
  GROUP BY pages.id
) AS tmp

You can even provide a suffix for your query to do things like GROUP BY etc:

$rf = $rockfinder->find("template=cat|dog");
$rf->addColumns(['created']);
$rf->dump();
db($rf->getObjects(
  "SELECT COUNT(id) AS `count`, DATE_FORMAT(created, '%Y-%m-%d') AS `date`",
  "GROUP BY DATE_FORMAT(created, '%Y-%m-%d')"
));

[bernhard]

Another handy update on v1.0.19 ? 

map(column, labels)

Sometimes the value stored in the DB is a key that is not as descriptive as a text label. For example it could be that the returned value of a "sex" column is f for female or m for male. One option is to replace this value via JS which could have benefits regarding performance on large datasets, but it is a little more work to make everything work as expected (eg you need to take care of formatting of the cell as well as the sort and filter values).

It might be easier to map the returned value of the finder to an array of labels in such cases:

$sexes = ['f'=>'Female', 'm'=>'Male'];
$rockfinder->find(...)
  ->addColumns(["sex", ...])
  ->map("sex", $sexes);

The column will then show male and female instead of m and f.

[mel47]

Hi @bernhard

I have a question. I used RF1 and RockGrid. I saw both are deprecated. Since for now, it suits my needs coupled to a lack of time to rewrite my queries, I don't really want to convert to RF3. But I now do need to show data to admin users (and not just superadmin). Currently the table, in a custom admin page, just show "loading". Can I kindly ask how I can display data my users?
Sub-question : if rockgrid is deprecated, what else I should use?

Thanks

Mel

[dotnetic]

14 hours ago, mel47 said:

Hi @bernhard

I have a question. I used RF1 and RockGrid. I saw both are deprecated. Since for now, it suits my needs coupled to a lack of time to rewrite my queries, I don't really want to convert to RF3. But I now do need to show data to admin users (and not just superadmin). Currently the table, in a custom admin page, just show "loading". Can I kindly ask how I can display data my users?
Sub-question : if rockgrid is deprecated, what else I should use?

Thanks

Mel

There is also RockTabulator, which is the successor to RockGrid.

And most things that you can do with RockFinder are now possible with the  ProcessWire 3.0.172 – Find faster and more efficiently. Take a look at the findRaw and findJoin methods.

[bernhard]

On 10/31/2021 at 4:11 AM, mel47 said:

Currently the table, in a custom admin page, just show "loading". Can I kindly ask how I can display data my users?

I guess you'll have some JS errors in the console then? What does it say?

On 10/31/2021 at 4:11 AM, mel47 said:

Sub-question : if rockgrid is deprecated, what else I should use?

Well... that's not an easy question. For my admin projects I'm using RockGrid2 which is the successor of RockGrid, RockTabulator and RockDataTables ? It is based on v4 of tabulator.info and can use plain PHP arrays (such as findRaw) or RF3 etc as data source. But it is not finished and will not be publicly available any time soon. Likely never. All the other modules will most likely not get any updates in the future as I don't need them any more.

If someone really needs some of this stuff I'm happy to help or discuss what we can do via PM.

Hope that helps at least a little bit ? 

[mel47]

Thanks for answer. Honestly for now, as I said RockGrid perfectly fit my needs. Here the JS error (appearing only to non super-admin users): 

 

Uncaught SyntaxError: JSON.parse: unexpected keyword at line 1 column 1 of the JSON data
    onreadystatechange http://localhost/dev/site/modules/FieldtypeRockGrid/RockGridItem.js?t=1635649449:290
    getAjaxData http://localhost/dev/site/modules/FieldtypeRockGrid/RockGridItem.js?t=1635649449:288
    init http://localhost/dev/site/modules/FieldtypeRockGrid/RockGrid.js?t=1635649449:188
    init http://localhost/dev/site/modules/FieldtypeRockGrid/RockGrid.js?t=1635649449:223
    initGrid_suivi_org http://localhost/dev/admin/gestion24h/suivi/organismes/:290
    <anonymous> http://localhost/dev/admin/gestion24h/suivi/organismes/:301
RockGridItem.js:290:31
    onreadystatechange http://localhost/dev/site/modules/FieldtypeRockGrid/RockGridItem.js?t=1635649449:290
    (Asynchrone : EventHandlerNonNull)
    getAjaxData http://localhost/dev/site/modules/FieldtypeRockGrid/RockGridItem.js?t=1635649449:288
    init http://localhost/dev/site/modules/FieldtypeRockGrid/RockGrid.js?t=1635649449:188
    init http://localhost/dev/site/modules/FieldtypeRockGrid/RockGrid.js?t=1635649449:223
    initGrid_suivi_org http://localhost/dev/admin/gestion24h/suivi/organismes/:290
    <anonyme> http://localhost/dev/admin/gestion24h/suivi/organismes/:301

Mel

[mel47]

Sorry to come back again on the subject, but does someone have an idea why we can't see data if we are logged as admin? Before (unfortunately, don't remember which PW version it was), it was displaying correctly. I'm willing to modify anything : role, permission, module to get it working. I just want to avoid to give super-admin rights.

Thanks

Mel

[dotnetic]

Hey @mel47 What you mean by can't see data? Is the grid displayed, but the cells are empty? Is nothing displayed? Do you get Javascript errors in the dev console or maybe PHP errors (turn on debug mode).

Can you share the JSON response?

[Spiria]

I have a problem with Rockfinder. The user of "*=" seems not to be allowed? What am I doing wrong?  findRaw() returns the result.

        $search = 'template=blog_article,title*=Processwire,sort=-created,limit=10';
        $found = pages()->findRaw($search);
        $rockFinder = modules()->get('RockFinder3');
        $finder = $rockFinder
            ->find($search)
            ->addColumns(['title', 'blog_author','blog_summary','blog_picture'])
            ->addPath(user()->language->name);
        $rows = $finder->getRows();

The result of findRaw():

Error message with RockFinder

 

[bernhard]

12 hours ago, Spiria said:

$rockFinder = modules()->get('RockFinder3');

Does it work if you change that to modules()->get('RockFinder3Master'); ?

[Spiria]

5 hours ago, bernhard said:

Does it work if you change that to modules()->get('RockFinder3Master'); ?

Unfortunetaly not. Please note that this works if the variable $q is empty.

$q = "";
        $search = 'template=blog_article,title|page_content*=' . $q . ',sort=-created,limit=10';
        $rockFinder = modules()->get('RockFinder3Master');

But this fires the same error as decribed above:

$q = "ProcessWire";
        $search = 'template=blog_article,title|page_content*=' . $q . ',sort=-created,limit=10';
        $rockFinder = modules()->get('RockFinder3Master');

 

[bernhard]

What happens if you put that selector in a regular $pages->find() ?