Help with rails active record querying (like clause)

I want my code to do two things that is currently not 开发者_JAVA百科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.

This looks like a problem that would be better suited to using search rather than SQL. Have you considered something like thinking sphinx or act 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)

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.

I'm not sure if you want all fields to search the same term if that is the case then you can do this:

where("first_name LIKE :term OR middle_name LIKE :term OR last_name LIKE :term", { term: "%#{params[:term]}%"})

No need for any crazy split or map or anything else, this is just straight ActiveRecord