mysql - 如何在 QueryExpression::addCase() 中使用表列而不是变量

Question

在 CakePHP 的新 ORM 中，您可以使用 QueryBuilder 构建(理论上)任何查询。

我想根据另一个值选择两列之一的值。在常规查询中，可以按如下方式完成:

SELECT IF(from_id = 1, to_id, from_id) AS other_id FROM messages;

我正在尝试使用 QueryBuilder 和 QueryExpression::addCase() 归档相同的查询

$messagesQuery = $this->Messages->find('all');
$messagesQuery->select([
    'other_id' => $messagesQuery->newExpr()->addCase(
        $messagesQuery->newExpr()->add(['from_id' => $this->authUser->id]),
        ['to_id', 'from_id'],
        ['integer', 'integer']
    )
]);

这不起作用，因为传递的值不是整数，而是包含整数的表列。

通过反复试验(再次使用方法add())，我得到了以下结果:

$messagesQuery = $this->Messages->find('all');
$messagesQuery->select([
    'other_id' => $messagesQuery->newExpr()->addCase(
        $messagesQuery->newExpr()->add(['from_id' => $this->authUser->id]),
        [
            $messagesQuery->newExpr()->add(['to_id']), 
            $messagesQuery->newExpr()->add(['from_id'])
        ],
        ['integer', 'integer']
    )
]);

这会产生以下查询:

SELECT (CASE WHEN from_id = 1 THEN to_id END) AS `other_id` FROM messages Messages

现在，ELSE 部分丢失了，尽管 CakePHP book states :

Any time there are fewer case conditions than values, addCase will automatically produce an if .. then .. else statement.

examples in the CakePHP book在这种情况下不是很有帮助，因为它们仅使用静态整数或字符串作为值，例如:

#SELECT SUM(CASE published = 'Y' THEN 1 ELSE 0) AS number_published, SUM(CASE published = 'N' THEN 1 ELSE 0) AS number_unpublished FROM articles GROUP BY published

$query = $articles->find();
$publishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'Y']), 1, 'integer');
$notPublishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'N']), 1, 'integer');

$query->select([
    'number_published' => $query->func()->sum($publishedCase),
    'number_unpublished' => $query->func()->sum($unpublishedCase)
])
->group('published');

有没有办法让方法 addCase 使用两个表列作为值而不仅仅是静态值？

Answer 1

事实证明，我在之前的编辑中距离解决方案仅差一步。

作为CakePHP book correctly states :

Any time there are fewer case conditions than values, addCase will automatically produce an if .. then .. else statement.

要实现这一点，条件和值都必须是一个数组，即使只有一个条件。 (CakePHP 书中没有说明这一点。)

这段代码:

$messagesQuery = $this->Messages->find('all');
$messagesQuery->select([
    'other_id' => $messagesQuery->newExpr()->addCase(
        [
            $messagesQuery->newExpr()->add(['from_id' => $this->authUser->id])
        ],
        [
            $messagesQuery->newExpr()->add(['to_id']), 
            $messagesQuery->newExpr()->add(['from_id'])
        ],
        ['integer', 'integer']
    )
]);

此查询的结果:

SELECT (CASE WHEN from_id = 1 THEN to_id ELSE from_id END) AS `other_id` FROM messages Messages

Eureka