SQLAlchemy按列表中的字段过滤，但保持原始顺序? [英] Sqlalchemy filter by field in list but keep original order?

问题描述

我有一个像这样的Shoe模型:

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)

我有一个id列表，例如ids = [2,1,3]，当我在Shoe模型上查询时，结果在"ids"列表中有id时，我想返回: [{id:2，asin:"111"}，{id:1，asin:"113"}，{id:3，asin:"42"}]，但问题是使用以下查询语句不会保留原始顺序，结果将随机返回.如何保持筛选依据的列表顺序?

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?

一个不正确:Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()

推荐答案

如果您的ID列表比较合理，则可以对每个ID分别执行SQL查询:

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]

对于大量ID，SQL查询将花费很长时间.然后，您最好只进行一次查询，然后将值按正确的顺序进行第二步(借用如何从中选择对象在python中按其属性列出的对象列表):

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]

这是假设这些ID是唯一的(在您的情况下，它们应该是唯一的).如果有多个具有相同id的元素，则第一种方法将引发异常.

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.

这篇关于SQLAlchemy按列表中的字段过滤，但保持原始顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！