How to negate scope conditions in Rails

Sometimes you want to find the inverse of an ActiveRecord scope. Depending on what you want to achieve, this is quite easy with Rails 7, and a bit more complicated with Rails 6 and below, or when the inverse scope may contain NULL values. [1]

There are two different ways of "inverting a scope":

As an example, consider the following model.

Copyclass User < ApplicationRecord
  scope :admins, -> { where(role: ['admin', 'superuser']) }
  # ...
end

Mathematical NOT

You know this one from basic set theory. It proces the "complementary set" for your scope, like so:

• compute all the elements within the original scope
• take their IDs
• compute all elements whose IDs are not in that set

Implementation: Subquery

Always use a subquery to implement this.

For example, to get all users which are not part of User.admins, say:

CopyUser.where.not(id: User.admins)

Rails will generate a query like the following.

CopySELECT * FROM users WHERE id NOT IN (SELECT id FROM users WHERE role IN ('admin', 'superuser'))

Note that subqueries may be inefficient on large tables. However, this works with complex conditions, as the subquery is evaluated in full. You will even see records with NULL values.

Inverted constraints

For this one, we don't invert the result. Instead, we negate the WHERE clause.

Copy# original scope
User.admins

# inverted scope
User.where.not(role: ['admin', 'superuser'])

Caution

Databases use a 3-valued logic. Either A = 0 or A != 0 or A IS NULL.
If your column allows NULL values, then this is a place that might trip you up. A user can be neither in the original scope, nor in the inverted scope, if their role column contains NULL.

Option A: invert_where (Rails 7+)

CopyUser.admins.invert_where

Rails produces a query with every scope condition inverted.

CopySELECT "users".* FROM "users" WHERE "users"."role" NOT IN ('admin', 'superuser')

That also works for more complex conditions, e.g. with ranges like where(created_at: 2.days.ago..), including orphan checks via where.missing(...).

Important

The inverted scope will not return rows where role is NULL, since in NOT NULL equals NULL in SQL. If this important for you, use a solution with a subquery (role NOT IN (...)).

Backport for Rails ≤ 6

You could backport invert_where to older Rails versions, by doing what Rails 7 does Show archive.org snapshot , e.g. in your ApplicationRecord.

Copyclass ApplicationRecord < ActiveRecord::Base
  def self.invert_where
    spawn.tap do |relation|
      relation.where_clause = relation.where_clause.invert
    end
  end
end

Option B: Arel

As described in an excellent article by @jbhannah Show archive.org snapshot , you can use Arel to invert conditions.
Arel's API is quite arcane, but works well:

CopyUser.where(User.admins.arel.constraints.reduce(:and).not)

CopySELECT "users".* FROM "users" WHERE NOT ("users"."role" IN ('admin', 'superuser'))

That also works with range conditions like where(created_at: 1.week.ago..), but does not work for missing (because its join statement is lost).

So, sadly this is not a drop-in replacement for invert_where -- but can be a valid option when a subquery is too expensive.

Option C: Use where_values_hash

Finally, you may use where_values_hash from ActiveRecord::Relation to construct a single SQL query which NOTs your scope's conditions.

CopyUser.where.not(User.admins.where_values_hash)

That's just like saying User.where.not(role: ['admin', 'superuser']), but programmatically.

The resulting SQL is quite pretty.

CopySELECT * FROM users WHERE role NOT IN ('admin', 'superuser')

This only works with "simple" scopes and conditions. It will not work for example for:

• Scopes using joins or similar, as where_values_hash does not include those.
• Conditions where values are ranges, e.g. where(created_at: 1.week.ago..) has an empty where_values_hash.

This should be the least preferred option. Often times, you are better off using invert_where (if available), Arel or a subquery.