user1835351 user1835351 - 1 year ago 213
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:

Rob Rob

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(
[next(s for s in shoes if == 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.