Rails ActiveRecord Where With AND OR Conditions

Question

In Rails I'm trying to write a complex query like this

where("A OR (B AND C) OR (D AND E AND F) OR (G AND H AND I))"

However struggling to make it work using just ActiveRecord without SQL. The following scope using SQL produces the correct results.

scope :for_teacher_sql, ->(teacher) { 
  where("(announcements.user_id = :user_id) 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 = :user_id) OR " + 
        "(announcements.school_id IS NOT NULL AND announcements.classroom_id IS NOT NULL AND classroom_teachers.teacher_id = :user_id)", user_id: teacher.id ).
  left_outer_joins(school: :school_teachers).left_outer_joins(classroom: :classroom_teachers) if teacher.present? }

And the generated SQL looks correct.

SELECT "announcements".* FROM "announcements" 
LEFT OUTER JOIN "schools" ON "schools"."deleted_at" IS NULL AND "schools"."id" = "announcements"."school_id" 
LEFT OUTER JOIN "school_teachers" ON "school_teachers"."school_id" = "schools"."id" 
LEFT OUTER JOIN "classrooms" ON "classrooms"."deleted_at" IS NULL AND "classrooms"."id" = "announcements"."classroom_id" 
LEFT OUTER JOIN "classroom_teachers" ON "classroom_teachers"."classroom_id" = "classrooms"."id" 
WHERE "announcements"."deleted_at" IS NULL AND (
  (announcements.user_id = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0') 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 = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0') OR 
  (announcements.school_id IS NOT NULL AND announcements.classroom_id IS NOT NULL AND classroom_teachers.teacher_id = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0'))

Doing a hybrid ActiveRecord with SQL seems to produce the correct results as well.

scope :for_teacher_hybrid, ->(teacher) { 
  where("announcements.user_id = ?", teacher.id).or(
  where("announcements.school_id IS NULL AND announcements.classroom_id IS NULL")).or(
  where("announcements.school_id IS NOT NULL AND announcements.classroom_id IS NULL AND school_teachers.teacher_id = ?", teacher.id)).or(
  where("announcements.school_id IS NOT NULL AND announcements.classroom_id IS NOT NULL AND classroom_teachers.teacher_id = ?", teacher.id)).
  left_outer_joins(school: :school_teachers).left_outer_joins(classroom: :classroom_teachers) if teacher.present? }

Although the generated SQL has some additional brackets.

SELECT "announcements".* FROM "announcements" 
LEFT OUTER JOIN "schools" ON "schools"."deleted_at" IS NULL AND "schools"."id" = "announcements"."school_id" 
LEFT OUTER JOIN "school_teachers" ON "school_teachers"."school_id" = "schools"."id" 
LEFT OUTER JOIN "classrooms" ON "classrooms"."deleted_at" IS NULL AND "classrooms"."id" = "announcements"."classroom_id" 
LEFT OUTER JOIN "classroom_teachers" ON "classroom_teachers"."classroom_id" = "classrooms"."id" WHERE "announcements"."deleted_at" IS NULL AND (
  (
    (
      (announcements.user_id = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0') 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 = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0')
  ) OR 
  (announcements.school_id IS NOT NULL AND announcements.classroom_id IS NOT NULL AND classroom_teachers.teacher_id = '80e699e6-77f2-4e13-8cef-02c9d8ee2ac0')
)

However I can't get it to work just using ActiveRecord without SQL.

scope :for_teacher_plain, ->(teacher) { 
  where(user_id: teacher.id).or(
  where.not(school: nil).where.not(classroom: nil)).or(
  where.not(school: nil).where(classroom: nil).where(school: { school_teachers: { teacher_id: teacher.id } })).or(
  where.not(school: nil).where.not(classroom: nil).where(classroom: { classroom_teachers: { teacher_id: teacher.id } })).
  left_outer_joins(school: :school_teachers).left_outer_joins(classroom: :classroom_teachers) if teacher.present? && teacher.teacher? }

The AND queries are not grouped inside the OR conditions, so I must not be writing them correctly.

SELECT "announcements".* FROM "announcements" 
LEFT OUTER JOIN "schools" ON "schools"."deleted_at" IS NULL AND "schools"."id" = "announcements"."school_id" 
LEFT OUTER JOIN "school_teachers" ON "school_teachers"."school_id" = "schools"."id" 
LEFT OUTER JOIN "classrooms" ON "classrooms"."deleted_at" IS NULL AND "classrooms"."id" = "announcements"."classroom_id" 
LEFT OUTER JOIN "classroom_teachers" ON "classroom_teachers"."classroom_id" = "classrooms"."id" 
WHERE "announcements"."deleted_at" IS NULL AND (
  (
    ("announcements"."user_id" = $1 OR "announcements"."school_id" IS NOT NULL AND "announcements"."classroom_id" IS NOT 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
)

Note, I'm using paranoia which is why the deleted_at IS NULL statements are being included.

I can fallback to using the SQL version but I'd really like to understand why the pure ActiveRecord isn't working, and how to write complex AND with OR conditions like this.

Rails ActiveRecord Where With AND OR Conditions

0 Answers