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

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 2

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 3

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 4

So the answer to the original question, can you join scopes with 'or' instead of 'and' seems to be "no you can't". But you can hand code a completely different scope or query that does the job, or use a different framework from ActiveRecord e.g. MetaWhere or Squeel. Not useful in my case

I'm 'or'ing a scope generated by pg_search, which does a bit more than select, it includes order by ASC, which makes a mess of a clean union. I want to 'or' it with a handcrafted scope that does stuff I can't do in pg_search. So I've had to do it like this.

Product.find_by_sql("(#{Product.code_starts_with('Tom').to_sql}) union (#{Product.name_starts_with('Tom').to_sql})")

I.e. turn the scopes into sql, put brackets around each one, union them together and then find_by_sql using the sql generated. It's a bit rubbish, but it does work.

No, don't tell me I can use "against: [:name,:code]" in pg_search, I'd like to do it like that, but the 'name' field is an hstore, which pg_search can't handle yet. So the scope by name has to be hand crafted and then unioned with the pg_search scope.

Answer 5

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 6

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.

Answer 7

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

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

Answer 8

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 9

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

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

Answer 10

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 11

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 12

Rails 4

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

Answer 13

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 14

the squeel gem provides an incredibly easy way to accomplish this (prior to this I used something like @coloradoblue's method):

names = ["Kroger", "Walmart", "Target", "Aldi"]
matching_stores = Grocery.where{name.like_any(names)}

Answer 15

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 16

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 17

names = ["tim", "tom", "bob", "alex"]
sql_string = names.map { |t| "name = '#{t}'" }.join(" OR ")
@people = People.where(sql_string)

Answer 18

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 19

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

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

Answer 20

Use ARel

t = Person.arel_table

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