Search multiple columns - Rails

Question

I am currently writing a search method for my rails applications, and at the moment it works fine. I have the following in my game.rb:

def self.search(search)
  if search
    find(:all, :conditions => ['game_name LIKE ? OR genre LIKE ? OR console LIKE ?', "%#{search}%", "#{search}", "#{search}"])
  else
    find(:all)
  end
end

Now that searches fine, but my problem is that if there is a record in game_name that has the word 'playstation' in it, it will finish the search there. It only returns that record, rather than all games that have 'playstation' stored in console. Now I understand this is because I have 'OR' in my conditions, but I don't know an alternative. 'AND' requires all the conditions to match or none return at all. What is an alternative I can use to AND and OR? Help would be much appreciated.

If there is a solution that has separate search boxes and entries, then that would be fine, I don't necessarily require the search to find it all based on one search form.

Answer 1

If I understand your question correctly, your SQL looks good to me for what you are trying to do. An OR clause will return all records that match in column1, column2, or column3. It doesn't stop at the first match. I do see an issue with your parameters in that the first you are using LIKE with % but in the second two you aren't, maybe that is where your issue is coming from.

Should this be your find (% around second and third search)?

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

or better use DRY version (above will not work for Rails 4.2+):

Item.where('game_name LIKE :search OR genre LIKE :search OR console LIKE :search', search: "%#{search}%") 

Answer 2

What if you have 15 columns to search then you will repeat key 15 times. Instead of repeating key 15 times in query you can write like this:

key = "%#{search}%"  @items = Item.where('game_name LIKE :search OR genre LIKE :search OR console LIKE :search', search: key).order(:name) 

It will give you same result.

Thanks

Answer 3

I think this is a little bit of a cleaner solution. This allows you to add/remove columns more easily.

key = "%#{search}%"
columns = %w{game_name genre console}
@items = Item.where(
  columns
    .map {|c| "#{c} like :search" }
    .join(' OR '),
  search: key
)