sqlalchemy - 加入具有 2 个条件的子表 [英] sqlalchemy - join child table with 2 conditions

问题描述

如何在连接 2 个表时向 ON 子句添加 2 个条件.我有 3 个层次结构的三个表，每个表都带有已删除的标志.我还必须在单个查询中加入所有这些表，并根据已删除标志进行过滤.目前，条件被添加到查询的 where 子句中，它不会过滤已删除的记录.它需要添加到 ON 子句中.请提出建议.

How to add 2 conditions to the ON clause when you join 2 tables. I have 3 three tables in hierarchy each with the deleted flag. I have to join all these table in a single query and filter based on deleted flag also. Currently the conditions gets added to the where clause of the query, which does not filter the deleted records. It needs to be added to the ON clause. Please suggest.

我目前的查询如下:

result = session.query(Host).filter(and_(Host.id.in_(ids), Host.deleted == False)).\
    join(Switch).filter(Switch.deleted == False).\
    join(Port).filter(Port.deleted == False).\
    options(joinedload('switches')).\
    options(joinedload('ports')).\
    all()

谢谢

推荐答案

尝试 contains_eager 而不是joinedload.可能发生的情况是，您有 4 个连接，您使用 join 定义了两个连接，然后是 options(joinedload(...)) 中的两个连接

Try contains_eager instead of joinedload. What is probably happening is that you have 4 joins the two you defined with join and then then the two from the options(joinedload(...))

修改你的代码，应该给这个:

Modifying your code, should give this:

from sqlalchemy import and_

result = (session.query(Host).filter(and_(Host.id.in_(ids), Host.deleted == False)).
    join(Switch, and_(Switch.host_id==Host.id, Switch.deleted == False)).
    join(Port, and_(Port.switch_id==Switch.id, Port.deleted == False)).
    options(contains_eager('switches')).
    options(contains_eager('ports')).
    all()
)

这篇关于sqlalchemy - 加入具有 2 个条件的子表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！