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' => [email protected]}

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 = '[email protected]')

more conditions add with where..