Unixmonkey.net

For some complex searches, I find myself needing to build a query with some optional conditions that may or may not exist based on search terms.

I could concatenate an SQL string, but when we are dealing with user-supplied input, we need to parameterize the sql query with question marks (?) for binding parameters to to avoid SQL injection attacks.

But I may not know how many parameters I’m actually going to use in a query.

With a form like this:

Show me my friends: 
  living in: [   ]
  whose hobby is: () skating, () fishing, () basketball
  over: [  ] years old

  *(no required fields)

At first stab, you could try something like this:

User.friends.find(:all, :conditions => [
   'town LIKE ? AND hobby = ? AND age >= ?', 
   "%#{params[:town]}%", params[:hobby], params[:age] 
])

This would work, but only if all fields are filled out; otherwise the SQL generated would break looking like this if someone only filled in the name part of the form:

"SELECT * FROM users WHERE town LIKE "%indianapolis%" AND hobby = '' AND age >= ''"

There are some plugins like Ezra’s Ez-Where to handle query building; but how about we try using the built-in symbol key interpolation as described here in ActiveRecord to get the job done.

This lets you use named :symbols in place of question marks for binding.

Find lets you supply your conditions as a 2-element array with a string, and a corresponding hash like so:

User.friends.find(:all, :conditions => [ 
  'town LIKE :town AND hobby = :hobby AND age >= :age', 
  { :town => "%#{params[:town]}%" , :hobby => params[:hobby],  :age = params[:age] }
])

Again, this will work just fine if all fields are filled out; but how do we omit conditions and hash keys?

Our final SQL string can be built by joining conditions with AND (or OR if your app suggests it), and stuffing new key/value pairs into our arguments hash, or with Hash.merge!

First we’ll set up our search method like so:

def results
  conditions  = []
  arguments = {}
 
  unless params[:town].blank?
    conditions << 'town LIKE :town'
    arguments[:town] = "%#{params[:town]}%"
  end
 
  unless params[:hobby].blank?
    conditions << 'hobby = :hobby'
    arguments[:hobby] = params[:hobby]
  end
 
  unless params[:age].blank?
    conditions << 'age >= :age'
    arguments[:age] = params[:age]
  end
 
  all_conditions = conditions.join(' AND ')
 
  @user_friends = User.friends.find(:all, :conditions => [all_conditions, arguments])
end

An alternative is to use Hash.merge! like so:
(this will let you assign more than one key/value pair at a time or to combine hashes)

instead of:

arguments[:town]  = "%#{params[:town]}%"
arguments[:hobby] = params[:hobby]

  arguments.merge!({ 
    :town  => "%#{params[:town]}%",
    :hobby => params[:hobby]
  })

Maybe not as elegant as using a plugin, but certainly clear and flexible.