Rails 6.1.3.1中否定嵌套查询返回空结果集的问题求助

问题原因 #

你遇到的where.not(id: ...)返回空结果的核心原因是SQL的三值逻辑：如果子查询Task.where(state: "started").select(:profile_id)中存在profile_id为NULL的记录，或者子查询返回空集时，NOT IN (...)的判断会失效。

例如，若子查询返回[1, 2, NULL]，那么profiles.id NOT IN (1,2,NULL)等价于profiles.id != 1 AND profiles.id !=2 AND profiles.id != NULL，而profiles.id != NULL的结果是unknown，整个WHERE条件不成立，最终返回空结果集。

正确写法 #

推荐使用NOT EXISTS替代NOT IN，它在处理NULL值时更可靠，且性能更优：

# 筛选没有任何"started"状态任务的Profile（包括无任务的Profile）
Profile.where.not(
  Task.where("tasks.profile_id = profiles.id")
      .where(state: "started")
      .exists
)

也可以用LEFT JOIN + IS NULL的方式实现，效果一致：

Profile.left_joins(:tasks)
       .where(tasks: { state: "started" })
       .where(tasks: { id: nil })

性能优化建议 #

1. 添加复合索引：给tasks表的state和profile_id字段建立复合索引，加速关联查询：

   # 在迁移文件中添加
   add_index :tasks, [:state, :profile_id]
   

2. 优先使用EXISTS/JOIN：数据库查询优化器对EXISTS和JOIN的支持优于子查询，能生成更高效的执行计划。
3. 避免不必要的子查询：如果不需要复用子查询逻辑，直接用关联查询替代嵌套子查询，可读性和性能都会更好。

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