Jump to content

RockFinder3 - Combine the power of ProcessWire selectors and SQL


bernhard
 Share

Recommended Posts

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

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

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

@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) 🙂

Link to comment
Share on other sites

  • 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

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
 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...