Is it possible to negate a scope in Rails?

Question

I have the following scope for my class called Collection:

scope :with_missing_coins, joins(:coins).where("coins.is_missing = ?", true)

I can run Collection.with_missing_coins.count and get a result back -- it works great! Currently, if I want to get collections without missing coins, I add another scope:

scope :without_missing_coins, joins(:coins).where("coins.is_missing = ?", false)

I find myself writing a lot of these "opposite" scopes. Is it possible to get the opposite of a scope without sacrificing readability or resorting to a lambda/method (that takes true or false as a parameter)?

Something like this:

Collection.!with_missing_coins

score 82 · Answer 1 · answered Jun 01 '15 at 21:52

82

In Rails 4.2, you can do:

scope :original, -> { ... original scope definition ... }
scope :not_original, -> { where.not(id: original) }

It'll use a subquery.

answered Jun 01 '15 at 21:52

Bill Lipa

2,039
1
19
11

for Mongoid I use `self.not(other_scope.selector)` – Cyril Duchon-Doris Jun 11 '16 at 11:42
2

Great idea but in my case, the subquery took twice as long. – gitb Mar 30 '17 at 19:08
2

Sure. That is because it has to find the `id`s of all the rows of `original` and then run a query where these ids are excluded. Even though Rails uses nested query, it is doing the work twice – Misu May 26 '17 at 09:21
It's not a subquery, it's an additional query. – Alex Jul 15 '20 at 21:28
1

I've only ever seen Rails generate a subquery from this. Do you have a counter-example? – Bill Lipa Jul 17 '20 at 15:52

score 15 · Accepted Answer · edited Jul 29 '15 at 06:25

I wouldn't use a single scope for this, but two:

scope :with_missing_coins, joins(:coins).where("coins.is_missing = ?", true)
scope :without_missing_coins, joins(:coins).where("coins.is_missing = ?", false)

That way, when these scopes are used then it's explicit what's happening. With what numbers1311407 suggests, it is not immediately clear what the false argument to with_missing_coins is doing.

We should try to write code as clear as possible and if that means being less of a zealot about DRY once in while then so be it.

score 12 · Answer 3 · edited Sep 07 '16 at 17:27

There's no "reversal" of a scope per se, although I don't think resorting to a lambda method is a problem.

scope :missing_coins, lambda {|status| 
  joins(:coins).where("coins.is_missing = ?", status) 
}

# you could still implement your other scopes, but using the first
scope :with_missing_coins,    lambda { missing_coins(true) }
scope :without_missing_coins, lambda { missing_coins(false) }

then:

Collection.with_missing_coins
Collection.without_missing_coins

thisismydesign · Answer 4 · 2022-06-07T09:03:28.623

4

You can do this programmatically, without a subquery:

scope :my_scope, ...

Entity.where.not(Entity.my_scope.where_values_hash)

See also: https://makandracards.com/makandra/486959-how-to-negate-scope-conditions-in-rails

Note this only works with AR queries and does not work with SQL queries. E.g. where('updated_at < ?', 1.hour.ago) would not be negated.

edited Jun 07 '22 at 09:03

answered Dec 10 '21 at 10:28

thisismydesign

21,553
9
123
126

A similar approach with Arel but didn't work for me. https://jbhannah.net/articles/negating-activerecord-scopes – akostadinov Jan 18 '22 at 18:10
1

This only works for very simple where clauses like `where(something: something)`, and if someone adds something like `where('updated_at < ?', 1.hour.ago)` to the scope, it will break `where_values_hash` in a very hard to track down way. – brainbag Feb 14 '22 at 15:34

score 2 · Answer 5 · edited Feb 11 '20 at 14:44

this might just work, did not test it much. uses rails 5 I guess rails 3 has where_values method instead of where_clause.


scope :not, ->(scope_name) do 
              query = self
              send(scope_name).joins_values.each do |join|
                   query = query.joins(join)
              end
              query.where((send(scope_name).
                    where_clause.send(:predicates).reduce(:and).not))
end

usage

Model.not(:scope_name)

score 2 · Answer 6 · answered Oct 10 '22 at 14:13

With Rails 7, you can use invert_where:

scope :with_missing_coins, -> { joins(:coins).where(coins: { is_missing: true }) }
scope :without_missing_coins, -> { with_missing_coins.invert_where }

However;

It will invert everything before it, so Collection.with_something.without_missing_coins will invert with_something too, returning rows "without something". You'll need to watch out for the order of scopes and conditions: Collection.without_missing_coins.with_something.
Since it inverts the condition and not the argument (ie. it converts it to != true, rather than = false), the negated scope will include null values too; unless the field is defined as null: false.

So it's probably better and clearer to write them explicitly. If you want to merge them, maybe you can use another parameterized scope:

scope :missing_coins, ->(is_missing) { joins(:coins).where(coins: { is_missing: is_missing }) }
scope :with_missing_coins, -> { missing_coins(true) }
scope :without_missing_coins, -> { missing_coins(false) }

score 2 · Answer 7 · answered Nov 30 '22 at 01:57

TL;DR

Collection.with_missing_coins.invert_where

More details

Rails 7 introduced invert_where

It allows you to invert an entire where clause instead of manually applying conditions

class User < ApplicationRecord
  scope :active, -> { where(accepted: true, locked: false) }
end

User.where(accepted: true)
# SELECT * FROM users WHERE accepted = TRUE

User.where(accepted: true).invert_where
# SELECT * FROM users WHERE accepted != TRUE

User.active
# SELECT * FROM users WHERE accepted = TRUE AND locked = FALSE

User.active.invert_where
# SELECT * FROM users WHERE NOT (accepted = TRUE AND locked = FALSE)

Be careful, compare these variants

User.where(role: "admin").active.invert_where
# SELECT * FROM users WHERE NOT (role = 'admin' AND accepted = TRUE AND locked = FALSE)

User.active.invert_where.where(role: "admin")
# SELECT * FROM users WHERE NOT (accepted = TRUE AND locked = FALSE) AND role = 'admin'

And look at this

User.where(accepted: true).invert_where.where(locked: false).invert_where
# SELECT * FROM users WHERE NOT (accepted != TRUE AND locked = FALSE)

invert_where inverts all where conditions before it. Therefore it's better to avoid using this method in scopes because it could lead to unexpected results

Much better to use this method explicitly and only in the beginning of where chain immediately after it is necessary to invert all the previous conditions

score 0 · Answer 8 · answered Jul 15 '21 at 22:36

The answer by @bill-lipa is good, but beware when the scopes you want involve associations or attachments.

If we have the following scope to select all users with a resume attached:

scope :with_resume, -> { joins(:resume_attachment) }

The following negation will cause an exception if with_resume is empty:

scope :without_resume, -> { where.not(id: with_resume) } 
#=> users.without_resume
#=> ActiveRecord::StatementInvalid (PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Therefore you'll need to check first to get the results you want:

scope :without_resume, -> { with_resume.any? ? where.not(id: with_resume) : where.not(id: []) }

score -2 · Answer 9 · answered Sep 21 '19 at 11:37

-2

Update . Now Rails 6 adds convenient and nifty negative enum methods.

# All inactive devices
# Before
Device.where.not(status: :active)
#After 
Device.not_active

Blog post here

answered Sep 21 '19 at 11:37

ZainNazirButt

377
5
13

this does not answer the question. question uses a `join` and does not use any `enum`s – Tim Krins Oct 10 '19 at 19:15

Is it possible to negate a scope in Rails?

9 Answers9

Linked