ORDER BY case

[LincolnLex44]

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]

Latitude doesn't have any helpers to specifically deal with case ... end, but it is possible to do this with expressions.

[LincolnLex44]

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: https://github.com/shadowhand/latitude/pull/79/commits/fb0d2ffd047285d950553eb9ab8d27b3ad15fc1d

        $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');