Implementing Search in Ruby on Rails 12
Update: While searching like this works, it’s probably not the most efficient way. In the near future I’ll be looking into setting up Ruby/Odeum, which is a ruby binding to the QDBM Odeum inverted index library. Basically, every night a new search index would be generated, and searching would be done over this index, rather than dynamically over the database. This should greately improve performance. But until then, this is what I’m using …
I found this great post on Brandon Philips blog, which showed an example of how to search your database for relevant data, given a search string from the user. He got his inspiration from Tobias’ typo source tree, and, in turn, so did I. (Actually, mine is basically the exact same search, with some of the database column names changed. Thanks Tobias.)
song.rb
def self.search(query)
   if !query.to_s.strip.empty?
      tokens = query.split.collect {|c| "%#{c.downcase}%"}
      find_by_sql(["select s.* from songs s where #{ (["(lower(s.song_title) like ? or lower(s.song_lyrics) like ?)"] * tokens.size).join(" and ") } order by s.created_on desc", *(tokens * 2).sort])
   else
      []
   end
end
Once you’ve added the search method to your model, it’s even simpler to use.
search_controller.rb
def songs
   @query = @params["query"]
   @songs = Song.search(@query)
end
You’ll notice that I’m saving the search query into an instance variable called @query. This is so that I can output the query back to the user on the search results page, phrased like, Search results for “search query here”. If you’re going to do the same, don’t forget to sanitize the search query manually, or escape the HTML like so:
<%=h @query %>
[...] So Hard?
Implementing Search in Ruby on Rails
1121160209 Implementing Search in Ruby on Rails
This entry was posted
on [...]
Does this really work? When I tried it out with queries containing more than one token it failed.
tokens * 2 will duplicate the elements ["Ruby", "Rails"] leads to ["Ruby", "Rails", "Ruby", "Rails"]. So the question marks in the SQL query will be filled out both times with the same token.
In your example song_title will queried only for “Ruby” (twice) but not for “Rails”. What worked for me is to sort the array: (tokens * 2).sort The resulting array will be ["Ruby", "Ruby", "Rails", "Rails"] and both columns will be queried with both tokens.
Hi Harold,
Yes, it seems to be an error in my code and like you said, the same WHERE condition will be applied twice per song the way it’s written.
I’ve corrected the code as per your suggestion. Thanks for spotting the error.
first i must apologize for my lack of experience.
What i need to know is how would you use pagination within this method?
thank you.
jao
Good stuff Rory. Thanks for the tip!
Great tutorial. Thanks a bunch.
Doesn’t this leave your database open to SQL injection? What’s to stop the user from entering something like “; drop table songs ;” or something like that?
The built-in Rails bind variable facility will handle that, P.
Jao, you can use this code for pagination: http://snippets.dzone.com/posts/show/389
In this example you could write:
def songs
@query = @params["query"]
@song_pages, @songs = paginate_collection(Song.search(@query), :per_page => 10)
end
undefined local variable or method `query’ for #:0xb75a1a4c>
this pops up.. how can i fix it?
That search method in your model could be quite a bit simpler than that I believe. mySQL is pretty smart so the downcase isn’t needed. You also don’t need to split up a string and check against each word.
def self.search(query)
unless query.to_s.strip.empty?
find(:all, :conditions => ['title like ? or description like ?', "%#{query}%", "%#{query}%"], :order => ‘created_at desc’, :limit=>25)
end
end
how you did implement your view?