php cakephp case query-builder cakephp-3.x

Case statement with CakePHP Query Builder causes `array_combine()` and type error

I want to write the following query in CakePHP using the Query Builder:

SELECT
(CASE
    WHEN `ai` = 1 THEN CONCAT_WS(" ", `name`, " (AI)") ELSE `name`
END) AS `name`
FROM `drivers`;

I've tried a few ways, but always seem to fail. Firstly, I tried this:

return $query->select(function(Query $query) {
    return $query->newExpr()->addCase([
            $query->newExpr()->add(['ai' => 1])
        ], [
            $query->newExpr()->add(['name' => 'CONCAT_WS(" ", `name`, " (AI)")'])
    ]);
});

And I've tried this:

return $query->select(function(Query $query) {
    return $query->newExpr()->addCase([
            $query->newExpr()->add(['ai' => 1])
        ], [
            $query->newExpr()->add(['name' => $query->func()->concat([" ", 'name', " (AI)"])])
    ]);
});

but in both instances, I get this error:

Warning (2): array_combine(): Both parameters should have an equal number of elements [CORE/src/ORM/ResultSet.php, line 528]

Argument 1 passed to Cake\ORM\Entity::__construct() must be of the type array, bool given, called in /var/www/html/vendor/cakephp/cakephp/src/ORM/ResultSet.php on line 602

So both of those result in the same query, but it's still wrong...

Solution

You're not selecting any aliases, the CASE expression will simply sit in the SELECT clause as is, which the ORM doesn't like, as it tries to match the select list keys to the retrieved row's keys.

This could probably need a better, more helpful error message, or maybe the ORM could even warn when compiling the query, not sure.

Anyways, long story short, return an array with an alias, like this:

return $query->select(function(Query $query) {
    $case = $query->newExpr()->addCase(
        [
            // WHEN
            $query->newExpr()->add(['ai' => 1])
        ],
        [
            // THEN
            $query->func()->concat([' ', $query->identifier('name'), ' (AI)']),
            // ELSE
            $query->identifier('name')
        ]
    );

    return [
        'name' => $case,
    ];
});

Also note that you need to pass in the CONCAT() function expression directly, do not wrap it in another expression that assigns the result to something, that won't work. Furthermore you need to make sure that identifiers are passed as such, currently your name value would be bound as a quoted string literal, and you also need a second identifier value for the ELSE part.