Rails中纯ActiveRecord实现多组AND/OR组合复杂查询的问题问询

我明白你遇到的问题了——用纯ActiveRecord构建带嵌套AND/OR的复杂查询时，很容易因为条件分组不对导致逻辑偏离预期。咱们一步步拆解原因和解决办法：

为什么纯ActiveRecord版本失效？ #

问题出在SQL运算符优先级和ActiveRecord的or方法默认行为上：

• SQL里AND的优先级比OR高，所以你的原代码生成的(A OR B AND C)会被解析成(A OR (B AND C))吗？不，不对——其实是((A OR B) AND C)？不，更准确地说，当你写where(A).or(where(B).where(C))时，ActiveRecord不会自动给B AND C加上括号，最终SQL会变成A OR B AND C，而根据优先级，这等价于A OR (B AND C)？不对，看你生成的SQL：("announcements"."user_id" = $1 OR "announcements"."school_id" IS NOT NULL AND "announcements"."classroom_id" IS NOT NULL)，这里的逻辑是「用户ID匹配 或者（学校ID非空 且 班级ID非空）」？不对，其实不对，因为OR两边的条件如果是多个AND，必须显式分组，否则当后续再叠加OR时，整个逻辑会彻底混乱。

本质原因是：你没有把每个OR分支里的所有AND条件封装成独立的查询对象，导致ActiveRecord无法正确识别哪些条件属于同一个OR分组。

正确的纯ActiveRecord实现方式 #

核心思路是：先把每个OR分支的所有AND条件单独构建成ActiveRecord::Relation对象，再用or把这些分支组合起来。这样ActiveRecord会自动为每个分支添加括号，保证SQL逻辑和你预期的一致。

scope :for_teacher_plain, ->(teacher) do
  # 先处理边界情况，避免空查询
  next none unless teacher.present? && teacher.teacher?

  # 逐个构建每个OR分支的查询（每个分支都是独立的Relation）
  owned_announcements = where(user_id: teacher.id)
  global_announcements = where(school_id: nil, classroom_id: nil)
  school_level_announcements = where.not(school_id: nil)
                                  .where(classroom_id: nil)
                                  .where(school: { school_teachers: { teacher_id: teacher.id } })
  classroom_level_announcements = where.not(school_id: nil)
                                    .where.not(classroom_id: nil)
                                    .where(classroom: { classroom_teachers: { teacher_id: teacher.id } })

  # 用or组合所有分支，每个分支会被自动加上括号
  owned_announcements
    .or(global_announcements)
    .or(school_level_announcements)
    .or(classroom_level_announcements)
    .left_outer_joins(school: :school_teachers, classroom: :classroom_teachers)
end

如果你用的是Rails 6+，还可以用更直观的查询块语法，用&&明确表示AND关系，ActiveRecord会自动帮你分组：

scope :for_teacher_plain, ->(teacher) do
  next none unless teacher.present? && teacher.teacher?

  where(user_id: teacher.id)
    .or(where { school_id.nil? && classroom_id.nil? })
    .or(where { school_id.not_nil? && classroom_id.nil? && school.school_teachers.teacher_id == teacher.id })
    .or(where { school_id.not_nil? && classroom_id.not_nil? && classroom.classroom_teachers.teacher_id == teacher.id })
    .left_outer_joins(school: :school_teachers, classroom: :classroom_teachers)
end

验证逻辑 #

这两种写法生成的SQL会和你原生SQL版本的逻辑完全一致：每个OR分支都会被包裹在括号里，比如：

WHERE (
  ("announcements"."user_id" = $1) 
  OR ("announcements"."school_id" IS NULL AND "announcements"."classroom_id" IS NULL) 
  OR ("announcements"."school_id" IS NOT NULL AND "announcements"."classroom_id" IS NULL AND "school_teachers"."teacher_id" = $2) 
  OR ("announcements"."school_id" IS NOT NULL AND "announcements"."classroom_id" IS NOT NULL AND "classroom_teachers"."teacher_id" = $3)
)

关键总结 #

• 纯ActiveRecord构建复杂OR查询时，必须把每个OR分支的所有AND条件封装成独立的Relation对象（要么单独赋值变量，要么用查询块）
• 不要直接链式调用多个where后直接用or连接，那样会因为SQL优先级导致逻辑混乱
• 用or组合独立的Relation时，ActiveRecord会自动为每个分支添加括号，保证分组正确

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