如何正确使用SQLAlchemy的~或notin_子句？错误排查与解决

你遇到的这个问题其实是SQL连接逻辑和过滤条件匹配的典型误区，咱们一步步拆解清楚：

问题场景还原 #

先梳理下你的测试场景：

• 一共有4个Topic对象：<Topic 1>, <Topic 2>, <Topic 3>, <Topic 4>
• Highlight对象h1仅关联<Topic 1>, <Topic 3>
• 使用in_子句查询关联h1的Topic，结果完全正确：

in_query = Topic.query.join(
    highlights_topics, (highlights_topics.c.topic_id == Topic.id)
).filter(
    highlights_topics.c.highlight_id.in_([h1.id])
).all()
# 结果: [<Topic 1>, <Topic 3>]

但当你用~in_、notin_或!=做取反过滤时，期望得到<Topic 2>, <Topic 4>，实际却返回了全部4个Topic：

not_in_query = Topic.query.join(
    highlights_topics, (highlights_topics.c.topic_id == Topic.id)
).filter(
    ~highlights_topics.c.highlight_id.in_([h1.id])
).all()
# 错误结果: [<Topic 2>, <Topic 1>, <Topic 3>, <Topic 4>]

错误根源：INNER JOIN的逻辑和过滤条件不匹配 #

你用的join()是SQLAlchemy默认的内连接（INNER JOIN），内连接只会保留两张表中匹配关联条件的行。而你的取反逻辑犯了一个关键错误：

你想找的是「完全没有关联h1的Topic」，但内连接+取反过滤实际找的是「存在至少一个关联Highlight不是h1的Topic」

举个具体例子：假设<Topic 1>除了关联h1，还关联了另一个Highlight对象h2，那么highlights_topics表中会有两行记录：(topic_id=1, highlight_id=h1)和(topic_id=1, highlight_id=h2)。当你过滤highlight_id != h1.id时，第二行记录是符合条件的，所以<Topic 1>会被保留在结果里——这就导致本应排除的Topic被错误地包含进来。

哪怕某个Topic只关联了h1，只要数据库中存在其他Highlight，内连接的逻辑也不会帮你排除它，因为你没有限定「该Topic没有任何h1的关联记录」，只是限定「当前关联的Highlight不是h1」。

正确的解决方案 #

你最终采用的子查询方案是完全正确的，它精准命中了「找没有关联h1的Topic」的需求。再把代码贴出来加上注释：

# 子查询：先找出所有关联当前Highlight（self.id）的Topic ID
sub = db.session.query(Topic.id).outerjoin(
    highlights_topics, highlights_topics.c.topic_id == Topic.id
).filter(
    highlights_topics.c.highlight_id == self.id
)
# 主查询：取所有ID不在子查询结果中的Topic，就是完全没关联h1的Topic
q = db.session.query(Topic).filter(~Topic.id.in_(sub)).all()

另外还有一种等价写法：用左外连接+IS NULL，逻辑是左连接到关联表，然后过滤出「关联表中h1的记录不存在」的Topic：

q = Topic.query.outerjoin(
    highlights_topics, 
    (highlights_topics.c.topic_id == Topic.id) & (highlights_topics.c.highlight_id == h1.id)
).filter(
    highlights_topics.c.highlight_id.is_(None)
).all()

两种写法都能准确得到你想要的<Topic 2>, <Topic 4>结果。

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