Adding text search to ActiveRecord classes (See related posts)

It's fairly easy, but I have no idea how (in)efficient it is.

Here's the important stuff.

In model:

def self.search(terms)
  find_by_sql(["select t.* from table t where #{ (["(lower(t.text_field1) like ? or lower(t.text_field1) like ?)"] * tokens.size).join(" and ") } order by s.created_on desc", *(tokens * 2).sort])
end

Basically it searches any of the fields you specify. If I wanted just one search, I'd drop out the second lower(t.text_field) and change the (tokens*2) to just tokens.

Like this:

def self.search(terms)
  find_by_sql(["select t.* from table t where #{ (["(lower(t.text_field1) like ? )"] * tokens.size).join(" and ") } order by s.created_on desc", tokens])
end

The terms array is just an array of single words, lowercase. They're prepared like this:

 terms = query.split.collect {|c| "%#{c.downcase}%"}

To use this in a controller, I just do this:

 @results = Model.search( @params['query'].split.collect{ |c| "%#{c.downcase}%" } )

Just like that.

From Rory on Rails