ActiveRecord Multiple Parameter Finder

Building ActiveRecord finder is simple.

User.first
User.last
User.all(:conditions => ["age > ?", params[:age]])

We can even combine it with name scoping

class User < ActiveRecord::Base
  named_scope :older_than, lambda {|age|{:conditions => ["age >= ?", age]}}
end

After we make named scope, we can chain it to regular finder.

User.older_than(params[:age]).last

The problem is when we need a query based on unknown number of parameters. For example we may have built a form to search certain user with several fields, but we cannot force user to fill all the fields.

Usually the query built will be like this:

User.all(:conditions => [
  "age = :age AND address LIKE :address AND username LIKE :username",
  {:age => params[:age], :address => "%#{params[:address]}%",
   :username => "%#{params[:username]}%"}]

But what will happen if one of the parameters is blank?

The solution is build dynamic condition.

conditions = ""
value = {}
unless params[:age].blank?
  conditions += "age = :age"
  value[:age] = params[:age]
end
unless params[:address].blank?
  conditions += " AND " unless conditions.blank?
  conditions += "address LIKE :address"
  value[:address] = "%#{params[:address]}%"
end
unless params[:username].blank?
  conditions += " AND " unless conditions.blank?
  conditions += "username LIKE :username"
  value[:username] = "%#{params[:username]}%"
end
User.all(:conditions => [conditions, value])

We can even refactor that method.

conditions = []
value = {}
unless params[:age].blank?
  conditions << "age = :age"
  value[:age] = params[:age]
end
unless params[:address].blank?
  conditions << "address LIKE :address"
  value[:address] = "%#{params[:address]}%"
end
unless params[:username].blank?
  conditions << "username LIKE :username"
  value[:username] = "%#{params[:username]}%"
end
conditions = conditions.join(" AND ")
User.all(:conditions => [conditions, value])