Order by custom field values

[divan]

How to order by custom values, similar to sql's ORDER BY FIELD(name, 'value1', 'value2')?

Also, would be nice if it were possible to write more complex ordering logic, like "if field value contains 'red' - this goes first, if 'blue' - goes second, and the rest goes aftewards".

[elprans]

order by accepts arbitrary expressions, so you can use the if conditional:

order by
    0 if name = 'red' else
    1 if name = 'blue' else
    2

[divan]

@elprans thank you, fantastic. Works like a charm (with small fix s/name/.name/), and answering my second example solution is to use str::like:

... ORDER BY 
 0 if .name like "%red%" else 1

Worth adding these examples to the docs perhaps?

[elprans]

Worth adding these examples to the docs perhaps?

Yes, definitely. I made a ticket to improve the order by doc.