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.

  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.

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

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

  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.

  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.

  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:

  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.