Jump to content

RockFinder3 - Combine the power of ProcessWire selectors and SQL


bernhard

Recommended Posts

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.

Link to comment
Share on other sites

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

img

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

img

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

img

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

img

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

img

  • Like 2
  • Thanks 1
Link to comment
Share on other sites

  • 1 month later...
  • 4 weeks later...

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 ? 

A3knMWj.png

Happy finding ? 

  • Like 4
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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

 

LuMwjpJ.png

JAMUhHM.png

tPnyKAi.png

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?

RhCdyx5.png

  • Like 1
Link to comment
Share on other sites

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?

  • Haha 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

  • 3 weeks later...
  • 1 month later...

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

img

$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

img

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

img

  • Like 4
  • Thanks 2
Link to comment
Share on other sites

  • 3 months later...

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.

  • Like 1
Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ? 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
Share on other sites

  • 2 months later...

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

image.png.6bfceea2bceb1de99424526b24432bde.png

Error message with RockFinder

image.thumb.png.297082c9a7ffe9f54a9722608116c130.png

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...