Finding 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 taxons.

For example, if a product belongs to both the "Ruby on Rails" and "Shirts" taxon, I want that product to be returned in the dataset, but not if it only belongs to either "Ruby on Rails" or "Shirts"

Answer 1

I had this problem a while back, thankfully there is a nice 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 supply keywords to the search while filtering by Taxon1 AND Taxon2 and TaxonN. I don't ever need to do a Taxon1 OR Taxon2 search, so I made the following customizations. There might be a less hacky way to do this, but it works great for me.

I added a new product scope in /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 used the new scope by 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 helper to retrieve products (which means I can still supply keywords, etc along with the multiple taxons):

# 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 felt pretty painless. However, I'm open to better options.