Flask SQLAlchemy如何获取查询差集并对结果排序

直接在i0查询中追加notin_子查询条件，在数据库层完成过滤即可，无需先查询出两个结果集再做内存筛选，性能更高。

修正后代码 #

# 提取ir查询对应的source_id集合作为子查询
ir_source_ids = db.session.query(Ingredient.source_id).filter(
    Ingredient.ROrder.isnot(None),
    Ingredient.recipe_id == recipe_id
)

# 原i0查询追加过滤规则：排除source_id在ir结果集中的记录
i0 = db.session.query(Ingredient).filter(
    Ingredient.ROrder.isnot(None), 
    Ingredient.recipe_id == 0,
    Ingredient.source_id.notin_(ir_source_ids)
).order_by(Ingredient.ROrder)

# 原ir查询无需修改
ir = db.session.query(Ingredient).filter(
    Ingredient.ROrder.isnot(None), 
    Ingredient.recipe_id == recipe_id
).order_by(Ingredient.ROrder)

逻辑说明 #

以你提供的样例表、传入recipe_id=1为例：

• ir查询命中的记录source_id为C100、B121
• 追加过滤前i0查询命中ROrder非空、recipe_id=0的记录共2条，对应source_id恰好是C100、B121
• 追加过滤后这两条记录会被全部排除，i0返回结果为空，完全符合你的过滤规则

优化提示 #

• 若你的表中存在source_id为NULL的记录，建议在ir_source_ids子查询中追加Ingredient.source_id.isnot(None)条件，避免SQL中NOT IN遇到NULL值出现结果不符合预期的问题。
• 不要先将ir查询结果全部拉取到Python内存中再做列表推导过滤，该方式在数据量较大时会产生明显的性能损耗，子查询方案会将过滤逻辑下推到数据库执行，效率更高。

内容的提问来源于stack exchange，提问作者Shane S