Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Help with rails active record querying (like clause)

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.

like image 745
nacho10f Avatar asked Oct 26 '10 19:10

nacho10f


2 Answers

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)
like image 113
njorden Avatar answered Oct 05 '22 09:10

njorden


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.

like image 21
Adam Tanner Avatar answered Oct 05 '22 09:10

Adam Tanner