Jump to content
bernhard

RockFinder3 - Combine the power of ProcessWire selectors and SQL

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

  • Like 6

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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! 🙂 

Share this post


Link to post
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...