SQLAlchemy ORDER BY FIELD ()

Question

I am trying to sort an SQLAlchemy ORM object by field, but with a specific order of values ​​(which is neither ascending nor descending). If I were to make this query in MySQL, it would look like this:

SELECT letter FROM alphabet_table WHERE letter in ('g','a','c','k') ORDER BY FIELDS( letter, 'g','a','c','k'); 

for output:

 letter ------ g a c k 

For SQLAlchemy, I tried things line by line:

session.query (AlphabetTable) .filter (AlphabetTable.letter.in _ (('g', 'a', 'c', 'k'))). Order_by (AlphabetTable.letter.in _ (('g', 'a', 'c', 'k')))

What doesn't work ... any ideas? This is a small one-time list of constants, and I could just create a table with order and then join, but that seems too big.

Answer 1

This may not be a very satisfactory solution, but how about using an expression instead of order by fields :

 sqlalchemy.orm.Query(AlphabetTable) \ .filter(AlphabetTable.letter.in_("gack")) \ .order_by(sqlalchemy.sql.expression.case(((AlphabetTable.letter == "g", 1), (AlphabetTable.letter == "a", 2), (AlphabetTable.letter == "c", 3), (AlphabetTable.letter == "k", 4)))) 

Answer 2

sqlalchemy func expression can be used to create an order by field clause:

 session.query(AlphabetTable) \ .filter(AlphabetTable.letter.in_("gack")) \ .order_by(sqlalchemy.func.field(AlphabetTable.letter, *"gack"))