How to chain scope queries with OR instead of AND?
Ruby on-RailsRuby on-Rails-3ActiverecordRails ActiverecordRuby on-Rails Problem Overview
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'
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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).
Solution 2 - Ruby on-Rails
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.
Solution 3 - Ruby on-Rails
Just posting the Array syntax for same column OR queries to help peeps out.
Person.where(name: ["John", "Steve"])
Solution 4 - Ruby on-Rails
Use ARel
t = Person.arel_table
results = Person.where(
t[:name].eq("John").
or(t[:lastname].eq("Smith"))
)
Solution 5 - Ruby on-Rails
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)
)
Solution 6 - Ruby on-Rails
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.
Solution 7 - Ruby on-Rails
You can also use MetaWhere gem to not mix up your code with SQL stuff:
Person.where((:name => "John") | (:lastname => "Smith"))
Solution 8 - Ruby on-Rails
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
Solution 9 - Ruby on-Rails
For me (Rails 4.2.5) it only works like this:
{ where("name = ? or name = ?", a, b) }
Solution 10 - Ruby on-Rails
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.
Solution 11 - Ruby on-Rails
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')
Solution 12 - Ruby on-Rails
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))
Solution 13 - Ruby on-Rails
Rails 4
scope :combined_scope, -> { where("name = ? or name = ?", 'a', 'b') }
Solution 14 - Ruby on-Rails
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: https://stackoverflow.com/questions/6686920/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.
Solution 15 - Ruby on-Rails
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.
Solution 16 - Ruby on-Rails
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")
)
Solution 17 - Ruby on-Rails
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) }
Solution 18 - Ruby on-Rails
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)}
Solution 19 - Ruby on-Rails
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.
Solution 20 - Ruby on-Rails
names = ["tim", "tom", "bob", "alex"]
sql_string = names.map { |t| "name = '#{t}'" }.join(" OR ")
@people = People.where(sql_string)