CakePHP 3-如何在查询生成器中编写COALESCE（...）？ [英] CakePHP 3 - How to write COALESCE(...) in query builder?

问题描述

如何在查询生成器中编写这种 COALESCE（）语句？

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;
}

更新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();

返回值：

object(Cake\ORM\Entity) {

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

子级值为 NULL 并且父值是 1.00 ，因此我希望实体值是'value'=> （浮动）1.00 ，但我认为它是从查询中出来的，因为 FALSE 转换为（浮动）0 。

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.

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

更新3

我进行了另一项测试，并从两个表中选择了 name 字段，它只返回我输入到函数中的实际字符串（它们不会被评估为列名）。 ：

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'
    ])
]);

返回的实体具有：

'value' => 'Nodes.name'

所以我的新问题是如何让查询生成器评估字符串作为表/字段名称？

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

推荐答案

我无法获得Cake的 coalesce（）函数将参数评估为字段，它只是返回字段名称的实际字符串。

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.

我通过手动创建 COALESCE 语句。

// 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();

这篇关于CakePHP 3-如何在查询生成器中编写COALESCE（...）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！