Rails 4: how to use OR between conditions on find methods

Question

My question is similar to this one, but none of the answers there address my specific issue.

I want to find objects with something like this:

conditions = {first_name: @search OR last_name: @search}
Stuff.where( conditions )

Obviously, this syntax is invalid, but it's the easiest way I can think of to show what I want to do. I want to use the cleaner hash syntax with complex/compound conditions.

I know you can just write it out by hand with "pure string conditions" like this Stuff.where("first_name=#{@search} OR last_name=#{@search}") ...but this is NOT what I want to know.

Update It looks like you can do OR with an array like this: Stuff.where( some_column: ["option1", "option2"]). This is quite useful to know, but it doesn't solve my problem because I need the OR to apply to different key=value pairs... key=value OR key=value not key=value OR value.

Update2 The reason that I don't want to user the SQL string is because I need to build the query in several pieces and I don't know how to do that while still escaping the inserted variables. I haven't tested, but I'm assuming this won't work:

conditions = ["(project_id=? AND sent_to_api=1)", @project_id]
conditions = conditions + ["first_name=? OR last_name=?", @search, @search]
Stuff.where( conditions )

Hopefully this makes sense. Is there a way to do what I need with the SQL string syntax while still preserving Rails's built-in SQL escaping?

Answer 1

How about a reusable method that generates the dreaded SQL strings for you, and safely at that:

class ActiveRecord::Base
  def self.in_any(value, *column_names)
    raise 'At least one column must be supplied' unless column_names.present?
    sql_fragment = column_names.map{|f| "#{quote_column_name(f)} = :search"}.join(" OR ")
    where(sql_fragment, search: value)
  end
end

Stuff.in_any(@search, :first_name, :last_name)

UPDATE

If you don't want to add a method for some reason, you can do this on the fly quite safely without fear of injection. The most elegant way in this case (IMHO) would be to chain the filters together:

Stuff.where('project_id = ? AND sent_to_api = 1', @project_id).
      where('first_name = :search OR last_name = :search', search: @search)

Answer 2

Rails 5 Syntax is

Stuff.where(first_name: @search).or(Stuff.where(last_name: @search))

For older versions of Rails, you can use sql string

Stuff.where(["first_name = ? or last_name = ?", 'John', 'Smith'])

Or if both query parameters are same you can also use a Hash

Stuff.where(["first_name = :name or last_name = :name", name: @search])

Answer 3

You have search fields array like

search_fields = ['first_name', 'last_name', 'email']

params comes from controllers

params = {'first_name' => 'abc', 'email' => abc@gmail.com}

search_field_params = params.slice(*search_fields)

query = search_field_params.keys.map{|field| "#{field} = :#{field}" }.join(" OR ")

query_params = search_field_params.symbolize_keys

Stuff.where(user_id: user_id).where(query, query_params)

SELECT * FROM stuffs WHERE user_id = 537 AND (first_name = 'abc' OR email = 'abc@gmail.com')

more conditions add with where..

Answer 4

I can only think of three ways to do this:

1. Write SQL strings (.where('first_name = ? OR last_name = ?', 'foo', 'bar'). You've said you don't want to do this. Fair enough.

2. Use AREL. The question linked by fotanus covers this: ActiveRecord OR query. Your comment suggests you don't like this option either.

3. Use Squeel or some other gem that provides its own DSL for query creation. This should let you write your query as:

   Stuff.where{(first_name = search) | (last_name = search)}

Update: Off the top of my head, here's a way to build the query up in a few steps if you're on board with writing SQL strings. This feels a bit old - there's probably more elegant ways, but it should work fine:

conditions = []
values = []

conditions << 'first_name = ?'
values << 'Bob'

conditions << 'last_name = ?'
values << 'Smith'

#repeat as needed.

Stuff.where(conditions.join(' OR '), values)

Answer 5

For rails 5 it should be available to write condition with #or method:

Stuff.where( first_name: @search ).or( Stuff.where( last_name: @search ) )

But for older versions Arel technique is available:

condition = Stuff.arel_table[:first_name].eq( @search ).or( Stuff.arel_table[:last_name].eq( @search ) )
Stuff.where( condition )