Feb, 2022

Polymorphic joins in Active Record

Part V of the series - Understanding Active Record joins - How can we join polymorphic associations?

The polymorphic associations

In Active Record, you can use polymorphic associations to allow a model to belong to more than one other model, on a single association.

Here's an example, where a booking can belong to an accommodation or an office:

class Booking < ApplicationRecord
  belongs_to :bookable, polymorphic: true
end

class Accommodation < ApplicationRecord
  has_many :bookings, as: :bookable
end

class Office < ApplicationRecord
  has_many :bookings, as: :bookable
end

Rember that for this bookable association to work, the bookings table will have to hold the bookable_id and bookable_type columns. The Rails official documentation explains how to implement polymorphic associations, here.

The issue with joining polymorphic associations

In polymorphic associations if you try joining bookable directly, you will get an error:

Booking.joins(:bookable)

ActiveRecord::EagerLoadPolymorphicError (Cannot eagerly load the polymorphic association :bookable)

This is because Active Record does not know what table or tables to join, since there can be many tables under the generic bookable association.

One of the ways to solve this problem is to pass an SQL string, explicitly stating which bookable table we want to join, using the foreign key and type column:

Booking.joins("INNER JOIN accommodations ON accommodations.id = bookings.bookable_id AND bookings.bookable_type = 'Accommodation'")

But note that we are excluding the offices table from the query. If we would like to join it also, we would have to add a similar join statement but with a left join:

Booking
  .joins("LEFT OUTER JOIN accommodations ON accommodations.id = bookings.bookable_id AND bookings.bookable_type = 'Accommodation'")
  .joins("LEFT OUTER JOIN offices ON offices.id = offices.bookable_id AND bookings.bookable_type = 'Office'")

It is easy to imagine the mess it can potentially become if you need to add more bookable associations and perform additional queries on top of them.

It would help if instead of passing these verbose SQL strings, we could do something like:

Booking.left_joins(:accommodation, :office)

If you wanted to join all bookables, you would still have to pass all the bookable associations, though that could be done by passing the associations as a symbol instead.

If you try running that now, you will still get an error:

ActiveRecord::ConfigurationError: Can't join 'Booking' to association named 'accommodation'; perhaps you misspelled it?

The Booking model only knows about a bookable entity, so Active Record does not recognize Accommodation and Office individually as associations.

What if we could add these associations individually to the Booking model?

Using scoped associations

We can define specific associations by scoping them by bookable_type and foreign_key:

class Booking < ApplicationRecord
  belongs_to :bookable, polymorphic: true
  belongs_to :accommodation, -> { where(bookings: { bookable_type: 'Accommodation' }) }, foreign_key: 'bookable_id'
  belongs_to :office, -> { where(bookings: { bookable_type: 'Office' }) }, foreign_key: 'bookable_id'
end

Now, if you run the previous query again, you will get all bookings of the type 'Accommodation' and 'Office'. The SQL under the hood will be exactly the same as the one we have written before in the custom joins. You can confirm that by calling the .to_sql method on the query:

Booking.left_joins(:accommodation, :office).to_sql

=> "SELECT \"bookings\".* FROM \"bookings\" 
LEFT OUTER JOIN \"accommodations\" ON \"accomodations\".\"id\" = \"bookings\".\"bookable_id\" AND \"bookings\".\"bookable_type\" = 'Accommodation' 
LEFT OUTER JOIN \"offices\" ON \"offices\".\"id\" = \"bookings\".\"bookable_id\" AND \"bookings\".\"bookable_type\" = 'Office'

Happy querying!