Rails ActiveRecord - multiple attribute search

Question

I implement a simple search function that should check the string in the username, last_name and first_name. I saw this ActiveRecord method on the old RailsCast:

http://railscasts.com/episodes/37-simple-search-form

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

But how to do this so that he searches for a keyword in the name, last_name and name and returns a record if one of the fields matches the term?

I am also wondering if RailsCast code is susceptible to SQL injection?

Thanks a lot!

Answer 1

I assumed that your model name is a model - just replace it with your real model name when you execute the actual request:

 Model.where("name LIKE ? OR last_name LIKE ? OR first_name LIKE ?", "%#{search}%","%#{search}%","%#{search}%") 

About your worries about SQL injection - both pieces of code are immune to SQL injection. As long as you don't embed strings directly in your WHERE clause, you're fine. An example for an injectable code would be:

 Model.where("name LIKE '#{params[:name]}'") 

Answer 2

Although the selected answer will work, I noticed that it breaks if you try to enter a search for “Raul Riera” because it will fail in both cases because Raul Riera is neither my name nor my last name. my name and surname ... I decided this by doing

 Model.where("lower(first_name || ' ' || last_name) LIKE ?", "%#{search.downcase}%") 

Answer 3

The best way to do this is:

 Model.where("attr_a ILIKE :query OR attr_b ILIKE :query", query: "%#{query}%") 

Answer 4

With Arel, you can avoid spelling SQL manually with the following:

 Model.where( %i(name first_name last_name) .map { |field| Model.arel_table[field].matches("%#{query}%") .inject(:or) ) 

This would be especially useful if the list of fields to map was dynamic.