如何在SqlAlchemy中按joinloaded表过滤? [英] How to filter by joinloaded table in SqlAlchemy?

问题描述

假设我有 2 个模型，Document 和 Person.Document 通过owner"属性与 Person 建立关系.现在:

Lets say I got 2 models, Document and Person. Document got relationship to Person via "owner" property. Now:

session.query(Document)\
    .options(joinedload('owner'))\
    .filter(Person.is_deleted!=True)

将双重连接表Person.将选择一个人表，并过滤加倍的表，这不是我想要的，因为这样文档行将不会被过滤.

Will double join table Person. One person table will be selected, and the doubled one will be filtered which is not exactly what I want cuz this way document rows will not be filtered.

如何对连接加载的表/模型应用过滤器?

What can I do to apply filter on joinloaded table/model ?

推荐答案

你是对的，表 Person 将在结果 SQL 中使用两次，但每个服务于不同的目的:

You are right, table Person will be used twice in the resulting SQL, but each of them serves different purpose:

• 一个是过滤条件:filter(Person.is_deleted != True)
• 另一个是预先加载关系:options(joinedload('owner'))

但是您的查询返回错误结果的原因是您的过滤条件不完整.为了让它产生正确的结果，你还需要JOIN这两个模型:

But the reason your query returns wrong results is because your filter condition is not complete. In order to make it produce the right results, you also need to JOIN the two models:

qry = (session.query(Document).
        join(Document.owner). # THIS IS IMPORTANT
        options(joinedload(Document.owner)).
        filter(Person.is_deleted != True)
        )

这将返回正确的行，即使它仍然有 2 个对 Person 表的引用(联接).您查询的真正解决方案是使用 contains_eager 而不是 joinedload:

This will return correct rows, even though it will still have 2 references (JOINs) to Person table. The real solution to your query is that using contains_eager instead of joinedload:

qry = (session.query(Document).
        join(Document.owner). # THIS IS STILL IMPORTANT
        options(contains_eager(Document.owner)).
        filter(Person.is_deleted != True)
        )

这篇关于如何在SqlAlchemy中按joinloaded表过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！