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

Eos

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

@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. – Inbred

May be of help: ActiveRecord OR query Hash notation – Dubitable

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

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

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

Simpkins

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

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

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

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). – Ecumenical

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 – Outgoings

@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. – Inbred

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

@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. – Inbred

Answer 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

Unify

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

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

Help if you can - #15517786 – Beam

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

Answer 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

Inbred

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

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) – Inbred

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

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

@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. – Inbred

This would have worked before: Model.where({column: value, other_column: other_value}.map { |k, v| Model.sanitize_sql_hash_for_conditions(k => v) }.join(" OR ")) but sanitize_sql_hash_for_conditions is deprecated from Rails 4.9 – Inbred

Answer 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

Mundy

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 – Bearing

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

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

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

Corregidor

Answer 6

Rails 5 comes with an or method. (link to documentation)

This method accepts an ActiveRecord::Relation object. eg:

User.where(first_name: 'James').or(User.where(last_name: 'Scott'))

Shaggy

Answer 7

If you want to use arrays as arguments, the following code works in Rails 4:

query = Order.where(uuid: uuids, id: ids)
Order.where(query.where_values.map(&:to_sql).join(" OR "))
#=> Order Load (0.7ms)  SELECT "orders".* FROM "orders" WHERE ("orders"."uuid" IN ('5459eed8350e1b472bfee48375034103', '21313213jkads', '43ujrefdk2384us') OR "orders"."id" IN (2, 3, 4))

More information: OR queries with arrays as arguments in Rails 4.

Amu

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

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

Answer 8

The MetaWhere plugin is completely amazing.

Easily mix OR's and AND's, join conditions on any association, and even specify OUTER JOIN's!

Post.where({sharing_level: Post::Sharing[:everyone]} | ({sharing_level: Post::Sharing[:friends]} & {user: {followers: current_user} }).joins(:user.outer => :followers.outer}

Electrothermics

Answer 9

Just add an OR in the conditions

Model.find(:all, :conditions => ["column = ? OR other_column = ?",value, other_value])

Stewartstewed

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

Answer 10

With rails + arel, a more clear way:

# Table name: messages
#
# sender_id:    integer
# recipient_id: integer
# content:      text

class Message < ActiveRecord::Base
  scope :by_participant, ->(user_id) do
    left  = arel_table[:sender_id].eq(user_id)
    right = arel_table[:recipient_id].eq(user_id)

    where(Arel::Nodes::Or.new(left, right))
  end
end

Produces:

$ Message.by_participant(User.first.id).to_sql 
=> SELECT `messages`.* 
     FROM `messages` 
    WHERE `messages`.`sender_id` = 1 
       OR `messages`.`recipient_id` = 1

Pilsen

Answer 11

You could do it like:

Person.where("name = ? OR age = ?", 'Pearl', 24)

or more elegant, install rails_or gem and do it like:

Person.where(:name => 'Pearl').or(:age => 24)

Constrictive

Answer 12

I just extracted this plugin from client work that lets you combine scopes with .or., ex. Post.published.or.authored_by(current_user). Squeel (newer implementation of MetaSearch) is also great, but doesn't let you OR scopes, so query logic can get a bit redundant.

Singular

Answer 13

Using the activerecord_any_of gem, you can write

Book.where.any_of(Book.where(:author => 'Poe'), Book.where(:author => 'Hemingway')

Lev

Add some explanations to your answer – Dermatoplasty

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

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

Answer 14

I'd like to add this is a solution to search multiple attributes of an ActiveRecord. Since

.where(A: param[:A], B: param[:B])

will search for A and B.

Dunaville