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 identifiers, such as ids = [2,1,3], and when I request the Shoe model so that the results have identifiers in the identifiers list, I want to return: [{id: 2, asin: "111" }, {id: 1, asin: "113"}, {id: 3, asin: "42"}], but the problem is that using the following query statement does not preserve the original order, the results will be returned randomly. How to keep the order of the list that I filtered?

Invalid: Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()

Answer 1

If you have a reasonable small list of identifiers, you can simply run SQL queries for each identifier separately:

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

For a large number of identifiers, SQL queries take a lot of time. Then you better make one request and put the values ​​in the correct order in the second step (borrowed from how to select an object from the 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 means that the identifier is unique (which should be in your case). The first method throws an exception if there are several elements with the same identifier.

Answer 2

One way to solve this problem in the past was to use a SQL CASE expression to tell the database in which order I'd like the returned rows. Using your example:

 from sqlalchemy.sql.expression import case ordering = case( {id: index for index, id in enumerate(my_list_of_ids)}, value=Shoe.id ) Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).order_by(ordering).all() 

Answer 3

I also have the same problem using a MySQL database. This is what I did:

 my_list = [13,14,5,6,7] # convert my_list to str my_list_str = ','.join(map(str, my_list)) 

And here is what my query looks like:

 checkpoints = ( db_session.query(Checkpoint) .filter(Checkpoint.id.in_(my_list)) .order_by('FIELD(id, ' + my_list_str + ')') .all() ) 

FIELD () is a native function in MySQL.

EDIT: means your request should look like this:

 my_list_of_ids_str = ','.join(map(str, my_list_of_ids)) Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).order_by('FIELD(id, ' + my_list_of_ids_str + ')').all() 

Greetings

Answer 4

What do you mean when you say "original order"? The database does not have such a thing as the "source order". If you need some kind of order, you should add something like:

 .order_by(Shoe.id.desc()) 

If you do not specify an order, it is still possible that you will receive ordered data from the database. But in this case, the database simply uses an order that does not require unnecessary data manipulation. It just looks like ordered data, but it is not.