CODEWITHSUNDEEP
ruby-on-railsrubysearch
James
James

Reputation: 1887

Rails combine multiple columns in search

In my application I have a customers model with three columns, first_name, middle_name and last_name. I have a method in the model that performs the search:

class Customer < ActiveRecord::Base
  belongs_to :user

  def self.search(search, user)
    if search
      .where('first_name LIKE ? OR middle_name LIKE ? OR last_name LIKE ?', "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%")
      .where(user: user)
    else
      where(user: user)
    end
  end

end

The problem with this search function is that it only allows searching by one of the three columns at a time.

For example, a customer has a first_name of "foo", a middle_name of "bar" and a last_name of "baz". Searching for "foo", "bar", or "baz" individually returns results, but "foo bar" or "bar baz" does not. What is the best way I can allow searching across all three columns?

Upvotes: 2

Views: 2722

Answers (3)

Тарас Білоус
Тарас Білоус

Reputation: 1

If you use pg, you can try like this

def self.search(query)
    where(['phone ilike :query',
       'LOWER(name) ilike :query',
       'LOWER(email) ilike :query',
       'LOWER(address) ilike :query'].join(' OR '), {query: "%#{query}%" })
end

Upvotes: 0

kaushal sharma
kaushal sharma

Reputation: 231

.where("first_name LIKE ? OR middle_name LIKE ? OR last_name LIKE ? or CONCAT(first_name, middle_name, last_name) LIKE ?", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%")

Upvotes: 1

Shiva
Shiva

Reputation: 12592

You can concat your fields in the database query like

Updated:

.where("concat_ws(' ' , first_name, middle_name, last_name) LIKE ?", "%#{search}%")

This should work for foo, foo bar, or foo bar baz but not foo baz

If you want to support foo baz as well then

.where("first_name LIKE ? OR middle_name LIKE ?"\
       " OR last_name LIKE ? OR concat_ws(' ' , first_name, middle_name, last_name) LIKE ?"\
       " OR concat_ws(' ' , first_name, last_name) LIKE ?", 
       "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%")

Upvotes: 5

Related Questions