ruby-on-railspolymorphismrails-activerecord

How to get to polymorphic grandchildren in Rails ActiveRecord


I have an entity that has invoices and offers.

class Entity < ApplicationRecord
  has_many :invoices, dependent: :restrict_with_error
  has_many :offers, dependent: :restrict_with_error

Invoices and offers have ploymorphic positions as positionable.

class Invoice < ApplicationRecord
  belongs_to :entity
  has_many :positions, as: :positionable, dependent: :destroy
class Offer < ApplicationRecord
  belongs_to :entity
  has_many :positions, as: :positionable, dependent: :destroy
class Position < ApplicationRecord
  belongs_to :positionable, polymorphic: true

How can I get all positions that are under an entity?

@entity = Entity.first
@entity.positionables.positions # doesn't work: undefined method `positionables' for an instance of Entity
Position.preload(:positionable).positions # doesn't work: undefined method `positions' for an instance of ActiveRecord::Relation

I tried various other things but got nowhere.


Solution

  • To obtain all the Position objects for the Offer and Invoice objects associated with a given entity the following should work:

    Position.where(
      positionable_type: 'Offer', 
      positionable_id: Offer.where(entity_id: @entity.id)
    ).or(
      Position.where(
        positionable_type: 'Invoice', 
        positionable_id: Invoice.where(entity_id: @entity.id)
    ))
    

    This will result in SQL akin to

    SELECT 
      positions.*
    FROM 
      positions 
    WHERE 
      (
       positions.positionable_type = 'Offer' 
       AND positions.positionable_id IN ( SELECT id FROM offers where offers.entity_id = 1) 
      ) OR ( 
       positions.positionable_type = 'Invoice' 
       AND positions.positionable_id IN ( SELECT id FROM invoices where invoices.entity_id = 1) 
      )
    

    UPDATE based on the comment

    The following should be more dynamic:

    class Position < ApplicationRecord
      scope :by_entity, ->(entity_id) {
        # collect all the distinct positionable_types 
        distinct_positionables = Position.distinct.pluck(:positionable_type)
        # loop through the above to construct the same query dynamically
        distinct_positionables.filter_map do |obj|
          # skip any positionable_types that don't belong_to :entity
          next unless klass = obj.safe_constantize and klass.column_names.include?('entity_id')
          #construct the individual query 
          where( 
            positionable_type: obj, 
            positionable_id: klass.where(entity_id: entity_id)
          )
        end.reduce(:or) # join them all together with `or`
      }
      # alternative using Arel
      # scope :by_entity, ->(entity_id) {
      #  distinct_positionables = Position.distinct.pluck(:positionable_type)
      #  position_table = self.arel_table 
      #  condition = distinct_positionables.filter_map do |obj|
      #    next unless klass = obj.safe_constantize and klass.column_names.include?('entity_id')
      #    position_table[:positionable_type].eq(obj).and(
      #      position_table[:positionable_id].in(
      #         klass.select(:id).where(entity_id: entity_id).arel
      #       )
      #    )
      #  end.reduce(:or)
      #  where(condition)
      # }
    end
    

    Called as

    @entity = Entity.first
    Position.by_entity(@entity.id)
    

    Another alternative is that we could construct OUTER JOINS for all of the other tables using both or even all 3 conditions for the join.

    If someone that knows more about query planning than I, can comment on the efficiency of the subqueries vs the joins I will gladly post a JOIN based version as well.