Skip to content

Instantly share code, notes, and snippets.

@mildmojo
Last active April 5, 2024 16:00
Show Gist options
  • Save mildmojo/3724189 to your computer and use it in GitHub Desktop.
Save mildmojo/3724189 to your computer and use it in GitHub Desktop.
LEFT JOIN in ARel for ActiveRecord in Ruby on Rails
# 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/
@chochkov
Copy link

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

@araslanov-e
Copy link

Why method without self. ?

@mildmojo
Copy link
Author

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.

@ozydingo
Copy link

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

@mildmojo
Copy link
Author

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

@timrogers
Copy link

👍 ❤️

@Chrisgo-75
Copy link

Thank you.

@braindeaf
Copy link

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.

@DenisKem
Copy link

Thank you.

@kidlab
Copy link

kidlab commented May 17, 2018

Thanks! 👍

@dcluna
Copy link

dcluna commented Jan 4, 2019

Thanks!

@wonderer007
Copy link

Can we further join taxi_passengers with another table ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment