Ecto查询中where not in数组仍返回关联行问题排查

问题场景 #

在Phoenix项目里用Ecto构建数据查询逻辑，表A与表B为多对多关系，通过中间表C关联。需求是找出所有表A中，没有关联任何name在exclude数组内的表B记录的数据。

现状问题 #

当前代码中，匹配include数组的where条件能正常生效，但排除exclude数组的条件完全不起作用——用IO.inspect查看生成的查询语句，确认该条件已被加入，但查询结果依然包含了关联exclude表B的表A记录。

当前代码 #

# include : [string]
# exclude : [string]

query
|> join(:inner, [a], ba in assoc(a, :bs))
|> join(:inner, [_, ba], b in assoc(ba, :as))
|> where([_, b], b.name in ^include)
|> where([_, b], b.name not in ^exclude)
|> group_by([a], a.id)

问题原因 #

你当前的写法逻辑存在偏差：使用inner join后过滤b.name not in ^exclude，只会剔除那些关联的表B记录中name在exclude里的条目，但如果某个表A同时关联了符合include和exclude的表B记录，这条表A依然会被查询出来——因为只要存在一条符合include且不在exclude的关联记录，inner join就会保留该表A，无法彻底排除那些关联过exclude表B的表A。

解决方案 #

要实现“表A不存在与exclude数组内name的表B关联”的需求，需要直接排除掉那些存在exclude关联的表A，推荐两种实现方式：

方案一：NOT EXISTS子查询 #

# include : [string]
# exclude : [string]

# 先查出所有关联了exclude表B的表A ID
exclude_subquery = 
  from b in B,
    join: ba in C, on: ba.b_id == b.id,
    where: b.name in ^exclude,
    select: ba.a_id

query
|> join(:inner, [a], ba in assoc(a, :bs))
|> join(:inner, [_, ba], b in assoc(ba, :as))
|> where([_, b], b.name in ^include)
# 排除掉所有在子查询里的表A ID
|> where([a], a.id not in subquery(exclude_subquery))
|> group_by([a], a.id)

方案二：LEFT JOIN + IS NULL #

query
|> join(:inner, [a], ba in assoc(a, :bs))
|> join(:inner, [_, ba], b in assoc(ba, :as))
|> where([_, b], b.name in ^include)
# 左关联所有符合exclude条件的表B记录
|> left_join([a], ba_exclude in C, on: ba_exclude.a_id == a.id)
|> left_join([_, _, ba_exclude], b_exclude in B, on: b_exclude.id == ba_exclude.b_id and b_exclude.name in ^exclude)
# 只保留那些没有匹配到exclude关联的表A
|> where([_, _, _, b_exclude], is_nil(b_exclude.id))
|> group_by([a], a.id)

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