---
title: "Rails Multiple Polymorphic Joins"
description: "Implement multiple polymorphic joins in rails"
canonical_url: "https://www.bigbinary.com/blog/rails-multiple-polymorphic-joins"
markdown_url: "https://www.bigbinary.com/blog/rails-multiple-polymorphic-joins.md"
---

# Rails Multiple Polymorphic Joins

Implement multiple polymorphic joins in rails

- Author: Priyank Gupta
- Published: January 7, 2020
- Categories: Rails

Having polymorphic associations in Rails can be a hard nut to crack. It enforces
restriction on `joins` association which makes it difficult to write complex
queries.

Consider following architecture where Defects can be of `InspectedTruck` or
`InspectedTrailer` associated polymorphically.

```ruby
  class InspectedTruck
    has_many :defects, as: :associated_object
  end

  class InspectedTrailer
    has_many :defects, as: :associated_object
  end

  class Defect
    belongs_to :associated_object, polymorphic: true
  end
```

Finding defects for inspected trucks using `joins` will raise error.

```ruby
  => Defect.joins(:associated_object).load
  ActiveRecord::EagerLoadPolymorphicError: Cannot eagerly load the polymorphic association :associated_object
```

We need to write a raw sql `INNER JOIN` to fetch trucks with defects. Following
query runs perfectly fine.

```ruby
  sql = "INNER JOIN inspected_trucks ON inspected_trucks.id = defects.associated_object_id"
  Defect.joins(sql).load
```

We faced a scenario in one of our applications with multiple polymorphic joins.
We needed to build a single query which lists vehicle inspection time, truck or
trailer number and defect name (if available on the inspected item).

```ruby
  class Truck
    # attributes :number
    has_many :inspected_trucks
  end

  class Trailer
    # attributes :number
    has_many :inspected_trailers
  end

  class VehicleInspectionReport
    # attributes :inspection_time
    has_one :inspected_truck, class_name: "InspectedTruck"
    has_many :inspected_trailers, class_name: "InspectedTrailer"
  end

  class InspectedTruck
    belongs_to :truck
    has_many :defects, as: :associated_object
  end

  class InspectedTrailer
    belongs_to :trailer
    has_many :defects, as: :associated_object
  end

  class Defect
    # attributes :name
    belongs_to :associated_object, polymorphic: true
  end
```

The task here was to query `VehicleInspectionReport` joining other five
different tables and select required attributes to show. But the challenge here
was posed by polymorphic association.

We had to come up with a way to query `InspectedTruck` and `InspectedTrailer` as
a single dataset. We identified the dataset a kind of Single Table Inheritance
(STI) dataset. And came up with following subquery.

```ruby
  SELECT id AS associated_object_id, 'InspectedTruck' AS associated_object_type, vehicle_inspection_report_id, truck_id, NULL trailer_id
  FROM inspected_trucks
    UNION
  SELECT id AS associated_object_id, 'InspectedTrailer' AS associated_object_type, vehicle_inspection_report_id, NULL truck_id, trailer_id
  FROM inspected_trailers
```

This subquery gave us all inspected items in a single dataset and we could refer
this dataset in a form of STI.

We were then able to build the final query using above subquery.

Add a scope in `VehicleInspectionReport` to join inspected items.

```ruby
  class VehicleInspectionReport
    # attributes :inspection_time

    INSPECTED_ITEMS_RAW_SQL = "(
                              SELECT id, 'InspectedTruck' AS object_type, vehicle_inspection_report_id, truck_id, NULL trailer_id
                                FROM inspected_trucks
                              UNION
                              SELECT id, 'InspectedTrailer' AS object_type, vehicle_inspection_report_id, NULL truck_id, trailer_id
                                FROM inspected_trailers
                            ) AS inspected_items"

    has_one :inspected_truck, class_name: "InspectedTruck"
    has_many :inspected_trailers, class_name: "InspectedTrailer"

    scope :joins_with_inspected_items, -> { joins("INNER JOIN #{INSPECTED_ITEMS_RAW_SQL} ON vehicle_inspection_reports.id = inspected_items.vehicle_inspection_report_id") }
  end
```

`joins_with_inspected_items` scope on `VehicleInspectionReport` will work in a
way of joining a STI table (`inspected_items`) on `VehicleInspectionReport`. We
can now chain any query which require inspected items. Example:

```ruby
  VehicleInspectionReport.select("defects.id AS defect_id,
                                  defects.name AS description,
                                  trucks.truck_number AS truck_number,
                                  trailers.number AS trailer_number,
                                  vehicle_inspection_reports.inspection_time AS inspection_time")
        .joins_with_inspected_items
        .joins("LEFT JOIN defects ON inspected_items.id = defects.associated_object_id
                  AND defects.associated_object_type = inspected_items.object_type")
        .joins("LEFT JOIN trucks ON inspected_items.truck_id = trucks.id")
        .joins("LEFT JOIN trailers ON inspected_items.trailer_id = trailers.id")
        .where("inspected_items.id IS NOT NULL")
        .order('truck_number, trailer_number, inspection_time DESC')
```

The underlying concept here is to structure STI dataset from polymorphic
architecture. Notice the use of `inspected_items` dataset in a form of STI using
`inspected_items.associated_object_id` AND
`inspected_items.associated_object_type`.

## Links

- [Human page](https://www.bigbinary.com/blog/rails-multiple-polymorphic-joins)