Ruby on Rails 3 howto make 'OR' condition

Question

I need an SQL statement that check if one condition is satisfied:

SELECT * FROM my_table WHERE my_table.x=1 OR my_table.y=1

I want to do this the 'Rails 3' way. I was looking for something like:

Account.where(:id => 1).or.where(:id => 2)

I know that I can always fallback to sql or a conditions string. However, in my experience this often leads to chaos when combining scopes. What is the best way to do this?

Another related question, is how can describe relationship that depends on an OR condition. The only way I found:

has_many :my_thing, :class_name => "MyTable",  :finder_sql => 'SELECT my_tables.* ' + 'FROM my_tables ' +
'WHERE my_tables.payer_id = #{id} OR my_tables.payee_id = #{id}'

However, these again breaks when used in combinations. IS there a better way to specify this?

Mike Campbell · Answer 1 · 2013-11-06T15:00:42.030

113

Account.where(id: [1,2]) no explanation needed.

edited Nov 06 '13 at 15:00

answered Dec 10 '12 at 11:02

Mike Campbell

7,921
2
38
51

The cleanest, DRYest one I've seen. Good Job! – Eric Wanchic Sep 04 '13 at 00:51
11

This is great, and so simple. I've also learned that you can use `nil` safely: e.g. `Thing.where(foo: [1, nil])` will get translated to `SELECT "things".* FROM "things" WHERE (("things"."foo" = 1 OR "things"."foo" IS NULL))`. Brilliant! – zkcro Nov 13 '13 at 03:48
But for example if you use "A is not null OR A is null" you can't use this answer, but nevertheless you can use `Account.arel_table[:id].not_eq(nil)` and `.or()` combinations in rails 3 – Daniel Antonio Nuñez Carhuayo May 19 '15 at 19:21
Loving this solution. Every other answer out there is so needlessly complex! – Ryan.lay Oct 25 '15 at 04:26
1

This is an answer to a different question. The original question asks `my_table.x=1 OR my_table.y=1` x and y are different columns in the table – Josh Mar 02 '18 at 22:08
This works for only one field. – Thomas Oct 18 '22 at 20:07

weston · Answer 2 · 2015-01-31T07:31:55.163

76

This will works in Rails 5, see rails master :

Post.where('id = 1').or(Post.where('id = 2'))
# => SELECT * FROM posts WHERE (id = 1) OR (id = 2)

For Rails 3.0.4+:

accounts = Account.arel_table
Account.where(accounts[:id].eq(1).or(accounts[:id].eq(2)))

edited Jan 31 '15 at 07:31

answered Apr 29 '11 at 17:33

weston

1,972
17
17

5

Definitely dislike this syntax. Still, though, interesting. – DRobinson Aug 12 '12 at 06:42
I like it because it can be combinatory – mdesantis May 20 '14 at 12:02

score 16 · Answer 3 · answered Apr 18 '12 at 22:33

16

Those arel queries are unreadable to me.

What's wrong with a SQL string? In fact, the Rails guides exposes this way as the first way to make conditions in queries: http://guides.rubyonrails.org/active_record_querying.html#array-conditions

So, I bet for this way to do it as the "Rails way":

Account.where("id = 1 OR id = 2")

In my humble opinion, it's shorter and clearer.

answered Apr 18 '12 at 22:33

David Morales

17,816
12
77
105

8

Prefer this, but I would use parameterized values. `Account.where("id = ? OR id = ?",1,2)` – spyle Sep 11 '14 at 14:06
I don't see any reason for parameterized values when using simple numbers. – David Morales Sep 15 '14 at 14:35
If you are hardcoding the id then no simple numbers would be fine but I would suspect these are being returned from elsewhere so paramterised is the way to go to avoid sql injection. – Mark Davies Jun 22 '15 at 11:23
Sure, as the first commenter states, using parameters is the good way to do it. I just wrote hardcoded numbers as an example. – David Morales Jun 23 '15 at 11:26

score 9 · Accepted Answer · answered Jul 20 '10 at 15:43

9

Sadly, the .or isn't implemented yet (but when it is, it'll be AWESOME).

So you'll have to do something like:

class Project < ActiveRecord::Base
  scope :sufficient_data, :conditions=>['ratio_story_completion != 0 OR ratio_differential != 0']
  scope :profitable, :conditions=>['profit > 0']

That way you can still be awesome and do:

Project.sufficient_data.profitable

answered Jul 20 '10 at 15:43

Jesse Wolgamott

40,197
4
83
109

from the arel github site: The OR operator is not yet supported. It will work like this: users.where(users[:name].eq('bob').or(users[:age].lt(25))) – hjuskewycz Jul 21 '10 at 11:18

jpemberthy · Answer 5 · 2013-08-27T00:15:46.347

6

I'd go with the IN clause, e.g:

Account.where(["id in (?)", [1, 2]])

edited Aug 27 '13 at 00:15

answered Jul 20 '10 at 14:51

jpemberthy

7,473
8
44
52

That would be appropriate, but notice the author is looking @ 2 different fields... – Brian Jul 20 '10 at 14:58
Oh yes that's true, I wrote the answer based in this statement `Account.where(:id => 1).or.where(:id => 2)`. Obviously It doesn't work for different fields. – jpemberthy Jul 20 '10 at 16:04
2

Won't work if one of the value in array is `nil`. Say you want to find all records where "viewed" is either `false` or `nil` and you do `Notification.where(:viewed => [false, nil])`, it won't work, as MySQL requires the syntax `viewed = 0 OR viewed IS NULL` and *won't work with* `viewed = 0 OR viewed = NULL` or `viewed IN (0, NULL)`. – Vikrant Chaudhary Jul 11 '11 at 08:08
`Account.where(["id in (?)", [1, 2]])` you forgot the `( ? )` – antpaw Aug 20 '13 at 17:42

score 5 · Answer 6 · answered Jul 10 '12 at 03:36

5

I've used the Squeel gem (https://github.com/ernie/squeel/) to do OR queries and it works beautifully.

It lets you write your query as Account.where{(id == 1) | (id == 2)}

answered Jul 10 '12 at 03:36

brad

9,573
12
62
89

score 4 · Answer 7 · answered Jul 10 '12 at 14:03

4

You can define an Array as value in the :conditions Hash.

So you could do for example:

Account.all(:conditions => { :id => [1, 2] })

Tested with Rails 3.1.0

answered Jul 10 '12 at 14:03

TehQuila

628
1
8
19

1

this is inefficient because you are retrieving all items from database and checking in memory – adrian.coroian Apr 21 '14 at 23:31

Santhosh · Answer 8 · 2016-10-18T09:02:03.937

2

Alternate syntax using Hash

Account.where("id = :val1 OR id = :val2", val1: 1, val2: 2).

This is particularly useful, when the value is compared with multiple columns. eg:

User.where("first_name = :name OR last_name = :name", name: 'tom')

edited Oct 18 '16 at 09:02

answered May 09 '16 at 13:27

Santhosh

28,097
9
82
87

score 1 · Answer 9 · answered Jan 14 '17 at 11:15

1

With rails_or, you could do it like:

Account.where(id: 1).or(id: 2)

(It works in Rails 4 and 5, too.)

answered Jan 14 '17 at 11:15

khiav reoy

1,373
13
14

Ruby on Rails 3 howto make 'OR' condition

9 Answers9

Linked