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'

Grantham

Answer 1

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).

Stereotaxis

Answer 2

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.

Stetson

This worked perfectly for me (running Rails 6.1) – Weightless

Answer 3

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

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

Melone

Answer 4

Use ARel

t = Person.arel_table

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

Fanniefannin

It comes bundled with the Active Record gem now. – Weightless

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)
)

Maid

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.

Uracil

Answer 7

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

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

Montespan

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

Farra

Answer 9

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) }

Unprejudiced

It works in Rails 7 as well. Your example of using self is a bonus: using or(...) by itself in scope seems to give syntax error, unexpected 'or' error, but self.or(...) works perfectly. – Kendakendal

Answer 10

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

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

Nester

Answer 11

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.

Agave

Answer 12

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')

Anthropogeography

Answer 13

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))

Withoutdoors

Answer 14

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")
  )

Sitsang

Answer 15

Rails 4

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

Centeno

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.

Basal

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.

Utilitarian

Answer 18

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)}

Fertilize

Answer 19

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.

Fabulist

Answer 20

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

Foible

This allows SQL injection via the #{t} construct. – Spaak