Sqlalchemy filter by field in list but keep original order?

Question

I have a Shoe model like this:

class Shoe(db.Model):
id = db.Column(db.Integer, primary_key = True)
asin = db.Column(db.String(20), index = True)

I have a list of ids like ids = [2,1,3] and when I query on the Shoe model such that the results have ids in the 'ids' list, I want back: [{id:2, asin:"111"},{id:1, asin:"113"},{id:3, asin:"42"}] but the problem is that using the following query statement doesn't preserve the original order, the results will come back random. How do I keep the order of the list I filtered by?

Incorrect one: Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()

Answer 1

If you have a reasonable small list of ids, you could just perform SQL queries on each id individually:

[Shoe.query.filter_by(id=id).one() for id in my_list_of_ids]

For a large number of ids, SQL queries will take a long time. Then you are better off with a single query and putting the values in the correct order in a second step (borrowed from how to select an object from a list of objects by its attribute in python):

shoes = Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()
[next(s for s in shoes if s.id == id) for id in my_list_of_ids]

This is assuming the id's are unique (which they should be in your case). The first method will raise an exception if there are multiple elements with the same id.