user1835351 user1835351 - 5 months ago 126
Python Question

Sqlalchemy filter by field in list but keep original order?

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()

Rob Rob
Answer

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.

Comments