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.

Symposium

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

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

Bohman

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! – Symposium

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... :) – Bohman

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

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

Sessions

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

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

Answer 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.

Admeasure

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. – Clactonian

Sooooo when can Sequel become the default ORM in Rails? – Geotropism

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" – Secondguess

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

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

Answer 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

Forever

This will work, but using .includes will select all fields, individually, by default (using a custom .select will not remove these other fields). – Passer

Answer 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

Tallu

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. – Chambermaid

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

Answer 6

You'd execute the query as:

Course.joins('LEFT JOIN student_enrollment on courses.id = student_enrollment.course_id')
      .where(active: true, student_enrollments: { student_id: SOME_VALUE, id: nil })

Aristocracy

Answer 7

I know that this is an old question and an old thread but in Rails 5, you could simply do

Course.left_outer_joins(:student_enrollments)

Emersion

The question is specifically targetting Rails 4.2. – Traitorous

Answer 8

You could use left_joins gem, which backports left_joins method from Rails 5 for Rails 4 and 3.

Course.left_joins(:student_enrollments)
      .where('student_enrollments.id' => nil)

Perusal

Answer 9

I've been struggling with this kind of problem for quite some while, and decided to do something to solve it once and for all. I published a Gist that addresses this issue: https://gist.github.com/nerde/b867cd87d580e97549f2

I created a little AR hack that uses Arel Table to dynamically build the left joins for you, without having to write raw SQL in your code:

class ActiveRecord::Base
  # Does a left join through an association. Usage:
  #
  #     Book.left_join(:category)
  #     # SELECT "books".* FROM "books"
  #     # LEFT OUTER JOIN "categories"
  #     # ON "books"."category_id" = "categories"."id"
  #
  # It also works through association's associations, like `joins` does:
  #
  #     Book.left_join(category: :master_category)
  def self.left_join(*columns)
    _do_left_join columns.compact.flatten
  end

  private

  def self._do_left_join(column, this = self) # :nodoc:
    collection = self
    if column.is_a? Array
      column.each do |col|
        collection = collection._do_left_join(col, this)
      end
    elsif column.is_a? Hash
      column.each do |key, value|
        assoc = this.reflect_on_association(key)
        raise "#{this} has no association: #{key}." unless assoc
        collection = collection._left_join(assoc)
        collection = collection._do_left_join value, assoc.klass
      end
    else
      assoc = this.reflect_on_association(column)
      raise "#{this} has no association: #{column}." unless assoc
      collection = collection._left_join(assoc)
    end
    collection
  end

  def self._left_join(assoc) # :nodoc:
    source = assoc.active_record.arel_table
    pk = assoc.association_primary_key.to_sym
    joins source.join(assoc.klass.arel_table,
      Arel::Nodes::OuterJoin).on(source[assoc.foreign_key].eq(
        assoc.klass.arel_table[pk])).join_sources
  end
end

Hope it helps.

Wyattwyche

Love this recursive solution! – Micrometry

Answer 10

See below my original post to this question.

Since then, I have implemented my own .left_joins() for ActiveRecord v4.0.x (sorry, my app is frozen at this version so I've had no need to port it to other versions):

In file app/models/concerns/active_record_extensions.rb, put the following:

module ActiveRecordBaseExtensions
    extend ActiveSupport::Concern

    def left_joins(*args)
        self.class.left_joins(args)
    end

    module ClassMethods
        def left_joins(*args)
            all.left_joins(args)
        end
    end
end

module ActiveRecordRelationExtensions
    extend ActiveSupport::Concern

    # a #left_joins implementation for Rails 4.0 (WARNING: this uses Rails 4.0 internals
    # and so probably only works for Rails 4.0; it'll probably need to be modified if
    # upgrading to a new Rails version, and will be obsolete in Rails 5 since it has its
    # own #left_joins implementation)
    def left_joins(*args)
        eager_load(args).construct_relation_for_association_calculations
    end
end

ActiveRecord::Base.send(:include, ActiveRecordBaseExtensions)
ActiveRecord::Relation.send(:include, ActiveRecordRelationExtensions)

Now I can use .left_joins() everywhere I'd normally use .joins().

----------------- ORIGINAL POST BELOW -----------------

If you want OUTER JOINs without all the extra eagerly loaded ActiveRecord objects, use .pluck(:id) after .eager_load() to abort the eager load while preserving the OUTER JOIN. Using .pluck(:id) thwarts eager loading because the column name aliases (items.location AS t1_r9, for example) disappear from the generated query when used (these independently named fields are used to instantiate all the eagerly loaded ActiveRecord objects).

A disadvantage of this approach is that you then need to run a second query to pull in the desired ActiveRecord objects identified in the first query:

# first query
idents = Course
    .eager_load(:students)  # eager load for OUTER JOIN
    .where(
        student_enrollments: {student_id: some_user.id, id: nil}, 
        active: true
    )
    .distinct
    .pluck(:id)  # abort eager loading but preserve OUTER JOIN

# second query
Course.where(id: idents)

Micaelamicah

This is interesting. – Chambermaid

+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. – Rogue

Answer 11

It'a join query in Active Model in Rails.

Please click here for More info about Active Model Query Format.

@course= Course.joins("LEFT OUTER JOIN StudentEnrollment 
     ON StudentEnrollment .id = Courses.user_id").
     where("StudentEnrollment .id IS NULL AND StudentEnrollment .student_id = 
    <SOME_STUDENT_ID_VALUE> and Courses.active = true").select

Barranca

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

Answer 12

Use Squeel:

Person.joins{articles.inner}
Person.joins{articles.outer}

Hanley

Squeel is an unsupported library, not recommended – There

Answer 13

If anyone out there still needs true left_outer_joins support in Rails 4.2 then if you install the gem "brick" on Rails 4.2.0 or later it automatically adds the Rails 5.0 implementation of left_outer_joins. You would probably want to turn off the rest of its functionality, that is unless you want an automatic "admin panel" kind of thing available in your app!

Micrometry