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.
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
);
});