How to use OR condition in ActiveRecord query

Question

I want to grab all the users that either have an email as the one supplied or the first and last name. So for example:

users = User.where(:first_name => "James", :last_name => "Scott")

which will return all the users that have the first and last name of "James" & "Scott".

users = User.where(:email => "james@gmail.com")

which will return the user with the email as "james@gmail.com".

Is there a way to do a where clause to return either the users with the same first and last name and the user with the email that matches in one where query or do I need to do a merge on the 2 separate where clauses.

possible duplicate of [ActiveRecord OR query](http://stackoverflow.com/questions/3639656/activerecord-or-query) — Andrew Marshall, Jun 03 '12 at 14:52
@Trace: you forgot to select an answer or at least give some feedback. — tokland, Feb 16 '16 at 10:13

Santhosh · Answer 1 · 2016-08-19T09:02:26.743

65

Rails 5 comes with an or method.

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

User.where(first_name: 'James', last_name: 'Scott')
    .or(User.where(email: 'james@gmail.com'))

edited Aug 19 '16 at 09:02

answered May 09 '16 at 13:31

Santhosh

28,097
9
82
87

3

For Rails 5 application this should be considered the most correct answer. – Brent Matzelle Oct 12 '16 at 20:14
probably it should better be `or(unscoped.where(email: 'james@gmail.com'))` – akostadinov Aug 04 '22 at 13:18

Anil · Answer 2 · 2012-06-03T14:45:41.583

16

Try this:

users = User.where("(first_name = 'James' and last_name = 'Scott') or email = 'james@gmail.com'")

To interpolate variables:

users = User.where("(first_name = ? and last_name = ?) or email = ?", first_name, last_name, email)

edited Jun 03 '12 at 14:45

answered Jun 03 '12 at 14:32

Anil

3,899
1
20
28

tokland · Answer 3 · 2016-09-27T20:26:09.930

9

If you prefer a DSL approach (with no SQL), can use the underlying Arel layer:

t = User.arel_table
users = User.where(
  (t[:first_name].eq('Ernie').and(t[:last_name].eq('Scott')).
    or(t[:email].eq('james#gmail.com'))
)

That's a bit ugly, but if you use some wrapper over Arel (for example, this one), the code is much nicer:

users = User.where(
  ((User[:first_name] == 'Ernie') & (User[:last_name] == 'Scott')) |
   (User[:email] == 'james#gmail.com')
)

edited Sep 27 '16 at 20:26

answered Jun 03 '12 at 14:42

tokland

66,169
13
144
170

1

Interesting wrapper, I've always disliked having to use arel, but I might be less averse to it with this; nice. – Andrew Marshall Jun 03 '12 at 14:45

DGM · Answer 4 · 2012-06-03T17:57:26.653

5

If you don't want to write SQL as the others have mentioned, you could access the arel structures directly (looks like another answer has that) or use the squeel gem to do it much more elegantly:

User.where{(first_name == 'Ernie') & (last_name == 'Scott') | (email == 'james#gmail.com')}

edited Jun 03 '12 at 17:57

answered Jun 03 '12 at 14:37

DGM

26,629
7
58
79

yeah I guess that would be more like ruby. In any case, it would need a tweak to use variables as well. – DGM Jun 03 '12 at 17:58

score 1 · Answer 5 · answered Jun 03 '12 at 14:30

1

You could use string conditions, as in Client.where("orders_count = ? AND locked = ?", params[:orders], false), and use OR in conjunction with AND. Be careful with the priority of those operators though.

Cf Active Record Query Interface guide

answered Jun 03 '12 at 14:30

ksol

11,835
5
37
64

How to use OR condition in ActiveRecord query

5 Answers5

Linked