How to chain scope queries with OR instead of AND?

Question

I'm using Rails3, ActiveRecord

Just wondering how can I chain the scopes with OR statements rather than AND.

e.g.

Person.where(:name => "John").where(:lastname => "Smith")

That normally returns:

name = 'John' AND lastname = 'Smith'

but I'd like:

`name = 'John' OR lastname = 'Smith'

Answer 1

Use ARel

t = Person.arel_table

results = Person.where(
  t[:name].eq("John").
  or(t[:lastname].eq("Smith"))
)

Answer 2

You would do

Person.where('name=? OR lastname=?', 'John', 'Smith')

Right now, there isn't any other OR support by the new AR3 syntax (that is without using some 3rd party gem).

Answer 3

According to this pull request, Rails 5 now supports the following syntax for chaining queries:

Post.where(id: 1).or(Post.where(id: 2))

There's also a backport of the functionality into Rails 4.2 via this gem.

Answer 4

Just posting the Array syntax for same column OR queries to help peeps out.

Person.where(name: ["John", "Steve"])

Answer 5

Update for Rails4

requires no 3rd party gems

a = Person.where(name: "John") # or any scope 
b = Person.where(lastname: "Smith") # or any scope 
Person.where([a, b].map{|s| s.arel.constraints.reduce(:and) }.reduce(:or))\
  .tap {|sc| sc.bind_values = [a, b].map(&:bind_values) }

Old answer

requires no 3rd party gems

Person.where(
    Person.where(:name => "John").where(:lastname => "Smith")
      .where_values.reduce(:or)
)

Answer 6

In case anyone is looking for an updated answer to this one, it looks like there is an existing pull request to get this into Rails: https://github.com/rails/rails/pull/9052.

Thanks to @j-mcnally's monkey patch for ActiveRecord (https://gist.github.com/j-mcnally/250eaaceef234dd8971b) you can do the following:

Person.where(name: 'John').or.where(last_name: 'Smith').all

Even more valuable is the ability to chain scopes with OR:

scope :first_or_last_name, ->(name) { where(name: name.split(' ').first).or.where(last_name: name.split(' ').last) }
scope :parent_last_name, ->(name) { includes(:parents).where(last_name: name) }

Then you can find all Persons with first or last name or whose parent with last name

Person.first_or_last_name('John Smith').or.parent_last_name('Smith')

Not the best example for the use of this, but just trying to fit it with the question.

Answer 7

You can also use MetaWhere gem to not mix up your code with SQL stuff:

Person.where((:name => "John") | (:lastname => "Smith"))

Answer 8

If you're looking to provide a scope (instead of explicitly working on the whole dataset) here's what you should do with Rails 5:

scope :john_or_smith, -> { where(name: "John").or(where(lastname: "Smith")) }

Or:

def self.john_or_smith
  where(name: "John").or(where(lastname: "Smith"))
end

Answer 9

For me (Rails 4.2.5) it only works like this:

{ where("name = ? or name = ?", a, b) }

Answer 10

Rails 4 + Scope + Arel

class Creature < ActiveRecord::Base
    scope :is_good_pet, -> {
        where(
            arel_table[:is_cat].eq(true)
            .or(arel_table[:is_dog].eq(true))
            .or(arel_table[:eats_children].eq(false))
        )
    }
end

I tried chaining named scopes with .or and no luck, but this worked for finding anything with those booleans set. Generates SQL like

SELECT 'CREATURES'.* FROM 'CREATURES' WHERE ((('CREATURES'.'is_cat' = 1 OR 'CREATURES'.'is_dog' = 1) OR 'CREATURES'.'eats_children' = 0))

Answer 11

I'm working in Rails 6 now and it seems like this is now possible. Using the queries from the OP:

# in the Person model:
scope :john, -> { where(name: "John") }
scope :smith, -> { where(lastname: "Smith") }
scope :john_or_smith, -> { john.or(self.smith) }

Answer 12

This would be a good candidate for MetaWhere if you're using Rails 3.0+, but it doesn't work on Rails 3.1. You might want to try out squeel instead. It's made by the same author. Here's how'd you'd perform an OR based chain:

Person.where{(name == "John") | (lastname == "Smith")}

You can mix and match AND/OR, among many other awesome things.

Answer 13

An updated version of Rails/ActiveRecord may support this syntax natively. It would look similar to:

Foo.where(foo: 'bar').or.where(bar: 'bar')

As noted in this pull request https://github.com/rails/rails/pull/9052

For now, simply sticking with the following works great:

Foo.where('foo= ? OR bar= ?', 'bar', 'bar')

Answer 14

Rails 4

scope :combined_scope, -> { where("name = ? or name = ?", 'a', 'b') }

Answer 15

This is a very convenient way and it works fine in Rails 5:

Transaction
  .where(transaction_type: ["Create", "Correspond"])
  .or(
    Transaction.where(
      transaction_type: "Status",
      field: "Status",
      newvalue: ["resolved", "deleted"]
    )
  )
  .or(
    Transaction.where(transaction_type: "Set", field: "Queue")
  )

Answer 16

If you can't write out the where clause manually to include the "or" statement (ie, you want to combine two scopes), you can use union:

Model.find_by_sql("#{Model.scope1.to_sql} UNION #{Model.scope2.to_sql}")

(source: ActiveRecord Query Union)

This is will return all records matching either query. However, this returns an array, not an arel. If you really want to return an arel, you checkout this gist: https://gist.github.com/j-mcnally/250eaaceef234dd8971b.

This will do the job, as long as you don't mind monkey patching rails.

Answer 17

Also see these related questions: here, here and here

For rails 4, based on this article and this original answer

Person
  .unscoped # See the caution note below. Maybe you want default scope here, in which case just remove this line.
  .where( # Begin a where clause
    where(:name => "John").where(:lastname => "Smith")  # join the scopes to be OR'd
    .where_values  # get an array of arel where clause conditions based on the chain thus far
    .inject(:or)  # inject the OR operator into the arels 
    # ^^ Inject may not work in Rails3. But this should work instead:
    .joins(" OR ")
    # ^^ Remember to only use .inject or .joins, not both
  )  # Resurface the arels inside the overarching query

Note the article's caution at the end:

Rails 4.1+

Rails 4.1 treats default_scope just as a regular scope. The default scope (if you have any) is included in the where_values result and inject(:or) will add or statement between the default scope and your wheres. That's bad.

To solve that, you just need to unscope the query.