Rails 3 - Using LIKE to search a combined 2 columns

Question

I'm following ryan's Simple Search Form tutorial here: http://railscasts.com/episodes/37-simple-search-form

I have the following line in my Users Model:

find(:all, :conditions => ['fname LIKE ?', "%#{search}%"])

But what I'd like to do is search across a combine 2 columns,: fname & lname

As users are searching my full names:

Example, James Brown fname = James lname = Brown

Is there a way to do this in Rails safely that will work across DBs like SQLite, MySQL or Postgres (heroku uses)?

Thanks!

Answer 1

It may not be pretty, but I use this in my Person model:

scope :by_full_name lambda {|q| 
    where("first_name LIKE ? or last_name LIKE ? or concat(last_name, ', ', first_name) LIKE ?", "%#{q}%", "%#{q}%" , "%#{q}%")
}

See one of my other posts for an bit extra that will let the search query be optional.