Sqlalchemy get results in the same order

Question

I am using this query

users = User.query.filter(User.id.in_(user_ids)).all()

I want to get the users in the same order as in user_ids. Is there anyway I can do this directly in the query or will I have to sort it again? Also if I have to sort it again, what is the pythonic way of sorting the lists of objects based on another list of IDs.

Well I have found the pythonic way of sorting it. users.sort(key=lambda x: user_ids.index(x.id)) but still looking if its possible to do it directly in the query — Waseem, Mar 28 '15 at 21:15
So you have your `user_ids` in an arbitrary order (not necessarily sorted), and you want to order the SQL results based on the `User.id` column and that `user_ids` list? I.e. not just `ORDER BY "User.id"`? — Lukas Graf, Mar 28 '15 at 21:38
I have seen that DB does not return me the results in the order i give it the user_ids list. So yes I want to sort the results based on used_ids list and not ORDER BY "User.id" — Waseem, Mar 28 '15 at 21:41
Well, unless you add an `.order_by()` clause the results are just going to be ordered in insertion order. *If* your `user_ids` is already sorted, then adding an `.order_by(User.id)` clause will obviously have the same effect as ordering by `user_ids` (ignoring collation details). But if it's not, and you still want to order by it, that's going to be very complicated to do directly in SQL, so I'd go with sorting them afterwards in Python. — Lukas Graf, Mar 28 '15 at 21:46
Ok then I will go with pythonic way. Thanks for the explanation. — Waseem, Mar 28 '15 at 21:48
You're welcome. I would assume doing it in SQL could be achieved by using [hybrid properties](http://stackoverflow.com/questions/27521645/sqlalchemy-order-by-function-result), but I imagine this could be rather tricky to implement in a cross-DB portable way. — Lukas Graf, Mar 28 '15 at 21:51
Anything is rather tricky to implement in a cross-DB portable way if one counts MySQL in... — Antti Haapala -- Слава Україні, Mar 28 '15 at 22:13

Antti Haapala -- Слава Україні · Accepted Answer · 2015-03-28T22:18:59.880

This is very possible, though ugly, with PostgreSQL 9.4+ and SQLAlchemy 0.9.7+. Basically we build a map of id -> ordinal, then use the JSONB -> operator to find the ordinal for each user id (-> requires TEXT for JSON keys

from sqlalchemy.sql.postgresql import JSONB, TEXT
from sqlalchemy.sql import cast

id_order = { str(v): k for k, v in enumerate(user_ids) }
users = User.query.filter(User.id.in_(user_ids)).\
    order_by(cast(id_order, JSONB)[cast(User.id, TEXT)])

This will create SQL ORDER BY similar to

ORDER BY CAST('{"1": 2, "3": 0, "2": 1}' AS JSONB) ->
         CAST(user.id AS TEXT)

For other databases just sort it in the client - though it means you cannot browse through the results.

The efficient code to sort on client is:

id_order = { v: k for k, v in enumerate(user_ids) }
users = sorted(users, key=lambda u: id_order.get(u.id))

Sqlalchemy get results in the same order

1 Answers1