DEV Community

Cover image for Polymorphic joins in Active Record
Ana Nunes da Silva
Ana Nunes da Silva

Posted on • Updated on • Originally published at ananunesdasilva.com

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
Enter fullscreen mode Exit fullscreen mode
class Accommodation < ApplicationRecord
  has_many :bookings, as: :bookable
end
Enter fullscreen mode Exit fullscreen mode
class Office < ApplicationRecord
  has_many :bookings, as: :bookable
end
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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'")
Enter fullscreen mode Exit fullscreen mode

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'")
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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?
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Happy querying!

More on joining with Active Record:

Top comments (4)

Collapse
 
vishaldeepak profile image
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

Collapse
 
anakbns profile image
Ana Nunes da Silva

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

Collapse
 
nrpx profile image
Arseniy Pelipas

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

Thread Thread
 
anakbns profile image
Ana Nunes da Silva • Edited

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. 👍