CakePHP 3 - How to write COALESCE(...) in query builder?

Question

How do I write this kind of COALESCE() statement in the query builder?

SQL

SELECT COALESCE(n.value, p.value) AS value
FROM nodes n
LEFT JOIN parents p ON p.id = n.parent_id

PHP

I can retrieve both the child and parent values and then go through the result set and just use the parent one if the child one is empty, but if there is a more elegant way to build it into the query itself, I would prefer that.

$child = $this->Nodes->find()
    ->select(['id', 'value'])
    ->where(['Nodes.id' => $id])
    ->contain([
        'Parents' => function ($q) {
            return $q->select('value');
        }
    ])
    ->first();

if (empty($child->value)) {
    $child->value = $child->parent->value;
}

Update 1

So this is what I have at the moment, but it doesn't work.

$child = $this->Nodes->find()
    ->select(['id', 'value'])
    ->where(['Nodes.id' => $id])
    ->contain([
        'Parents' => function ($q) {
            return $q->select([
                'value' => $q->func()->coalesce([
                    'Nodes.value',
                    'Parents.value'
                ])
            ]);
        }
    ])
    ->first();

Returns:

object(Cake\ORM\Entity) {

    'id' => (int) 234,
    'value' => (float) 0,
    '[new]' => false,
    '[accessible]' => [
        '*' => true
    ],
    '[dirty]' => [],
    '[original]' => [],
    '[virtual]' => [],
    '[errors]' => [],
    '[invalid]' => [],
    '[repository]' => 'Nodes'
}

The child value is NULL and the parent value is 1.00 so I would expect the entity value to be 'value' => (float) 1.00 but I assume it's coming out of the query as FALSE converted to (float) 0.

Update 2

It seems aliasing the coalesce to a name which already exists as a normal field does not work. It requires a unique field name for the coalesce result.

Update 3

I did another test and selected the name field from the two tables instead, and it just returns the actual strings I entered into the function (they do not get evaluated as column names):

return $q->select([
    'value' => $q->func()->coalesce([
        'Nodes.name',
        'Parents.name'
    ])
]);

The returned entity has:

'value' => 'Nodes.name'

So my new question would be how to get the query builder to evaluate the strings as table/field names?

Note that `COALESCE(n.value, p.value) AS value` is the preferred way of writing this (not that it helps your question at all!) — rjdown, Nov 10 '16 at 18:31
COALESCE is also supported by the ORM: http://api.cakephp.org/3.3/class-Cake.Database.FunctionsBuilder.html#_coalesce — floriank, Nov 10 '16 at 18:33
@BadHorsie I realise this was asked a long time ago but I'm trying to do the same thing in Cake 3.7. Did you manage to get it working through the ORM or did you stick with writing the expression manually? — Andy, Sep 05 '19 at 10:22
@Andy The only way I could get it to work was as per my answer below. It's still using the ORM I guess, but not very elegantly. — BadHorsie, Sep 05 '19 at 11:35
@BadHorsie thanks for replying. I've opened it here and got an answer where someone has shown how to use it with the ORM: https://stackoverflow.com/questions/57803785/cakephp-3-putting-un-necessary-parentheses-in-sql-causing-error/57804439#57804439 Hope this may help you as well as me! — Andy, Sep 05 '19 at 11:40
@Andy Yes, the default use of the `coalesce()` function wouldn't work for my particular case of trying to coalesce two field values from the query itself. It would appear that the `'identifier'` key/value pair is required to stop the names of my fields being evaluated as strings. Nobody told me that at the time though, and it wasn't documented anywhere in Cake. Maybe it's an addition in the past 3 years, and I found a bug at the time. I don't know. — BadHorsie, Sep 05 '19 at 11:55

score 4 · Accepted Answer · answered Nov 11 '16 at 12:02

I could not get Cake's coalesce() function to evaluate the parameters as fields, it was just returning the actual strings of the field names.

I got it working by manually creating the COALESCE statement instead.

// Create the query object first, so it can be used to create a SQL expression
$query = $this->Nodes->find();

// Modify the query
$query
    ->select([
        'id',
        'value' => $query->newExpr('COALESCE(Nodes.value, Parents.value)')
    ])
    ->where(['Nodes.id' => $id])
    ->contain('Parents')
    ->first();

score 2 · Answer 2 · answered Dec 07 '20 at 23:10

CakePHP's coalesce() uses the following format to differentiate field names from literal values:

                'value' => $q->func()->coalesce([
                    'User.last_name' => 'identifier',
                    ', ',
                    'User.first_name' => 'identifier'
                ])

The code above should yield results like Smith, John.

The default behavior is to treat the element as a literal.

See https://api.cakephp.org/3.3/class-Cake.Database.FunctionsBuilder.html#_coalesce

The docs don't explain this well at all. H/T to https://www.dereuromark.de/2020/02/06/virtual-query-fields-in-cakephp/ for a clear example.

score 0 · Answer 3 · answered Nov 10 '16 at 18:38

0

See http://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions

Haven't tried it but I guess it's:

$child = $this->Nodes->find()
    ->select(['id', 'value'])
    ->where(['Nodes.id' => $id])
    ->contain([
        'Parents' => function ($q) {
            return $q->select(['value' => $query->func()->coalesce([
                /* Fields go here... I think. :) */
            ])]);
        }
    ])
    ->first();

If this isn't working check the unit tests of the core how to call this function.

answered Nov 10 '16 at 18:38

floriank

25,546
9
42
66

Thanks *burzum*. I can't get it to work unfortunately. I found a couple of problems with it, the one at the moment being that I can't get the `coalesce()` to evaluate my field names as fields. It just gives the string that I put into the array. See updated question. – BadHorsie Nov 11 '16 at 11:44

CakePHP 3 - How to write COALESCE(...) in query builder?

3 Answers3

Linked