Rails Named Bind Variables

Let's say you have a form that searches various colums in a database table for a particular term. Your query might look something like this:

User.paginate(:page => params[:page],
:conditions => ["field1 LIKE ? OR field_2 LIKE ? OR field_3 LIKE ?",
params[:search], params[:search], params[:search]])

Not very DRY, is it? Turns out there is a much more elegant way to do this in Rails. You can use named bind variables instead. Named bind variables allow you to replace the question mark placeholders in the conditions array with a symbol. The values corresponding to these symbol placeholders can be then accessed via a hash.

To refactor the simple User search example cited above, first wire up the controller like so:

User.search(params[:search], params[:page])

Then in the User model, create a class method called search using a named bind variable:

def self.search(search, page)
  sql_condition = %w(email real_name nickname
                     twitter_account_name
                     github_account_name).
                  map {|field| "#{field} LIKE :search"}.join(" OR ")

  paginate :per_page => 20, :page => page,
           :conditions => [sql_condition, {:search => "%#{search}%"}],
           :order => 'email'
end

The value of the named bind variable (:search) is accessed through the hash using the matching symbol key.

Another possible use case for named bind variables is in those cases where you have multiple parameters in your conditions array. If we are dealing with four or five parameters, it takes some looking to figure out which question mark matches which parameter. With named bind variables you can do something like the following:

Company.where(
  "id = :id AND name = :name AND division = :division AND
  created_at > :accounting_date",
  { :id => 3, :name => "37signals", :division => "First",
    :accounting_date => '2005-01-01' }
).first