Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@chochkov chochkov commented Aug 14, 2014

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

@araslanov-e

This comment has been minimized.

Copy link

@araslanov-e araslanov-e commented Dec 4, 2015

Why method without self. ?

@mildmojo

This comment has been minimized.

Copy link
Owner Author

@mildmojo mildmojo commented Jan 18, 2016

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

This comment has been minimized.

Copy link

@ozydingo ozydingo commented Feb 23, 2016

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

This comment has been minimized.

Copy link
Owner Author

@mildmojo mildmojo commented Mar 25, 2016

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

@timrogers

This comment has been minimized.

Copy link

@timrogers timrogers commented Sep 17, 2016

👍 ❤️

@Chrisgo-75

This comment has been minimized.

Copy link

@Chrisgo-75 Chrisgo-75 commented Dec 9, 2016

Thank you.

@braindeaf

This comment has been minimized.

Copy link

@braindeaf braindeaf commented Aug 8, 2017

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

This comment has been minimized.

Copy link

@DenisKem DenisKem commented Mar 30, 2018

Thank you.

@kidlab

This comment has been minimized.

Copy link

@kidlab kidlab commented May 17, 2018

Thanks! 👍

@dcluna

This comment has been minimized.

Copy link

@dcluna dcluna commented Jan 4, 2019

Thanks!

@wonderer007

This comment has been minimized.

Copy link

@wonderer007 wonderer007 commented Feb 13, 2019

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
You can’t perform that action at this time.