113

How do you do an or query in Rails 5 ActiveRecord? Also, is it possible to chain or with where in ActiveRecord queries?

K M Rakibul Islam
  • 33,760
  • 12
  • 89
  • 110

5 Answers5

242

The ability to chain or clause along with where clause in ActiveRecord query will be available in Rails 5. See the related discussion and the pull request.

So, you will be able to do the following things in Rails 5:

To get a post with id 1 or 2:

Post.where('id = 1').or(Post.where('id = 2'))

Some other examples:

(A && B) || C:

    Post.where(a).where(b).or(Post.where(c))

(A || B) && C:

    Post.where(a).or(Post.where(b)).where(c)
K M Rakibul Islam
  • 33,760
  • 12
  • 89
  • 110
  • 3
    How can I get (A || B) && ( C || D). I tried Post.where(a).or(Post.where(b)).where(c).or(Post.where(d)) but It produces as: (A || B) && C || D – Imran Ahmad May 28 '16 at 13:22
  • 3
    @Imran i believe it would be `Post.where(a).or(Post.where(b)).where(Post.where(c).or(Post.where(d)))` this should create (a || b) && (c || d) – engineersmnky Oct 19 '16 at 14:26
  • 1
    @Imran This doesn't seem to work for me: I get `ArgumentError: Unsupported argument type: # (MyModel::ActiveRecord_Relation)` – Suan Mar 14 '18 at 15:39
  • 6
    The equivalent to .or that takes a relation and produces an and is .merge. (A || B) && ( C || D) can be produced by Post.where(a).or(Post.where(b)).merge(Post.where(c).or(Post.where(d))) – Siim Liiser Mar 13 '19 at 10:45
  • @SiimLiiser where is `merge` coming from ? if its `Array#merge` that does not work for me, as I want a single query to be executed. and also being able to pass the whole thing as a relation – Mathieu J. Sep 26 '19 at 12:15
  • 2
    @MathieuJ. It's ActiveRecord::Relation#merge. https://api.rubyonrails.org/classes/ActiveRecord/SpawnMethods.html#method-i-merge – Siim Liiser Sep 27 '19 at 16:23
14

We don't need to wait for rails 5 to use this OR query. We can also used it with rails 4.2.3. There is a backport here.

Thank to Eric-Guo for gem where-or, Now we can add this OR functionality in >= rails 4.2.3 also using this gem.

Dipak Gupta
  • 7,321
  • 1
  • 20
  • 32
8

(Just an addition to the answer by K M Rakibul Islam.)

Using scopes, the code can become prettier (depending on the eyes looking):

scope a,      -> { where(a) }
scope b,      -> { where(b) }

scope a_or_b, -> { a.or(b) }
Nicholas
  • 2,147
  • 3
  • 23
  • 31
5

I needed to do a (A && B) || (C && D) || (E && F)

But in Rails 5.1.4's current state this get's too complicated to accomplish with the Arel or-chain. But I still wanted to use Rails to generate as much of the query as possible.

So I made a small hack:

In my model I created a private method called sql_where:

private
  def self.sql_where(*args)
    sql = self.unscoped.where(*args).to_sql
    match = sql.match(/WHERE\s(.*)$/)
    "(#{match[1]})"
  end

Next in my scope I created an array to hold the OR's

scope :whatever, -> {
  ors = []

  ors << sql_where(A, B)
  ors << sql_where(C, D)
  ors << sql_where(E, F)

  # Now just combine the stumps:
  where(ors.join(' OR '))
}

Which will produce the expected query result: SELECT * FROM `models` WHERE ((A AND B) OR (C AND D) OR (E AND F)).

And now I can easily combine this with other scopes etc. without any wrongful OR's.

The beauty being that my sql_where takes normal where-clause arguments: sql_where(name: 'John', role: 'admin') will generate (name = 'John' AND role = 'admin').

mtrolle
  • 2,234
  • 20
  • 19
  • I think you can use `.merge` as the equivalent of &&, and build a proper tree to capture your parens. Something like... `(scopeA.merge(scopeB)).or(scopeC.merge(scopeD)).or(scopeE.merge(scopeF))`, assuming each of the scopes looks something like `Model.where(...)` – nar8789 Oct 10 '19 at 23:21
  • Check out this before using merge - https://github.com/rails/rails/issues/33501 – Aarthi Feb 01 '20 at 17:15
1

Rails 5 has ability for or clause with where. For Example.

User.where(name: "abc").or(User.where(name: "abcd"))
Foram
  • 483
  • 5
  • 12