ActiveRecord Arel OR condition

Question

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

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 2

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

View more solutions

Comments

[Dmytrii Nagirniak]

I saw that. It is NOT yet supported. How does it answer the question?

[Dave Newton]

Couldn't format it in a comment. There's tests for the OR operators, but the page you linked to is from 2009 and isn't the AREL actually being used. It may not answer the question, but at least it's the correct reference, and doesn't say it isn't supported.

[Dmytrii Nagirniak]

Ok. But you can't do it with Rails ActiveRecord scopes. Have you tried the example with admins & authors or similar? There's no or method on ActiveRecord::Relation. Converting it to Arel gives another set of problems (the query is SelectManager, not Where). Or I have missed something?

[Dave Newton]

Oh, thought you were referring to arel because you linked to it--sorry.

[tokland]

AFAIK, you can pass arel conditions to AR methods, so this should work: User.where(users[:name].eq('bob').or(users[:age].lt(25)))

[Dmytrii Nagirniak]

@tokland, but how do you combine two existing realtions with OR?

[tokland]

@Dmytrii: AFAIK this is not possible, ORs must be in the same expression. For me it makes sense, scopes are accumulative, so AND is ok while OR is not.

[Dmytrii Nagirniak]

Well, your query isn't correct SQL :) But what you suggested is exactly what I cannot do. Please DO read the question. Now, do you see the note in bold?

[Unixmonkey]

@DmytriiNagirniak Fixed the sql error, but I still don't see why this wouldn't work for you. Maybe its an Arel thing (I still use 2.3 mostly), or maybe the question needs more clarification.

[Dmytrii Nagirniak]

This would work. But as I said in my question, I have 2 scopes. I need to combine those using OR statement. I can't rewrite those 2 scopes into a single where statement.

[Dmytrii Nagirniak]

Can you convert the scopes into SmartTuple easily? I am asking because the app is already using heavily the Arel.

[Alex Fortuna]

No, but you may return SmartTuple objects from your code instead of returning scopes, and then quickly convert SmartTuples into scopes when needed.

[Dmytrii Nagirniak]

That's pretty nice actually. Thanks.

[Sjors Branderhorst]

Honestly, it works, but composing sql with regexes like that, I just had to down vote your answer. Then you might as well just write sql and use find_by_sql and skip the whole Arel layer.

[Dmytrii Nagirniak]

Mind to show better solutions then. I provided 2, one of which I don't like and don't use.

[Dmytrii Nagirniak]

Please read the question. There are 2 rails relations generated that can't be controlled.

[Sjors Branderhorst]

True, I should have read the question better. You specifically state: "But how can you produce the following query having 2 different Arel relations already available?", and I do not respond to that. And indeed, when "OR"-ing condition scopes isn't supported you need to resort to hacks. So an apology is in order. I just wanted to point out there might be an easier solution that would help you skip having the problem in the first place.

[bhaibel]

This was giving me an "undefined method .or for [string]" error, but "#{admins} or #{authors}" worked great.

[tomaszbak]

bhaibel: I had the same problem - it was caused by join. I use the joined model instead and no more string error.

[AlexChaffee]

you can skip the "where_values.reduce" step if you start with a real ARel query... see my answer

[ches]

Three people have given valid answers using valid ARel and yet you've accepted your own ugly answer where, as Sjors said, you may as well have just manipulated strings to construct SQL in the first place. Downvote.

[BM5k]

This doesn't work as expected (rails 3.2.12) if your scopes have more than one condition. The problem is that parens are not placed around the OR conditions, causing ANDs to affect the entire query instead of part

[Sathish]

This worked for me too. Now that Rails 4 is released, is this still the best way to get the OR condition?

[Blue Smith]

Great tip! Thank you.

[Gary]

This works well to get multiple ids baased on different searches on a complex table including Postgesql where 'LIKE' does not work on integers.

[Gabor Garami]

To be really honest, Arel refuses to compile queries if you have joins on any side of the OR, referring to the "OR" operands should be structurally same. So at some cases, hacking SQL is an only way to make this stuff working as Arel still not provides a good dynamical way to compile complex SQLs.