CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
WozPoz
WozPoz

Reputation: 992

Rails 3 - Using LIKE to search a combined 2 columns

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!

Upvotes: 11

Views: 6768

Answers (2)

WozPoz
WozPoz

Reputation: 992

This ended up working extremely well... Not sure about performance though. Can Indexes Help This?

:conditions => ['fname || lname LIKE ?', "%#{search}%"]

Upvotes: 0

DGM
DGM

Reputation: 26979

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.

Upvotes: 14

Related Questions