ActiveRecord Arel OR condition
SqlRuby on-RailsRubyActiverecordArelSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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 - Sql
From the actual arel page:
> The OR operator works like this: > users.where(users[:name].eq('bob').or(users[:age].lt(25)))
Solution 5 - Sql
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.
Solution 6 - Sql
Just make a scope for your OR condition:
scope :author_or_admin, where(['kind = ? OR kind = ?', 'Author', 'Admin'])
Solution 7 - Sql
Using SmartTuple it's going to look something like this:
tup = SmartTuple.new(" OR ")
tup << {:kind => "admin"}
tup << {:kind => "author"}
User.where(tup.compile)
OR
User.where((SmartTuple.new(" OR ") + {:kind => "admin"} + {:kind => "author"}).compile)
You may think I'm biased, but I still consider traditional data structure operations being far more clear and convenient than method chaining in this particular case.
Solution 8 - Sql
To extend jswanner answer (which is actually awesome solution and helped me) for googling people:
you can apply scope like this
scope :with_owner_ids_or_global, lambda{ |owner_class, *ids|
with_ids = where(owner_id: ids.flatten).where_values.reduce(:and)
with_glob = where(owner_id: nil).where_values.reduce(:and)
where(owner_type: owner_class.model_name).where(with_ids.or( with_glob ))
}
User.with_owner_ids_or_global(Developer, 1, 2)
# => ...WHERE `users`.`owner_type` = 'Developer' AND ((`users`.`owner_id` IN (1, 2) OR `users`.`owner_id` IS NULL))
Solution 9 - Sql
What about this approach: http://guides.rubyonrails.org/active_record_querying.html#hash-conditions (and check 2.3.3)
admins_or_authors = User.where(:kind => [:admin, :author])
Solution 10 - Sql
Unfortunately it is not supported natively, so we need to hack here.
And the hack looks like this, which is pretty inefficient SQL (hope DBAs are not looking at it :-) ):
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
both = User.where("users.id in (#{admins.select(:id)}) OR users.id in (#{authors.select(:id)})")
both.to_sql # => where users.id in (select id from...) OR users.id in (select id from)
This generates subselets.
And a little better hack (from SQL perspective) looks like this:
admins_sql = admins.arel.where_sql.sub(/^WHERE/i,'')
authors_sql = authors.arel.where_sql.sub(/^WHERE/i,'')
both = User.where("(#{admins_sql}) OR (#{authors_sql})")
both.to_sql # => where <admins where conditions> OR <authors where conditions>
This generates proper OR condition, but obviously it only takes into account the WHERE part of the scopes.
I chose the 1st one until I'll see how it performs.
In any case, you must be pretty careful with it and watch the SQL generated.