ORDER BY case
riseoflex88 opened this issue · 2 comments
riseoflex88 commented
I'm looking to do something similar to this... https://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order
Where I already know the specific order to apply before creating the query.
In raw sql this would be written like so...
WHERE
id IN ('a','b','c') ...
ORDER BY
CASE id
WHEN 'a' THEN 3
WHEN 'b' THEN 1
WHEN 'c' THEN 2
END, id
shadowhand commented
Latitude doesn't have any helpers to specifically deal with case ... end
, but it is possible to do this with expressions.
riseoflex88 commented
I thought so too, but when I tried using express I get the following error:
Argument 1 passed to Latitude\\QueryBuilder\\order() must be of the type string, object given
I can't find many examples of using express in situ, so I've gone off of the snippet here: fb0d2ff
$orderby_case = "ORDER BY CASE id \n";
foreach ($programme_ids as $id => $order)
{
$orderby_case .= "WHEN '$id' THEN $order \n";
}
$orderby_case .= "END, id";
$query->orderBy(express($orderby_case), 'DESC');