Rails 4中的左外连接 [英] LEFT OUTER JOIN in Rails 4

问题描述

我有 3 个模型:

class Student < ActiveRecord::Base
  has_many :student_enrollments, dependent: :destroy
  has_many :courses, through: :student_enrollments
end

class Course < ActiveRecord::Base   
    has_many :student_enrollments, dependent: :destroy
    has_many :students, through: :student_enrollments
end

class StudentEnrollment < ActiveRecord::Base
    belongs_to :student
    belongs_to :course
end

我希望查询 Courses 表中的课程列表，这些课程在 StudentEnrollments 表中不存在且与某个学生相关联.

I wish to query for a list of courses in the Courses table, that do not exist in the StudentEnrollments table that are associated with a certain student.

我发现也许 Left Join 是要走的路，但似乎 rails 中的 joins() 只接受一个表作为参数.我认为会做我想做的 SQL 查询是:

I found that perhaps Left Join is the way to go, but it seems that joins() in rails only accept a table as argument. The SQL query that I think would do what I want is:

SELECT *
FROM Courses c LEFT JOIN StudentEnrollment se ON c.id = se.course_id
WHERE se.id IS NULL AND se.student_id = <SOME_STUDENT_ID_VALUE> and c.active = true

如何以 Rails 4 方式执行此查询?

How do I execute this query the Rails 4 way?

感谢任何输入.

推荐答案

您也可以传递一个字符串，即 join-sql.例如 joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")

You can pass a string that is the join-sql too. eg joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")

虽然为了清晰起见，我会使用 rails 标准的表格命名:

Though I'd use rails-standard table naming for clarity:

joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")

这篇关于Rails 4中的左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！