Polymorphic joins in Active Record

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!

More on joining with Active Record:

• Understanding Active Record inner joins
• Understanding Active Record left outer joins
• Understanding multiple joins in Active Record
• How to access joined data with Active Record

Comments

[VISHAL DEEPAK]

I think there's a mistake here ,

belongs_to :accommodation, -> { where(bookings: { bookable_type: 'Accommodation' }) }, foreign_key: 'bookable_id'

accomdation_id probably does not exist

[Ana Nunes da Silva]

You're right Vishal, same for the office relation. Fixed both. Thanks for the heads-up!

[Arseniy Pelipas]

@anakbns sorry, but you'll end up with an empty result subset. These JOINs are mutually exclusive. Possible use is left_joins.

[Ana Nunes da Silva]

Thanks @nrpx! Yes, in this case inner joining both associations does not make sense since it will try to fetch bookings that belong both to an accommodation and an office, which is an impossibility here. Updated to use a left join instead. 👍