Understanding multiple joins in ActiveRecord

In part I and part II of this series, we used mostly single joins and left_joins to filter an ActiveRecord collection based on a direct association.

How can we filter a collection based on multiple associations?

Let's start by looking at our domain models: Host, Accommodation, Booking, Payment, Rating, and Tag.

class Host < ApplicationRecord
  has_many :accommodations
end

class Accommodation < ApplicationRecord
  belongs_to :host
  has_many :bookings
  has_many :tags
end

class Booking < ApplicationRecord
  belongs_to :accommodation
  has_many :ratings
end

class Payment < ApplicationRecord
  belongs_to :booking
end

class Rating < ApplicationRecord
  belongs_to :booking
end

class Tag < ApplicationRecord
  belongs_to :accommodation
end

Host.all

Id	Name
100	"Bennie Lubowitz"
101	"Soon Goyette"
102	"Aimee Douglas"

Accommodation.all

Id	Name	Host Id
121	"Charming House"	100
122	"Lisbon Flat"	101
123	"Le Petit Chalet"	102
124	"The Farm House"	102
125	"Beach House"	100

Booking.all

Id	Accommodation Id	Check In	Check Out
62	121	20 Jun 2020	23 Jun 2020
63	122	07 Aug 2021	08 Aug 2021
64	123	22 May 2021	28 May 2021
65	124	01 Apr 2021	02 Apr 2021

Payment.all

Id	Amount	Booking Id
50	450	62
51	100	63
52	770	64
52	150	65

Rating.all

Id	Rate	Booking Id
25	4	62
26	3	64
27	5	63

Tag.all

Id	Name	Accommodations Id
10	"Pool	121
11	"Countryside"	121
12	"Riverview"	122

Multiple associations

Get all the bookings that have at least one payment and one rating

Looking at our models, we see that both payments and ratings are directly associated with bookings. The ActiveRecord method joins accepts multiple direct associations, separated by a comma. So in our case, that will be:

Booking.joins(:payments, :ratings)

This will produce the following SQL:

SELECT "bookings".* FROM "bookings" 
INNER JOIN "payments" ON "payments"."booking_id" = "bookings"."id" 
INNER JOIN "ratings" ON "ratings"."booking_id" = "bookings"."id"

And return the following bookings:

Id	Accommodation Id	Check In	Check Out
62	121	20 Jun 2020	23 Jun 2020
63	122	07 Aug 2021	08 Aug 2021
64	123	22 May 2021	28 May 2021

Note that although there is a payment for booking id 65, there is no rating for this same booking, so it will not be included in the returned collection. If we'd have no ratings at all, no bookings would be returned. This means that combining inner joins is cumulative. Using our example, we will only return bookings that have payments AND ratings.

It could be the case though, that you need to return bookings with payments that might or might not have an associated rating. The 'might or might not have' condition calls for a left_joins:

Booking.joins(:payments).left_joins(:ratings)

This will produce the following SQL:

SELECT "bookings".* FROM "bookings" 
INNER JOIN "payments" ON "payments"."booking_id" = "bookings"."id" 
LEFT OUTER JOIN "ratings" ON "ratings"."booking_id" = "bookings"."id"

And return the following bookings:

Id	Accommodation Id	Check In	Check Out
62	121	20 Jun 2020	23 Jun 2020
63	122	07 Aug 2021	08 Aug 2021
64	123	22 May 2021	28 May 2021
65	124	01 Apr 2021	02 Apr 2021

Now, all bookings with payments are included, even the booking id 65. This will return exactly the same ActiveRecord objects that Bookings.joins(:payments) returns. The difference is that you will have ratings data available to query.

For instance, we can now do:

Booking.joins(:payments).left_joins(:ratings).where(ratings: { id: nil  })

Which is the same as saying: get all the bookings that have payments but no ratings.

Nested joins

Get all hosts that have bookings for their accommodations

The premise here has a slight, but important, nuance from the first exercise. Before, both joined tables - payments and ratings - had a direct relationship with bookings.

But now we're dealing with nested associations - accommodations are directly associated with hosts but bookings are directly associated with accommodations, not to hosts.

So instead of passing two associations separated by a comma, we can pass the nested associations as a key-value pair:

Host.joins(accommodations: :bookings) 

Notice the two INNER JOINs in the SQL below:

SELECT "hosts".* FROM "hosts" 
INNER JOIN "accommodations" ON "accommodations"."host_id" = "hosts"."id" 
INNER JOIN "bookings" ON "bookings"."accommodation_id" = "accommodations"."id"

We're first joining accommodations to hosts and then joining bookings to accommodations.

Let's have a look at the returned collection now:

Id	Name
100	"Bennie Lubowitz"
101	"Soon Goyette"
102	"Aimee Douglas"

Although not all accommodations have bookings (the "Beach House" doesn't), all hosts have at least one accommodation that has bookings.

Multi-level Nested joins

Get all hosts that have accommodations, where those accommodations have bookings with ratings, and where those accommodations also have tags

This case is also a bit different from the previously nested joins we analyzed. This time we will have two subsets of nested joins happening, both in relation to accommodations.

We will need to travel from accommodations to ratings, through bookings, and also from accommodations to tags. In ActiveRecord, that would be:

Host.joins(accommodations: [{ bookings: :ratings }], :tags) 

Let's look at the SQL behind it to understand what's going on:

SELECT "hosts".* FROM "hosts" 
INNER JOIN "accommodations" ON "accommodations"."host_id" = "hosts"."id" 
INNER JOIN "bookings" ON "bookings"."accommodation_id" = "accommodations"."id"
INNER JOIN "ratings" ON "ratings"."booking_id" = "bookings"."id"
INNER JOIN "tags" ON "tags"."accommodation_id" = "accommodations"."id"

• There's a first nesting that starts with accommodations, gets all their matching bookings, and then gets all the ratings that match the joined bookings.

• Then there's a second nesting that gets the accommodations and their matching tags.

Run these two nested joins together and we get the two hosts that meet all these requirements:

Id	Name
100	"Bennie Lubowitz"
101	"Soon Goyette"

A few personal remarks and tips

Multiple joins can get complex very quickly. There a few tips that can help you make sense of what's going on at the SQL level:

Make use of the .to_sql method

ActiveRecord can make simple queries easier to read but that's not so true for complex queries. If you're not sure what is the SQL your ActiveRecord query is building, you can call .to_sql to get the returned SQL statement, similar to the SQL snippets I've added throughout these exercises.

Remember you can pass SQL strings to ActiveRecord

To avoid being lost in the ActiveRecord -> SQL translations, I usually pass SQL strings to the ActiveRecord query methods instead. I find it more explicit and flexible in complex queries.

Multiple joins can make your queries slow

My last advice is to always be aware of any potential performance issues when you're working with multiple joins. Joining tables can be a heavy job for both your database and ActiveRecord.

__

So far we've been using joins to filter data. In Part IV, the last one of this series, we'll see how to access the joined data through the returned ActiveRecord objects.