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 

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.

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 

How do I execute this query the Rails 4 way?

Any input is appreciated.

Answer 1

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

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

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

Answer 2

If anyone came here looking for a generic way to do a left outer join in Rails 5, you can use the #left_outer_joins function.

Multi-join example:

Ruby:

Source.  select('sources.id', 'count(metrics.id)').  left_outer_joins(:metrics).  joins(:port).  where('ports.auto_delete = ?', true).  group('sources.id').  having('count(metrics.id) = 0').  all 

SQL:

SELECT sources.id, count(metrics.id)   FROM "sources"   INNER JOIN "ports" ON "ports"."id" = "sources"."port_id"   LEFT OUTER JOIN "metrics" ON "metrics"."source_id" = "sources"."id"   WHERE (ports.auto_delete = 't')   GROUP BY sources.id   HAVING (count(metrics.id) = 0)   ORDER BY "sources"."id" ASC