How to implement CASE WHEN under ORDER BY using CakePHP 3

Question

The SQL:

SELECT Students.name AS `Students__name`, Students.role AS 
`Students__role` FROM students Students
WHERE id IN 
  (1053,1056,733,734,735,736,737,739,748) 
  AND name LIKE '%v%' ORDER BY 
CASE 
  WHEN name LIKE '%v' THEN 1
  WHEN name LIKE 'v%' THEN 2
  WHEN name LIKE '%v%' THEN 3
  ELSE 4
END

So far:

$SInfo = TableRegistry::get('Students')->find()
->where(function($ex) use ($sArray, $sName) {
    $ex->in('id', $sArray);
    $ex->like('name', '%'.$sName.'%');
    return $ex;
});
->select(['name', 'role']);

order() function does not take ExpressionQuery. I have tried using $ex->addCase() under order but that didn't work either.

I think you can simply put the whole CASE statement in the `order()` method: `->order(['CASE WHEN ...'])` — arilia, Oct 31 '17 at 10:07
Please show how you used the expressions, passing them in the order clause should work just fine. — ndm, Oct 31 '17 at 13:43

SamHecquet · Accepted Answer · 2017-10-31T13:56:34.920

3

If you want to use a CASE statement in the order() method, you can write it like this:

$SInfo = TableRegistry::get('Students')->find()
    ->select(['name', 'role'])
    ->where(function($ex) use ($sArray, $sName) {
        $ex->in('id', $sArray);
        $ex->like('name', '%' . $sName . '%');
        return $ex; 
    })
    ->order('(CASE WHEN name LIKE \'%v\' then 1 when name LIKE \'v%\' then 2 when name LIKE \'%v%\' then 3 else 4 END)');

Edit: As suggested in the comment of this answer (thanks @ndm) you should use corresponding CASE expressions instead:

$SInfo = TableRegistry::get('Students')->find()
->select(['name', 'role'])
->where(function($ex) use ($sArray, $sName) {
    $ex->in('id', $sArray);
    $ex->like('name', '%' . $sName . '%');
    return $ex; 
})
->order(function ($exp, $q) {
    return $exp->addCase(
        [
            $q->newExpr()->like('name', '%v'),
            $q->newExpr()->like('name', 'v%'),
            $q->newExpr()->like('name', '%v%'),
        ],
        [1,  2, 3, 4], # values matching conditions
        ['integer', 'integer', 'integer', 'integer'] # type of each value
    );
});

edited Oct 31 '17 at 13:56

answered Oct 31 '17 at 13:14

SamHecquet

1,818
4
19
26

I would strongly recommend to use corresponding CASE expressions instead! **https://book.cakephp.org/3.0/en/orm/query-builder.html#case-statements** – ndm Oct 31 '17 at 13:42
@ndm I edited my answer based on your suggestion, thanks! – SamHecquet Oct 31 '17 at 13:57
I missed out on the part of using `newExpr()` and I tried directly iterating through with `$exp->like()`. Thank you for the right direction. – Keval Domadia Nov 01 '17 at 13:01
1

@SamHecquet Awarded bounty for a thoughtful quick canonical response. – Keval Domadia Nov 09 '17 at 13:55

How to implement CASE WHEN under ORDER BY using CakePHP 3

1 Answers1