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]

Caution

Databases use a 3-valued logic. Either A = 0 or A != 0 or A IS NULL. Don't forget the third case!

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

• We build reliable cloud solutions with Infrastructure as code
• We are experts in security, Linux and databases
• We support your dev team to perform

Read more Show archive.org snapshot

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

As an example, consider the following model.

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

The "mathematical NOT"

You know this one from basic set theory. It gets you the "complementary set". It is what you would intuitively expect, if you don't work with DBs all the time. It works like this:

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

# original scope
User.admins

# inverted scope
User.where.not(id: User.admins)

Implementation: Subquery

For implementing this, always use a subquery, as already shown:

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

Rails will generate a query like the following.

SELECT * 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.

The "inverted constraints"

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

# original scope
User.admins

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

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

Implementation A: invert_where (Rails 7+)

User.admins.invert_where

Rails produces a query with every scope condition inverted.

SELECT "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.

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

Implementation 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:

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

SELECT "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.

Implementation 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.

User.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.

SELECT * 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.