Search code examples
cakephpsql-order-byquery-buildercakephp-3.x

CakePHP 3.x case statement on order

I am trying to use a CASE statement on the order of a MySQL statement in CakePHP 3.x app. The simple select is as follows:

$articles = $this->Articles->find()
    ->where($conditions)   
    ->order(function ($exp, $q) {
        return $exp->addCase(
            [
                $q->newExpr()->gt('Articles.modified', (new Time())->subDays(365)) // article has been updated in the last x days
                    ],
            ['priority'], # values matching conditions
            ['string'] # type of each value
        );
    })
->limit(15)
->all();

The following SQL is generated:

SELECT `Articles`.`id` AS `Articles__id`, .... 
FROM `articles` `Articles` 
WHERE (`publish` < :c0 AND `Articles`.`publish` > :c1) 
ORDER BY CASE WHEN `Articles`.`modified` > :c2 THEN :param3 END LIMIT 15

The case statement is not correct because it is missing the DESC order which should come after the 'END' - see this fiddle:

http://sqlfiddle.com/#!9/8df161/5

I'm not sure if this is a limitation with how CakePHP handles CASE?

Further I require a second order after the case statement to order by 'publish' desc.

Solution

  • Expressions passed to Query::order() must generate everything required by the ORDER BY clause, including the direction keyword.

    If the expression that you're using doesn't support that, then you can use Query::oderAsc() or Query::oderDesc(), which will append the respective direction keyword accordingly.

    $query = $this->Articles->find();
$query
    ->where($conditions)
    ->orderDesc(
        $query->newExpr()->addCase(/* ... */)
    )
    // ...

    See also