LEFT OUTER JOIN in Rails 4

Question

I have 3 models:

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

What I want is to query for a list of courses in the Courses table, which do not exist in the StudentEnrollments table that are related to a certain student.

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

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

Can anyone tell me how I can execute the above query in the Rails 4 way?

Any input is appreciated.

Answer 1

You can provide a string that is the join-sql. Like:

joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")

But, I will suggest you use the rails-standard table naming for better understanding:

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

For more information regarding the same, do refer to the Microsoft SQL Server certification course.