ActiveRecord OR query

Question

How do you do an OR query in Rails 3 ActiveRecord. All the examples I find just have AND queries.

Edit: OR method is available since Rails 5. See ActiveRecord::QueryMethods

Solution 1

Use ARel

t = Post.arel_table

results = Post.where(
  t[:author].eq("Someone").
  or(t[:title].matches("%something%"))
)

The resulting SQL:

ree-1.8.7-2010.02 > puts Post.where(t[:author].eq("Someone").or(t[:title].matches("%something%"))).to_sql
SELECT     "posts".* FROM       "posts"  WHERE     (("posts"."author" = 'Someone' OR "posts"."title" LIKE '%something%'))

Solution 2

If you want to use an OR operator on one column's value, you can pass an array to .where and ActiveRecord will use IN(value,other_value):

Model.where(:column => ["value", "other_value"]

outputs:

SELECT `table_name`.* FROM `table_name` WHERE `table_name`.`column` IN ('value', 'other_value')

This should achieve the equivalent of an OR on a single column

Solution 3

in Rails 3, it should be

Model.where("column = ? or other_column = ?", value, other_value)

This also includes raw sql but I dont think there is a way in ActiveRecord to do OR operation. Your question is not a noob question.

Rails 5 added or, so this is easier now in an app with Rails version greater than 5:

Model.where(column: value).or(Model.where(other_column: other_value)

this handles nil values as well

Solution 4

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

Update: According to https://github.com/rails/rails/pull/16052 the or feature will be available in Rails 5

Update: Feature has been merged to Rails 5 branch

Solution 5

Rails has recently added this into ActiveRecord. It looks to be released in Rails 5. Committed to master already:

https://github.com/rails/rails/commit/9e42cf019f2417473e7dcbfcb885709fa2709f89

Post.where(column: 'something').or(Post.where(other: 'else'))

# => SELECT * FROM posts WHERE (column = 'something') OR (other = 'else)

View more solutions

Comments

[ryan0]

Learn SQL. ActiveRecord makes it easy to get things working, but you still need to know what your queries are doing, otherwise your project may not scale. I also thought I could get by without ever having to deal with SQL, and I was very wrong about that.

[rubyprince]

@ryan0, you are so right. We may use fancy gems and other things, but we should be aware of what these gems are doing inside and what the underlying technology is, and maybe use the underlying technologies without the help of the gem, if need may arise, for the sake of performance. Gems are created with a specific number of usecases in mind, but there may be situations where one of the usecase in our project might be different.

[potashin]

May be of help: ActiveRecord OR query Hash notation

[Philipp Claßen]

Since Rails 5, IMHO the accepted answer should be Greg Olsen version: Post.where(column: 'something').or(Post.where(other: 'else'))

[iNulty]

This question has a tag of ruby-on-rails-3. Why would the accepted answer relate only to Rails 5?

[pho3nixf1re]

Feels a little messy, but at least I'm not writing sql, which just feels wrong! I'm going to have to look into using Arel more.

[pho3nixf1re]

This is more the syntax for Rails 2, and it requires me to write at least portion of a sql string.

[shuitian]

The great thing is that you don't have to do it all at once, you can build up queries and it won't actually hit the database until you actually need the data. This example was just combined for brevity.

[Macario]

I can't beleave how much more elegant Sequel is

[jibiel]

Even if there's raw sql — this statement looks much clearer for me than Arel. Maybe even DRYer.

[rubyprince]

if you need to chain, other table joins which might have the columns with same column names, you should use it like this, Page.where("pages.column = ? or pages.other_column = ?", value, other_value)

[koonse]

this is the cleanest most "rails way" answer, should have been accepted

[deadkarma]

Thanks @koonse, it's not exactly an 'OR' query, but it produces the same result for this situation.

[Pratik Bothra]

Help if you can - stackoverflow.com/questions/15517286/…

[fotanus]

This solution is not a replacement for OR as you can't use two different columns this way.

[Cameron Martin]

Or this: Order.where(query.where_values.inject(:or)) to use arel all the way.

[DGM]

And that fails if the query aliases the tables. That's when arel shines.

[Zeke Fast]

> OR queries with arrays as arguments in Rails 4. Useful link! Thanks!

[rubyprince]

There is nothing wrong with SQL. The thing that can go wrong is how we build the SQL string namely SQL Injection. So, we will have to sanitize the user input before providing it to a SQL query that has to be run against the database. This will be handled by ORMs, and these have been handling the edge cases, that we tend to miss. So, it is always advisable to use ORM to create SQL queries.

[amoebe]

Another problem with SQL is that it's not database agnostic. For example matches will produce LIKE for sqlite (case insensitive by default) and ILIKE on postgres (needs explicit case insensitive like operator).

[kvorobiev]

Add some explanations to your answer

[DannyB]

I believe Matthew was referring to the activerecord_any_of gem which adds support for this syntax.

[Toni Leigh]

SQL is however demonstrably faster, if you remove all that extra fluff and go straight for the DB then you'll be more performant - OK, you'll have to handle stuff like security and platforms yourself, but this trade-off will be considerable if you're dealing with massive tables or really frequent queries

[rubyprince]

@ToniLeigh ActiveRecord is using SQL under the hood. It is just a syntax for writing SQL queries which handles SQL sanitization and make your queries DB agnostic. The only overhead is where Rails converts the syntax to SQL query. And it is just a matter of milliseconds. Ofcourse you should write the best performant SQL query and try to convert it to ActiveRecord syntax. If the SQL cannot be represented in ActiveRecord syntax, then you should go for plain SQL.

[Toni Leigh]

a millisecond or two on something your doing 100 times a second is significant - it's an option, in unusual circumstances

[Sebastialonso]

If true, thanks @DannyB. The answer must explain its context.

[El'Magnifico]

or is available now Rails 5 but not to implemented this way because it expects 1 argument to be passed. It expects an Arel object. See the accepted answer

[Jeremy List]

The or method in ActiveRecord works if you're using it directly: but can break your expectations if it's used in a scope which is then chained.

[courtsimas]

What @JeremyList said is spot on. That bit me hard.

[rubyprince]

@ToniLeigh yeah, if you dont care about the benefits of the ORM, you can go full SQL. The issue is it will be complex to maintain, you will have to escape any user inputs (sanitize) everytime etc. It has some performance hit, but in my opinion is worth it.

[Ganesh Sagare]

This solution will not work if you check columns which has nil value

[rubyprince]

@GaneshSagare yes, the sql query should be column IS NULL or other_column IS NULL in that case. I think Rails provide an intermediate method for getting sql fragments (where_clause something like that), let me dig it up. That might be useful here.