LEFT JOIN in ARel for ActiveRecord in Ruby on Rails

left_join_arel_example.rb

# Here's a contrived example of a LEFT JOIN using ARel. This is an example of
# the mechanics, not a real-world use case.

# NOTE: In the gist comments, @ozydingo linked their general-purpose ActiveRecord
# extension that works for any named association. That's what I really wanted!
# Go use that! Go: https://gist.github.com/ozydingo/70de96ad57ab69003446

# == DEFINITIONS
# - A Taxi is a car for hire. A taxi has_many :passengers.
# - A Passenger records one person riding in one taxi one time. It belongs_to :taxi.

class Taxi < ActiveRecord::Base
  # This scope LEFT JOINs the Passenger table. You might use this if you wanted
  # to select taxis by a set of taxi and passenger IDs (as with the
  # SQL "taxis.id IN () OR passengers.id IN ()").
  def self.left_join_passengers
    taxis = Taxi.arel_table
    passengers = Passenger.arel_table

    # The key here is providing the join type (Arel::Nodes::OuterJoin) and
    # grabbing the ARel join object itself from Arel::SelectManager#join_sources
    # to feed to ActiveRecord's #joins method.
    # SQL equivalent: "LEFT OUTER JOIN passengers ON taxis.id = passengers.taxi_id"
    taxi_passengers = taxis.join(passengers, Arel::Nodes::OuterJoin).
                            on(taxis[:id].eq(passengers[:taxi_id])).
                            join_sources
    joins(taxi_passengers)
  end
end

# Sources that almost documented this:
# http://blog.donwilson.net/2011/11/constructing-a-less-than-simple-query-with-rails-and-arel/

ah, I've been looking for the join_sources thing! thanks for the gist!

Why method without self. ?

Good point, @araslanov-e. My Rails is pretty rusty now, but I think you're right; the method should be def self.left_join_passengers. Fixed.

Thank you for this, join_sources is my new best friend! I've generalized this to do essentially the same method but for any named association: https://gist.github.com/ozydingo/70de96ad57ab69003446

@ozydingo That's fantastic! That's what I actually wanted in ActiveRecord. Thanks!

👍 ❤️

Thank you.

Thank you for this. I have implemented this in our code base. I found one issue however, in that that it didn't work when on Polymorphic associations because original relation's bind values were not present on the new relation. https://gist.github.com/braindeaf/bceb8244416dc9c7035ad47a76b439b9 This seems to resolve things.

Thank you.

Thanks! 👍

Thanks!

Can we further join taxi_passengers with another table ?