如何强制SQLAlchemy ORM在子查询中包含表?

Question

我有两个用户表和访问者表，内容如下:

用户:

+----+-------------+
| id |    email    |
+----+-------------+
|  1 | [e-mail] |
|  2 | [e-mail] |
+----+-------------+

游客:

+----+---------+------+
| id | user_id | addr |
+----+---------+------+
|  1 |  NULL   |  1   |
|  2 |  NULL   |  2   |
|  3 |  NULL   |  1   |
|  4 |  NULL   |  2   |
|  5 |  NULL   |  3   |
|  6 |    1    |  4   |
|  7 |  NULL   |  5   |
|  8 |  NULL   |  6   |
|  9 |    2    |  2   |
+----+---------+------+

我想获得表访问者的所有id表user中存储的链接到给定模式的电子邮件地址列表中不包含的地址.

例如:

SELECT visitors.id
FROM visitors
WHERE visitors.addr NOT IN (
SELECT DISTINCT visitors.addr
FROM user, visitors
WHERE user.email LIKE '%bar%' AND visitors.user_id = user.id
)

内部select语句将返回addr2因此，外部语句将返回6行没有add == 2的访问者id(即除了2,4和9之外的所有行)。我试着用SQLalchemy语句做这个:

subquery = (
session.query(Visitors.addr).distinct()
.filter(User.email.like('%bar%'), Visitors.user_id == User.id)
.subquery()
)
mainquery = session.query(Visitors.id).filter(Visitors.addr.notin_(subquery))

这是SQLalchemys ORM创建的SQL语句:

SELECT visitors.id AS visitors_id
FROM visitors
WHERE visitors.addr NOT IN (SELECT DISTINCT visitors.addr
FROM user
WHERE user.email LIKE ? AND visitors.user_id = user.id)

这里的关键区别在于子查询不再在FROM子句中包含访问者，这个缺失的表意味着访问者。即使分配了addr 2，也会返回Id 2和4。是否有一种方法可以强制SQLAlchemy在FROM子句中包含给定的表，或者我是否应该尝试使用JOIN来实现相同的结果?编辑:使用的SQLAlchemy版本是1.3.20。从那时起，我将其更新为1.4.23，以便能够使用@ian-wilson建议的scalar_subquery。使用的数据库分别为SQLite 3.35.5和MySQL 5.7.34。使用scalar_subquery函数不能解决我的问题，使用子查询的连接可以。

Answer 1

我会在这里使用您提到的连接，但您也应该考虑尝试scalar_subquery(1.4)或as_scalar(<1.4)来生成in_子句。

subquery = session.query(
Visitors.addr.distinct()
).join(
User,
Visitors.user_id == User.id
).filter(
User.email.like('%bar%')
).scalar_subquery()
mainquery = session.query(Visitors.id).filter(Visitors.addr.notin_(subquery))

https://docs.sqlalchemy.org/en/14/orm/query.html sqlalchemy.orm.Query.scalar_subquery