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.

Solution 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")

Solution 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

Solution 3

There is actually a "Rails Way" to do this.

You could use Arel, which is what Rails uses to construct queries for ActiveRecrods

I would wrap it in method so that you can call it nicely and pass in whatever argument you would like, something like:

class Course < ActiveRecord::Base
  ....
  def left_join_student_enrollments(some_user)
    courses = Course.arel_table
    student_entrollments = StudentEnrollment.arel_table

    enrollments = courses.join(student_enrollments, Arel::Nodes::OuterJoin).
                  on(courses[:id].eq(student_enrollments[:course_id])).
                  join_sources

    joins(enrollments).where(
      student_enrollments: {student_id: some_user.id, id: nil},
      active: true
    )
  end
  ....
end

There is also the quick (and slightly dirty) way that many use

Course.eager_load(:students).where(
    student_enrollments: {student_id: some_user.id, id: nil}, 
    active: true
)

eager_load works great, it just has the "side effect" of loding models in memory that you might not need (like in your case)
Please see Rails ActiveRecord::QueryMethods .eager_load
It does exactly what you are asking in a neat way.

Solution 4

Combining includes and where results in ActiveRecord performing a LEFT OUTER JOIN behind the scenes (without the where this would generate the normal set of two queries).

So you could do something like:

Course.includes(:student_enrollments).where(student_enrollments: { course_id: nil })

Docs here: http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations

Solution 5

Adding to the answer above, to use includes, if you want an OUTER JOIN without referencing the table in the where (like id being nil) or the reference is in a string you can use references. That would look like this:

Course.includes(:student_enrollments).references(:student_enrollments)

or

Course.includes(:student_enrollments).references(:student_enrollments).where('student_enrollments.id = ?', nil)

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-references

View more solutions

Comments

[PJSCopeland]

If the record doesn't exist in StudentEnrollments, surely se.student_id = <SOME_STUDENT_ID_VALUE> would be impossible?

[Bhushan Kawadkar]

It is better to add some explanation to your answer posted.

[Khanetor]

My solution ended up being: query = "LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id AND" + " student_enrollments.student_id = #{self.id}" courses = Course.active.joins(query) .where(student_enrollments: {id: nil}) It's not as Rails as I want it to be, though it gets the job done. I tried using .includes(), which does the LEFT JOIN, but it does not let me specify an extra condition on joining. Thanks Taryn!

[Taryn East]

Great. Hey, sometimes we do what we do to get it working. Time for coming back to it and making it better in the future... :)

[mrbrdo]

I just have to say I can't believe ActiveRecord still has no built-in support for this after so many years. It's completely unfathomable.

[animatedgif]

Sooooo when can Sequel become the default ORM in Rails?

[Adit Saxena]

Rails shouldn't become bloated. Imo they got it right when they decided to extract gems out which were bundled by default in the first place. The philosophy is "do less but good" and "pick what you want"

[Murad Yusufov]

Rails 5 has support for LEFT OUTER JOIN: blog.bigbinary.com/2016/03/24/…

[iNulty]

Squeel is an unsupported library, not recommended

[textral]

To avoid eager_load's "side effect", see my answer

[Fangxing]

Thanks, I want to mention for cross association left outer joins, use left_outer_joins(a: [:b, :c])

[a2f0]

This is interesting.

[a2f0]

Will this work for a deeply nested relation or does the relation need to hang directly off the model being queried? I cant seem to find any examples of the former.

[alexventuraio]

Also you have available left_joins for short and behave the same way. Eg.left_joins(:order_reports)

[Andre Figueiredo]

+1 but you can improve a little more and use select(:id) instead of pluck(:id) and prevent materializing inner query, and leaving it all to database.

[Joshua Pinter]

@TarynEast "Make it work, make it fast, make it beautiful." :)

[Volte]

The question is specifically targetting Rails 4.2.

[RaphaMex]

Love it! Just had to replace joins by includes and it did the trick.

[Lorin Thwaits]

Love this recursive solution!