How can I build multiple attribute filtering on a table?

I have a requirement to allow an interface to narrow a products table based on a lengthy set of filtering options (price, platform, availability, etc).

开发者_开发百科The filters must compound, such that a visitor can first sort by price, then add a platform search filter to the existing price filter. Filters must also be removable.

In addition, the results will need to have several sorting options as well as pagination.

Any suggestions on the best way to implement something like this?

I am looking into has_scope and we will also be using Solr/Sunspot for searching, but am open to any suggestions.

This is one simple method, below is a much more concise version, but that second one is harder to understand without the first...

Given your filter criteria are price, platform and availability...

Name your form fields appropriately, and they will come in as part of the params hash. Then, run something like this in your controller:

@products = Product.where(true)
%w(price platform availability).each do |filter|
  unless params[filter.to_sym].nil?
    @products = @products.where(filter => params[filter.to_sym])
  end
end

This starts with a placeholder for all products, then steps through your filters and applies each one via a #where with an appropriate condition, unless the value of that filter field is nil. At the end, your products instance variable will hold the filtered collection. This works because the #where method on ActiveRecord classes is chainable, returning a Relation object each time to which you can apply another #where.

That first .where(true) piece is a little hacky, but if you don't put it in, then when all filters are nil @products will be the Product class object instead of the collection of all products. The #all method is deprecated, so I use .where(true) - not sure if there is a better or more acceptable way of doing this.

Note that in Rails 3 nothing actually gets executed against the DB until you call an enumerable method on the collection (like #each), so this should work well against the db.

A more concise version is this...

@products = Product.where(params.select {|key, value| %w(price platform availability).include?(key.to_s) && !value.nil?})

This works because you can pass in an entire hash of conditions to the #where method. The params.select portion of code inside the #where call pulls out all key-value pairs from the params hash where the key matches one of your filter criteria and the value is non-nil, and the resulting hash feeds into the #where. This also seems to not have the all-filters-nil issue mentioned above, since when all are nil the #where gets an empty hash and selects all.

Rails 3 ActiveRecord has a nice mechanism for dealing with stuff like this. You can call a method like where or order on your ActiveRecord, and what you get back will be a Relation. You can then call additional methods on that object in order to further narrow the search boundaries, or to specify joins, or so on. The database won't actually be hit until you call a method on the object that actually hands data from the database back to Ruby. More details here:

http://m.onkey.org/active-record-query-interface

http://railscasts.com/episodes/202-active-record-queries-in-rails-3

From there, all you need is a UI for adding and removing filter elements, which can be kept in session, or (I think more nicely, since it allows bookmarking and back-buttoning) as part of the URL. You can apply each filter element in order in your controller's index action.

I ended up learning about Solr/Sunspot facets and applying those to the search criteria.