代码之家 › 专栏 › 技术社区 › Trip

如何编写Rails SQL查询来查找所有子对象值相等的对象

relational-algebra activerecord postgresql ruby-on-rails sql

1

Trip · 技术社区 · 8 年前

我一直在读这篇文章,但无法将其写入Rails范围:

find all parent records where all child records have a given value (but not just some child records)

我有一个 Course Section Quiz ,对象:

class Course < ActiveRecord::Base
  has_many :course_members
  has_many :members, through: :course_members
  has_many :sections
  has_many :quizzes, through: :sections
end

class Quiz < ActiveRecord::Base
  belongs_to :member
  belongs_to :section
end

class Section < ActiveRecord::Base
  belongs_to :course
  has_many :quizzes
end

我想找到一个成员的所有课程,其中所有与该课程相关的测验都具有该属性 completed = true .

所以在我的 Member 同学们,我最想写的是:

has_many :completed_courses, -> { 
   joins(:courses, :quizzes, :sections)
   # .select( 'CASE WHEN quizzes.completed = true then 1 end') ??? maybe ???
}, class_name: 'Course'

哈哈!但除非太复杂。我一直试图把这写在 课程 也可以。

1 回复 | 直到 8 年前

1

2

Jay-Ar Polidario 8 年前

class Member < ActiveRecord::Base
  has_many :courses, through: :course_members
  has_many :course_members

  has_many :completed_courses,
    -> { joins(:quizzes).where.not(quizzes: {completed: [false, nil]}) },
    through: :course_members,
    source: :course
end

如果你的 completed 布尔列为 NOT NULL ,然后改变 [false, nil] 以上只是简单 false

使用示例

irb(main):002:0> Member.first.completed_courses
  Member Load (0.2ms)  SELECT "members".* FROM "members" ORDER BY "members"."id" ASC LIMIT 1
  Course Load (0.1ms)  SELECT "courses".* FROM "courses" INNER JOIN "sections" ON "sections"."course_id" = "courses"."id" INNER JOIN "quizzes" ON "quizzes"."section_id" = "sections"."id" INNER JOIN "course_members" ON "courses"."id" = "course_members"."course_id" WHERE (NOT (("quizzes"."completed" = 'f' OR "quizzes"."completed" IS NULL))) AND "course_members"."member_id" = ?  [["member_id", 1]]