CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecordruby-on-rails-4
barnett
barnett

Reputation: 1602

ActiveRecord query based on multiple objects via has_many relationship

I have a Product class that has_many Gender through Connection class instances. I want to query to find products that have both end_a and end_b present. The current class method works with 2 caveats:

The class method .query is below, any feedback or ideas are appreciated.

class Product < ActiveRecord::Base

  has_many   :connections, dependent: :destroy, as: :connectionable
  has_many   :genders,     through:   :connections

  def self.query(end_a, end_b)
    search_base = active.joins(:connections)
    end_a_search = search_base.where(connections: { gender_id: end_a  } )

    end_a_search & search_base.where(connections: { gender_id: end_b  } )
  end
end

ps: Once this is figured out will likely move this to a scope for Product

Upvotes: 0

Views: 100

Answers (1)

trushkevich
trushkevich

Reputation: 2677

class Product < ActiveRecord::Base
  has_many :connections, dependent: :destroy, as: :connectionable
  has_many :genders, through: :connections

  scope :with_genders, -> (end_a, end_b) {
    relation = joins('INNER JOIN connections c1 ON c1.connectionable_id = products.id AND c1.connectionable_type = \'Product\'')
      .joins('INNER JOIN connections c2 ON c1.connectionable_id = c2.connectionable_id AND c2.connectionable_type = \'Product\'')
      .where(c1: {gender_id: end_a}, c2: {gender_id: end_b})
      .group('products.id')
    end_a == end_b ? relation.having('COUNT(products.id) > 1') : relation
  }
end

Upvotes: 1

Related Questions