Using OR with queries in a scope

Question

In Rails3 I have:

Class Teacher
  #  active                 :boolean
  has_and_belongs_to_many :subjects

Class Subject
  #  active                 :boolean
  has_and_belongs_to_many :teachers

I am trying to construct a Teacher scope that returns all Teachers that are active or are associated with a Subject that is active.

These scopes work individually, but how to combine them as a single scope with an OR?

scope :active_teachers, where(active: true)
scope :more_active_teachers, joins(:subjects).where(:subjects => {active: true})

I've tried this without success:

scope :active_teachers, where(active: true).or(joins(:subjects)
      .where(:subjects => {active: true}))

UPDATE:

I thought I had a solution, but this no longer lazy loads, hits the database twice and — most importantly — returns an array rather than an AR object!

scope :active_teachers, where(active: true) |
                        joins(:subjects).where(:subjects => {active: true})

See my answer to the same question [here](http://stackoverflow.com/a/40269481/1876622). Also note similar questions [here](http://stackoverflow.com/questions/37445203/) and [here](http://stackoverflow.com/questions/1482940/) — HeyZiko, Oct 26 '16 at 20:52

score 5 · Accepted Answer · answered May 12 '13 at 11:14

5

You have Squeel to your rescue. More details here.

Using that, you could define something like:

class Teacher
  ...
  scope :active_teachers, joins{subjects}.where {(active == true) | (subjects.active == true)}
  ...
end

answered May 12 '13 at 11:14

pungoyal

1,768
15
17

+1 for Squeel. It's an excellent resource for complicated apps. – Chris Keele May 15 '13 at 21:41
+1. Thanks Puneet. I was hoping to avoid another gem, but Squeel seems like the ideal solution and I will no doubt need it later in the project anyway. You get the big tick. Thanks. – djoll May 17 '13 at 11:55

Toby Hede · Answer 2 · 2013-05-14T03:54:53.803

3

I think the short answer is you can't.

Oring in the code is going to break lazy loading ... really no way around it as you need the database to make the evaluation. ActiveRecord can't make the evaluations on the scopes without executing each subclause individually.

Something like this the following should work:

joins(:subjects).where("subjects.active = true OR teachers.active = true")

Not quite as elegant, but can be wrapped into a method for reuse.

edited May 14 '13 at 03:54

answered May 12 '13 at 11:36

Toby Hede

36,755
28
133
162

1

You would also need joins: `scope :active_teachers, joins(:subjects).where("subjects.active = true OR teachers.active = true")` – jokklan May 13 '13 at 13:19
1

Even if this isn't as elegant, it's a lot more straightforward. At some point, it's not worth jumping through hoops just to or two conditions together. – Adam Sanderson May 16 '13 at 17:51
That's how I feel about it – Toby Hede May 17 '13 at 01:00
Thanks Toby - I agree with your sentiments. Unfortunately I can't get the above query to give the result I'm after (ie. seems to double count the teachers with active subjects and return no teachers who are themselves active). I'd rather avoid another gem, but I think I'll need to go with Squeel. Thanks for your help! – djoll May 17 '13 at 11:54

score 3 · Answer 3 · edited May 23 '17 at 12:33

3

You can solve this by dropping to AREL. See this SO Question for how to do that.

AREL OR Condition

Or from the AREL Source Code README.md. I think (but haven't verified) that this would translate to the following for your particular example.

teachers.where(teachers[:active].eq(true).or(subjects[:active].eq(true)))

Good Luck!

edited May 23 '17 at 12:33

Community

1
1

answered May 15 '13 at 15:43

engineerDave

3,887
26
28

Thanks Dave! I'll need to further explore that. – djoll May 17 '13 at 11:56

score 1 · Answer 4 · answered May 15 '13 at 16:58

There's a rails pull request for this (https://github.com/rails/rails/pull/9052), but in the meantime, some one has created a monkey patch that you can include in your initializers that will allow you to do this and still give you an ActiveRecord::Relation:

https://gist.github.com/j-mcnally/250eaaceef234dd8971b

With that, you'll be able to OR your scopes like this

Teacher.active_teachers.or.more_active_teachers

or write a new scope

scope :combined_scopes, active_teachers.or.more_active_teachers

Thanks keithepley, I'll keep my eye on that one. I'm a bit reluctant to step off the canonical path, but that patch would do the trick. Thanks. — djoll, May 17 '13 at 11:30

Using OR with queries in a scope

4 Answers4

Linked