CODEWITHSUNDEEP
ruby-on-rails
Ryan Bigg
Ryan Bigg

Reputation: 107728

Finding records with two specific records in another table

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"

Upvotes: 6

Views: 668

Answers (3)

Jamie Stephens
Jamie Stephens

Reputation: 101

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.

Upvotes: 1

Samuel
Samuel

Reputation: 38346

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

Upvotes: 13

Michael Pearson
Michael Pearson

Reputation: 171

Assuming performance isn't a requirement:

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

Upvotes: 0

Related Questions