HABTM Search by multiple associated records

Question

Following issue:
I have the ID of a Product and two IDS for Option Values. I want to look up the Variant that matches the Product ID and the two Option Value IDs. So for an Example:

I have a Product called "Tshirt" and the one I want, is Blue and Large.

The following setup I have is:

product.rb

class Product < ActiveRecord::Base
  has_many :variants
end

variant.rb

class Variant < ActiveRecord::Base
  belongs_to :product
  has_and_belongs_to_many :option_values, :join_table => :spree_option_values_variants
end

option_values.rb

class OptionValue < ActiveRecord::Base

  has_and_belongs_to_many :variants, :join_table => 'spree_option_values_variants', :class_name => "Spree::Variant"
end

Right now, I have:

Taking the example at the top:

Spree::Variant.includes(:option_values).where(:spree_option_values => {:id => [color.id, size.id]}, :product_id => product.id)

This tough gives me back ALL the Variants that are either Large OR Blue. I tough I want the Variant that is Large AND Blue.

score 0 · Answer 1 · answered Apr 12 '13 at 21:52

I think I found the Answer, which solves this Problem in 2 Queries:

(Spree::Variant.joins(:option_values).where(:spree_option_values => {:id => color.id}, :product_id => self.id) & Spree::Variant.includes(:option_values).where(:spree_option_values => {:id => size.id}, :product_id => self.id)).last

HABTM Search by multiple associated records

product.rb

variant.rb

option_values.rb

1 Answers1