Rails ActiveRecord - Search on Multiple Attributes

Question

I'm implementing a simple search function that should check for a string in either the username, last_name and first_name. I've seen this ActiveRecord method on an old RailsCast:

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

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

But how do I make it so that it searches for the keyword in name, last_name and first name and returns the record if the one of the fields matched the term?

I'm also wondering if the code on the RailsCast is prone to SQL injections?

Thanks a lot!

Erez Rabih · Accepted Answer · 2012-08-05T13:53:33.533

24

I assumed your model name is Model - just replace it with your real model name when you do the actual query:

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

About your worries about SQL injections - both of code snippets are immune to SQL injections. As long as you do not directly embed strings into your WHERE clause you are fine. An example for injection-prone code would be:

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

edited Aug 05 '12 at 13:53

answered Aug 05 '12 at 13:48

Erez Rabih

15,562
3
47
64

What if the controller is changed to pass the whole `params` rather than just `params[:search]` and in our query we use `"%#{params[:search]}%"` instead of `"%#{search}%"`. Is that still immune to SQL injections? – Dennis Feb 04 '14 at 19:24
@Dennis yes it is. As long as you are using ? and passing extra argument to replace them in the query you are fine. – Erez Rabih Feb 06 '14 at 05:40
Thanks @ErezRabih. What about using hashes? E.g. `where(name: params[:name])` – Dennis Feb 06 '14 at 23:18
This query works like a charm! (using active record 5) – Stef Hej Jan 04 '17 at 18:11
1

Having those three params is really ugly. Better to name it like this `Model.where("x ILIKE :query OR y ILIKE :query", query: "%#{params[:query]}%")` – Peter R Jun 17 '17 at 13:46

score 13 · Answer 2 · answered Apr 10 '14 at 00:10

13

Although the selected answer will work, I noticed that it breaks if you try to type a search "Raul Riera" because it will fail on both cases, because Raul Riera is not either my first name or my last name.. is my first and last name... I solved it by doing

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

answered Apr 10 '14 at 00:10

raulriera

714
10
28

I found this answer more useful. Thanks! – Leonard Teo Sep 09 '15 at 05:32
1

Wouldn't it be better to use `ILIKE` rather than downcase? Let the DB handle it. – Peter R Jun 17 '17 at 13:45
It doesn't work when last_name or first_name equal to nil, if so, you need set default empty string value to first and last name – Vlad Hilko Oct 06 '17 at 13:18

score 2 · Answer 3 · edited Nov 09 '18 at 11:59

2

With Arel, you can avoid writing the SQL manually with something like this:

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

This would be particularly useful if the list of fields to match against was dynamic.

edited Nov 09 '18 at 11:59

peter

41,770
5
64
108

answered Sep 14 '17 at 17:32

Sean

333
4
9

you forgot the closing } on the .map line, adapted it – peter Nov 09 '18 at 12:00

score 1 · Answer 4 · answered Nov 09 '18 at 12:53

A more generic solution for searching in all fields of the model would be like this

def search_in_all_fields model, text
  model.where(
    model.column_names
      .map {|field| "#{field} like '%#{text}%'" }
      .join(" or ")
  )
end

Or better as a scope in the model itself

class Model < ActiveRecord::Base
  scope :search_in_all_fields, ->(text){
    where(
      column_names
        .map {|field| "#{field} like '%#{text}%'" }
        .join(" or ")
    )
  }
end

You would just need to call it like this

Model.search_in_all_fields "test"

Before you start.., no, sql injection would probably not work here but still better and shorter

class Model < ActiveRecord::Base
  scope :search_all_fields, ->(text){
    where("#{column_names.join(' || ')} like ?", "%#{text}%")
  }
end

score 0 · Answer 5 · answered Jun 17 '17 at 13:47

0

The best way to do this is:

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

answered Jun 17 '17 at 13:47

Peter R

3,185
23
43

Rails ActiveRecord - Search on Multiple Attributes

5 Answers5

Linked