Using IFNULL ... GROUP_CONCAT() in CakePHP 3

Question

I have a vanilla MySQL query as follows which works fine:

SELECT d.*, IFNULL(
     (SELECT GROUP_CONCAT(value) FROM display_substances `ds` 
         WHERE `ds`.`display_id` = `d`.`id`
         AND ds.substance_id = 2 
         GROUP BY `ds`.`display_id`
     ), 'Not Listed'
) `substances` FROM displays `d`;

The background to this is that I have 2 tables, displays and display_substances. I want to render a table of every row in displays and the corresponding values in display_substances for a given substance ID (represented by ds.substance_id = 2 in the query above).

Not every row in displays has a corresponding display_substances value. If this is the case it should output the words "Not Listed". This effectively means that if there is no corresponding row in display_substances - then there is no data for that display.id - therefore the data is "Not Listed" in the database for that particular row. The query above does exactly this.

I want to be able to write my query using CakePHP's ORM syntax.

The structures for the tables are as follows.

mysql> DESCRIBE displays;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| label    | varchar(255)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

mysql> DESCRIBE display_substances;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| display_id   | smallint(5) unsigned  | NO   | MUL | NULL    |                |
| substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| value        | text                  | NO   |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

The Table classes exist with the appropriate relationships defined.

// src/Model/Table/DisplaysTable.php
$this->hasMany('DisplaySubstances', [
    'foreignKey' => 'display_id'
]);

// src/Model/Table/DisplaysSubstancesTable.php
$this->belongsTo('Displays', [
   'foreignKey' => 'display_id',
   'joinType' => 'INNER'
]);

So far I have this:

    $substance_id = 2;

    $data = 
        $DisplaySubstances->find()
        ->contain(['Displays'])
        ->where(['DisplaySubstances.substance_id' => $substance_id)
        ->select(['Displays.id', 'Displays.label', 'Displays.anchor', 'DisplaySubstances.value'])
        ->enableHydration(false)->toArray();

This will get me the rows in displays which have corresponding values in display_substances for substance ID 2. This effectively is everything where we have data, but doesn't include any rows which are "Not Listed".

I don't know how to write the IFNULL...GROUP_CONCAT part of my vanilla SQL query using Cake's ORM syntax.

I've read How to use group_contact in cakephp query? so can see that it's possible to use GROUP_CONCAT in the ->select() condition. But the linked example is a lot more simple because it doesn't use an IFNULL condition and the corresponding action (returning "Not Listed").

Please can someone advise how to write this in Cake's ORM syntax, if this is possible?

CakePHP version is 3.5.18

Answer 1

IFNULL and GROUP_CONCAT are both SQL functions, so you'd use the functions builder, which can be used to create any function call you want, its magic call handler will create a generic function call if there's no concrete implementation for the invoked method, ie $functionsBuilder->IFNULL() and $functionsBuilder->GROUP_CONCAT() will just work.

The functions builder also accepts expressions, so you can pass another query object to your IFNULL() call, ie the subquery in your example SQL.

$subquery = $DisplaySubstances->find()
    ->select(function (\Cake\ORM\Query $query) {
        return [
            $query->func()->GROUP_CONCAT(['value' => 'identifier'])
        ];
    })
    ->where(function (\Cake\Database\Expression\QueryExpression $exp) use ($substance_id) {
        return [
            $exp->equalFields('DisplaySubstances.display_id', 'Displays.id'),
            'DisplaySubstances.substance_id' => $substance_id
        ];
    })
    ->group('DisplaySubstances.display_id');

$query = $Displays->find()
    ->select(function (\Cake\ORM\Query $query) use ($subquery) {
        // Before CakePHP 3.8.3 queries need to be wrapped in an additional expression
        // in order for the query builder to generate them wrapped in parentheses in SQL
        $subquery = $query->newExpr($subquery);

        return [
            'substances' => $query->func()->IFNULL([$subquery, 'Not Listed'])
        ];
    })
    ->select($Displays);

See also

• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions