Constructing a has-and-belongs-to-many query

Question

Hi, I have a rails app (running on version 2.2.2) that has a model called Product. Product is in a has-and-belongs-to-many relationship with Feature. The problem is that I need have search functionality for the products. So I need to be able to search for products that have a similar name, and some other attributes. The tricky part is that the search must also return products that have the exact set of features indicated in the search form (this is represented by a bunch of checkboxes). The following code works, but it strikes me as rather inefficient:

@products = Product.find(:all, :conditions=>["home=? AND name LIKE ? AND made_by LIKE ? AND supplier LIKE ? AND ins LIKE ?",hme,'%'+opts[0]+'%','%'+opts[1]+'%','%'+opts[3]+'%','%'+opts[4]+'%'])


#see if any of these products have the correct features
if !params[:feature_ids].nil?
  f = params[:feature_ids].collect{|i| i.to_i}
  @products.delete_if {|x| x.feature_ids!=f}
end

I'm sorry that my grasp of rails/sql is so weak, but does anyone have any suggestions about how to improve the above code? Thanks so much!

Answer 1

Hello,

First, i would recommend you to manually write a FeatureProduct model (and not use the default 'has_and_belongs_to_many') EG

class FeatureProduct
  belongs_to :feature
  belongs_to :product
end

class Product
  has_many :feature_products
  has_many :features, :through => :feature_products
end

class Feature
  has_many :feature_products
  has_many :products, :through => :feature_products
end

For the search: You may find the gem SearchLogic to be exactly what you need. It has support for 'LIKE' conditions (it means that you can write in a more 'Rails way' your query). It also has support for performing a search with conditions on a related model (on your Feature model, to be more precise).

The solution would be something like:

search = Product.search
search.name_like = opt[0]
search.made_by_like = opt[1]
...
search.feature_products_id_equals = your_feature_ids
..
@product_list = search.all

There is also an excellent screencast explaining the use of this gem.

Good luck :)