CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Rene Chan
Rene Chan

Reputation: 985

Using Where and or with two attributes in Rails

I would like to use a scope function and make the query so that it applies the scopes, and apply either of two conditions:

I was looking for something like this:

apply_scopes(Message).where((user_id: current_user.id) || (recipient_id: current_user.id))

but it has a syntax error obviously.

When is use:

apply_scopes(Message).where(user_id: current_user.id).or(Message.where(recipient_id: current_user.id))

i am under the impression that the scope is not taken in account.

What is the proper way of using WHERE and OR with two variable please ?

Upvotes: 1

Views: 593

Answers (1)

mu is too short
mu is too short

Reputation: 434585

You have to be careful when using Model.or. OR in SQL has lower precedence than AND so you can get surprising results. For example, suppose there's a scope some_scope that includes results with id = 6 but not those with id = 11, then this:

Model.some_scope.where(id: 6).or(Model.where(id: 11))

will produce SQL like this:

select * from models where some_scope and id = 6 or id = 11

and adding parentheses to hilight the precedence:

select * from models where (some_scope and id = 6) or id = 11

and we find that the query finds both 6 and 11.

But if we apply the scope at the end:

Model.where(id: 6).or(Model.where(id: 11)).some_scope

we get SQL like this:

select * from models where (id = 6 or id = 11) and some_scope

and only 6 is found.

I'd probably go with a string condition and a named placeholder:

apply_scopes(Message).where('messages.user_id = :uid or messages.recipient_id = :uid', uid: current_user.id)

to avoid confusion. In this particular case, you could also add another scope to Message if you want to hide the string:

scope :involving, ->(user) { where('messages.user_id = :uid or messages.recipient_id = :uid', uid: user) }

Presumably you'll be doing a lot of this sort of thing so an extra scope makes sense.

Upvotes: 1

Related Questions