如何在Python/SqlAlchemy查询中添加AND EXISTS(SELECT...)条件？

问题描述 #

你现有如下SQLAlchemy查询：

results = (
    session.query(MyFirstTable)
    # 省略若干.join()和.filter()
    .all()
)

想要添加额外的.filter()条件，对应的SQL写法如下：

SELECT * FROM MyFirstTable
    -- 省略INNER JOIN和WHERE条件
    AND EXISTS (
        SELECT * FROM MyOtherTable
            WHERE MyFirstTable.ID = MyOtherTable.myFirstTableID
            AND MyOtherTable.OtherThing = 42
    )

需求是：仅当MyFirstTable的记录主键ID与MyOtherTable中存在匹配记录，且该匹配记录满足OtherThing=42时，才返回该MyFirstTable记录（仅关心是否存在匹配，不关心数量）。询问能否在最终.all()之前添加.filter()实现该逻辑？

解决方案 #

完全可以实现。你可以借助SQLAlchemy的exists()构造子查询，然后把它作为条件传入.filter()中。

具体代码示例如下：

from sqlalchemy import exists

# 构造EXISTS子查询
subquery = exists().where(
    (MyOtherTable.myFirstTableID == MyFirstTable.ID) &
    (MyOtherTable.OtherThing == 42)
)

results = (
    session.query(MyFirstTable)
    # 保留原有的.join()和.filter()
    .filter(subquery)
    .all()
)

这段代码会生成你需要的SQL语句：通过exists()构造的子查询，检查MyFirstTable的ID是否在MyOtherTable中有对应的记录且满足OtherThing=42，只有符合条件的MyFirstTable记录才会被返回，完全匹配你的需求。

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