Rails Multiple Polymorphic Joins

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.

1  class InspectedTruck
2    has_many :defects, as: :associated_object
3  end
4
5  class InspectedTrailer
6    has_many :defects, as: :associated_object
7  end
8
9  class Defect
10    belongs_to :associated_object, polymorphic: true
11  end
Copy

Finding defects for inspected trucks using joins will raise error.

1  => Defect.joins(:associated_object).load
2  ActiveRecord::EagerLoadPolymorphicError: Cannot eagerly load the polymorphic association :associated_object
Copy

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

1  sql = "INNER JOIN inspected_trucks ON inspected_trucks.id = defects.associated_object_id"
2  Defect.joins(sql).load
Copy

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

1  class Truck
2    # attributes :number
3    has_many :inspected_trucks
4  end
5
6  class Trailer
7    # attributes :number
8    has_many :inspected_trailers
9  end
10
11  class VehicleInspectionReport
12    # attributes :inspection_time
13    has_one :inspected_truck, class_name: "InspectedTruck"
14    has_many :inspected_trailers, class_name: "InspectedTrailer"
15  end
16
17  class InspectedTruck
18    belongs_to :truck
19    has_many :defects, as: :associated_object
20  end
21
22  class InspectedTrailer
23    belongs_to :trailer
24    has_many :defects, as: :associated_object
25  end
26
27  class Defect
28    # attributes :name
29    belongs_to :associated_object, polymorphic: true
30  end
Copy

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.

1  SELECT id AS associated_object_id, 'InspectedTruck' AS associated_object_type, vehicle_inspection_report_id, truck_id, NULL trailer_id
2  FROM inspected_trucks
3    UNION
4  SELECT id AS associated_object_id, 'InspectedTrailer' AS associated_object_type, vehicle_inspection_report_id, NULL truck_id, trailer_id
5  FROM inspected_trailers
Copy

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.

1  class VehicleInspectionReport
2    # attributes :inspection_time
3
4    INSPECTED_ITEMS_RAW_SQL = "(
5                              SELECT id, 'InspectedTruck' AS object_type, vehicle_inspection_report_id, truck_id, NULL trailer_id
6                                FROM inspected_trucks
7                              UNION
8                              SELECT id, 'InspectedTrailer' AS object_type, vehicle_inspection_report_id, NULL truck_id, trailer_id
9                                FROM inspected_trailers
10                            ) AS inspected_items"
11
12    has_one :inspected_truck, class_name: "InspectedTruck"
13    has_many :inspected_trailers, class_name: "InspectedTrailer"
14
15    scope :joins_with_inspected_items, -> { joins("INNER JOIN #{INSPECTED_ITEMS_RAW_SQL} ON vehicle_inspection_reports.id = inspected_items.vehicle_inspection_report_id") }
16  end
Copy

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:

1  VehicleInspectionReport.select("defects.id AS defect_id,
2                                  defects.name AS description,
3                                  trucks.truck_number AS truck_number,
4                                  trailers.number AS trailer_number,
5                                  vehicle_inspection_reports.inspection_time AS inspection_time")
6        .joins_with_inspected_items
7        .joins("LEFT JOIN defects ON inspected_items.id = defects.associated_object_id
8                  AND defects.associated_object_type = inspected_items.object_type")
9        .joins("LEFT JOIN trucks ON inspected_items.truck_id = trucks.id")
10        .joins("LEFT JOIN trailers ON inspected_items.trailer_id = trailers.id")
11        .where("inspected_items.id IS NOT NULL")
12        .order('truck_number, trailer_number, inspection_time DESC')
Copy

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.