Help with rails active record querying (like clause)

Question

I want my code to do two things that is currently not doing

@students = Student.where(["first_name = ? OR middle_name = ? OR last_name = ?", params[:query].split])

1. Work. (it says im supposed to pass 4 parameters but I want the user to be able to type words and find by those words on each of those fields and return whatever matches)

2. Actually use Like clause instead of rigid equal clause.

Please Help.

Answer 1

This looks like a problem that would be better suited to using search rather than SQL. Have you considered something like http://rubygems.org/gems/thinking-sphinx or http://rubygems.org/gems/acts_as_ferret (solr would probably be overkill).

...if you must do this in sql, you could build a query something like this:

cols = ['first_name', 'last_name', 'middle_name']
query = 'John Smith'
sql_query = cols.map{|c| query.split.map{|q| "#{c} like '?'"}}.join(' OR ')
sql_query_array = query.split * cols.count
Student.where(sql_query, sql_query_array)

Answer 2

I agree with the previous advice that if you need to do search you should look at something like Solr or Sphinx.

Anyhow, this should help you out.

def search
   query = params[:query].split.map {|term| "%#{term}%" }
   sql = "first_name LIKE ? OR middle_name LIKE ? OR last_name LIKE ?" 

  @students = Student.where([sql, *query])
end

The answer to step 1 is using Ruby's awesome little feature called the "splat operator" which allows you to take an array and evaluate it as a list of arguments.

The answer to step 2 is to just massage the query string you get back from the params and turn it into something you can use with the LIKE operator. I basically stole this from Railscasts Simple Search Form episode.