ActiveRecord Arel OR condition
How can you combine 2 different conditions using logical OR instead of AND?
NOTE: 2 conditions are generated as rails scopes and can't be easily changed into something like where("x or y") directly.
Simple example:
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
It's easy to apply AND condition (which for this particular case is meaningless):
(admins.merge authors).to_sql
#=> select ... from ... where kind = 'admin' AND kind = 'author'
But how can you produce the following query having 2 different Arel relations already available?
#=> select ... from ... where kind = 'admin' OR kind = 'author'
It seems (according to Arel readme):
The OR operator is not yet supported
But I hope it doesn't apply here and expect to write something like:
(admins.or authors).to_sql
Solution 1:
ActiveRecord queries are ActiveRecord::Relation objects (which maddeningly do not support or), not Arel objects (which do).
[ UPDATE: as of Rails 5, "or" is supported in ActiveRecord::Relation; see https://stackoverflow.com/a/33248299/190135 ]
But luckily, their where method accepts ARel query objects. So if User < ActiveRecord::Base...
users = User.arel_table
query = User.where(users[:kind].eq('admin').or(users[:kind].eq('author')))
query.to_sql now shows the reassuring:
SELECT "users".* FROM "users" WHERE (("users"."kind" = 'admin' OR "users"."kind" = 'author'))
For clarity, you could extract some temporary partial-query variables:
users = User.arel_table
admin = users[:kind].eq('admin')
author = users[:kind].eq('author')
query = User.where(admin.or(author))
And naturally, once you have the query you can use query.all to execute the actual database call.
Solution 2:
I'm a little late to the party, but here's the best suggestion I could come up with:
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
admins = admins.where_values.reduce(:and)
authors = authors.where_values.reduce(:and)
User.where(admins.or(authors)).to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE ((\"users\".\"kind\" = 'admin' OR \"users\".\"kind\" = 'author'))"
Solution 3:
As of Rails 5 we have ActiveRecord::Relation#or, allowing you to do this:
User.where(kind: :author).or(User.where(kind: :admin))
...which gets translated into the sql you'd expect:
>> puts User.where(kind: :author).or(User.where(kind: :admin)).to_sql
SELECT "users".* FROM "users" WHERE ("users"."kind" = 'author' OR "users"."kind" = 'admin')
Solution 4:
From the actual arel page:
The OR operator works like this:
users.where(users[:name].eq('bob').or(users[:age].lt(25)))
Solution 5:
I've hit the same problem looking for an activerecord alternative to mongoid's #any_of.
@jswanner answer is good, but will only work if the where parameters are a Hash :
> User.where( email: 'foo', first_name: 'bar' ).where_values.reduce( :and ).method( :or )
=> #<Method: Arel::Nodes::And(Arel::Nodes::Node)#or>
> User.where( "email = 'foo' and first_name = 'bar'" ).where_values.reduce( :and ).method( :or )
NameError: undefined method `or' for class `String'
To be able to use both strings and hashes, you can use this :
q1 = User.where( "email = 'foo'" )
q2 = User.where( email: 'bar' )
User.where( q1.arel.constraints.reduce( :and ).or( q2.arel.constraints.reduce( :and ) ) )
Indeed, that's ugly, and you don't want to use that on a daily basis. Here is some #any_of implementation I've made : https://gist.github.com/oelmekki/5396826
It let do that :
> q1 = User.where( email: 'foo1' ); true
=> true
> q2 = User.where( "email = 'bar1'" ); true
=> true
> User.any_of( q1, q2, { email: 'foo2' }, "email = 'bar2'" )
User Load (1.2ms) SELECT "users".* FROM "users" WHERE (((("users"."email" = 'foo1' OR (email = 'bar1')) OR "users"."email" = 'foo2') OR (email = 'bar2')))
Edit : since then, I've published a gem to help building OR queries.