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

how write kind of coalesce() statement in query builder?

sql

select coalesce(n.value, p.value) value nodes n left join parents p on p.id = n.parent_id 

php

i can retrieve both child , parent values , go through result set , use parent 1 if child 1 empty, if there more elegant way build query itself, 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 have @ moment, 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 null , parent value 1.00 expect entity value 'value' => (float) 1.00 assume it's coming out of query false converted (float) 0.

update 2

it seems aliasing coalesce name exists normal field not work. requires unique field name coalesce result.

update 3

i did test , selected name field 2 tables instead, , returns actual strings entered function (they not evaluated column names):

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

the returned entity has:

'value' => 'nodes.name' 

so new question how query builder evaluate strings table/field names?

i not cake's coalesce() function evaluate parameters fields, returning actual strings of field names.

i got working manually creating coalesce statement instead.

// create query object first, can used create sql expression $query = $this->nodes->find();  // modify query $query     ->select([         'id',         'value' => $query->newexpr('coalesce(nodes.value, parents.value)')     ])     ->where(['nodes.id' => $id])     ->contain('parents')     ->first();