[SOLVED] Seach two columns together in a single search column

Seach two columns together in a single search column

I am using filterrifc gem in my rails app for adding filters to the index list.I have a users table with firstname, lastname and middlename as the field. What I am trying to do is search through all these column in a single search column. The issue I am facing is: When I am search for Jimmy then I get a list of all Jimmys in firstname, lastname and middlename. But When I try search for specific Jimmy Wilson it doesnt work. It still just shows Jimmys not the specific Jimmy Wilson. Please help me find the issue.

user.rb

scope :search_name, lambda { |query|
    return nil  if query.blank?

    terms = query.downcase.split(/\s+/)

    terms = terms.map { |e|
      ('%' + e + '%').gsub(/%+/, '%')
    }

    num_or_conds = 3
    where(
      terms.map { |term|
        "(LOWER(users.lastname) LIKE ?) OR (LOWER(users.firstname) LIKE ?) OR (LOWER(users.middlename) LIKE ?)"
      }.join(' AND '),
      *terms.map { |e| [e] * num_or_conds }.flatten
    )
  }

Solution

AND has higher precedence than OR therefore you have to change where you add parentheses:

where(
  terms.map { |term|
    "(LOWER(users.lastname) LIKE ? OR LOWER(users.firstname) LIKE ? OR LOWER(users.middlename) LIKE ?)"
  }.join(' AND '),
  *terms.map { |e| [e] * num_or_conds }.flatten
)