Cakephp3 case mysql statement is not creating the correct query

Question

I'm trying to create a query that returns the sum of a column using a case (it has logged time and the format in either minutes or hours, if it's in hours, multiply by 60 to convert to minutes). I'm very close, however the query is not populating the ELSE part of the CASE. The finder method is:

public function findWithTotalTime(Query $query, array $options)
{

    $conversionCase = $query->newExpr()
        ->addCase(
            $query->newExpr()->add(['Times.time' => 'hours']),
            ['Times.time*60', 'Times.time'],
            ['integer', 'integer']
        );
  return $query->join([
    'table' => 'times',
    'alias' => 'Times',
    'type' => 'LEFT',
    'conditions' => 'Times.category_id = Categories.id'
  ])->select([
    'Categories.name',
    'total' => $query->func()->sum($conversionCase)
  ])->group('Categories.name');
}

The resulting query is:

SELECT Categories.name AS `Categories__name`, (SUM((CASE WHEN 
Times.time = :c0 THEN :c1 END))) AS `total` FROM categories Categories 
LEFT JOIN times Times ON Times.category_id = Categories.id GROUP BY 
Categories.name

It's missing the ELSE statement before the CASE end, which according to the API docs:

...the last $value is used as the ELSE value...

https://api.cakephp.org/3.3/class-Cake.Database.Expression.QueryExpression.html

I know there might be a better way to do this, but at this point I'd like to at least know how to do CASE statements properly using the built in QueryBuilder.

Answer 1

Both arguments must be arrays

Looks like there are some documenation issues in the Cookbook, and the API could maybe be a little more clear on that subject too. Both, the $conditions argument as well as the $values argument must be arrays in order for this to work.

Enforcing types ends up with casting values

Also you're passing the SQL expression wrong, including the wrong types, defining the types as integer will cause the data passed in $values to be casted to these types, which means that you will be left with 0s.

The syntax that you're using is useful when dealing with user input, which needs to be passed safely. In your case however you want to pass hardcoded identifiers, so what you have to do is to use the key => value syntax to pass the values as literals or identifiers. That would look something like:

'Times.time' => 'identifier'

However, unfortunately there seems to be a bug (or at least an undocumented limitation) which causes the else part to not recognize this syntax properly, so for now you'd have to use the manual way, that is by passing proper expression objects, which btw, you may should have done for the Times.time*60 anyways, as it would otherwise break in case automatic identifier quoting is being applied/required.

tl;dr, Example time

Here's a complete example with all forementioned techniques:

use Cake\Database\Expression\IdentifierExpression;

// ...

$conversionCase = $query
    ->newExpr()
    ->addCase(
        [
            $query->newExpr()->add(['Times.time' => 'hours'])
        ],
        [
            $query
                ->newExpr(new IdentifierExpression('Times.time'))
                ->add('60')
                ->tieWith('*'), // setConjunction() as of 3.4.0
            new IdentifierExpression('Times.time')
        ],
    );

If you were for sure that you'd never ever make use of automatic identifier quoting, then you could just pass the multiplication fragment as:

'Times.time * 60' => 'literal'

or:

$query->newExpr('Times.time * 60')

See also

• Cookbook > Database Access & ORM > Query Builder > Case statements
• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
• API > \Cake\Database\Expression\QueryExpression::add()
• API > \Cake\Database\Expression\QueryExpression::tieWith()