Rails
Arel
ActiveRecord

Add a simple left_join method to ActiveRecord

For some reason, an easy way to do a LEFT JOIN has yet to make into ActiveRecord’s lore. There are a lot of ways to build one from scratch. I’ve decided to let existing ActiveRecord methods do the heavy lifting (so we get handling of nested associations and various association types for free) by doing a regular joins. This of course performs an INNER JOIN by default, but with a little understanding of the underlying Arel structure that ActiveRecord uses to represent queries, we can gently pull apart this final result into the its Arel nodes that we can put back together into a left join.

module ActiveRecordExtension
  extend ActiveSupport::Concern

  module ClassMethods
    def left_joins(*args)
      inner_joins = self.joins(*args).arel.join_sources
      left_joins = inner_joins.map do |join|
        Arel::Nodes::OuterJoin.new(join.left, join.right)
      end
      self.joins(left_joins)
    end
  end
end

ActiveRecord::Base.send(:include, ActiveRecordExtension)

Once this file is required, we can simply use

Foo.left_joins(:bar)

where Foo is some ActiveRecord model that has_one :bar. Since we’ve just piggy-backed onto ActiveRecord’s existing means of constructing joins using named associations, this would work equally well with any ActiveRecord association (belongs_to, has_many, has_one :through, etc). You could even pass in neseted associations like Foo.left_joins(:bar => {:baz => :qux})) to LEFT JOIN to :qux via :baz via :bar. If it works with joins, it will work with left_joins.

My most common use case for wanting this is to do a query for a Foo that does not have an associated Bar, so I’ve added that method into ClassMethods too:

    def without(assoc_name)
      assoc = reflect_on_association(assoc_name)
      left_joins(assoc_name).where(assoc.table_name => {assoc.klass.primary_key => nil})
    end

As written, of course, this will only work with associations defined on the model itself not nested assocaitions. To do that you’d have to recurse into the argument until you found the final association.

For now,

Foo.without(:bar)

gives us a simple way to do our desired query.