Search for records with two specific records in another table

Question

I have a Product model that has_and_belongs_to_many :taxons , and I want to find all products that are in specific taxa.

For example, if a product belongs to both the "Ruby on Rails" and the "Shirts" taxon, I want this product to return in the dataset, but if it belongs only to "Ruby on Rails" or "Ruby on Rails", Shirts "

Answer 1

I had this problem a while ago, fortunately there is a good solution.

 def self.has_taxons(taxons) id = arel_table[:id] Product.joins(:taxons).where(taxons: { name: taxons }).group(id).having(id.count.eq(taxons.size)) end 

Answer 2

This answer from @samuel is exactly what I was looking for, but I wanted to be able to still provide search keywords when filtering Taxon1 AND Taxon2 and TaxonN. I never need to search for Taxon1 OR Taxon2, so I made the following settings. There may be a less hacky way to do this, but it works great for me.

I added a new product area to / app / models / spree / product _decorator.rb

 Spree::Product.class_eval do add_search_scope :in_all_taxons do |*taxons| taxons = get_taxons(taxons) id = arel_table[:id] joins(:taxons).where(spree_taxons: { id: taxons }).group(id).having(id.count.eq(taxons.size)) end end 

Then a new area was used, adding it to / app / models / spree / base _decorator.rb

 Spree::Core::Search::Base.class_eval do def get_base_scope base_scope = Spree::Product.active base_scope = base_scope.in_all_taxons(taxon) unless taxon.blank? base_scope = get_products_conditions_for(base_scope, keywords) base_scope = add_search_scopes(base_scope) base_scope end end 

Now I can use the standard search assistant to retrieve products (which means that I can still provide keywords, etc. along with several taxa):

 # taxon_ids is an array of taxon ids @searcher = build_searcher(params.merge(:taxon => taxon_ids)) @products = @searcher.retrieve_products 

This works for me and seems pretty painless. However, I am open to better options.

Answer 3

Assuming performance is optional:

 a = Taxon.find_by_name!('Ruby on Rails').products.pluck(:id) b = Taxon.find_by_name!('Shirts').products.where(:id => a)