Polymorphic joins in Active Record
Tags: rails, activerecord, sql
Published: 2022-02-11 17:24:00

## The polymorphic associations

In Active Record, you can use [polymorphic associations](https://guides.rubyonrails.org/association_basics.html#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`:

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

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

```ruby
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](https://guides.rubyonrails.org/association_basics.html#polymorphic-associations).

## The issue with joining polymorphic associations

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

```ruby 
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:

```ruby 
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: 

```ruby 
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: 

```ruby 
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: 

```bash
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`:

```ruby
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:

```ruby
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](https://www.ananunesdasilva.com/posts/understanding-activerecord-inner-joins)
- [Understanding Active Record left outer joins](https://www.ananunesdasilva.com/posts/understanding-activerecord-left-outer-joins)
- [Understanding multiple joins in Active Record](https://www.ananunesdasilva.com/posts/multiple-joins-with-activerecord)
- [How to access joined data with Active Record](https://www.ananunesdasilva.com/posts/understanding-how-to-access-joins-data)