Read more

How to negate scope conditions in Rails

Arne Hartherz
October 16, 2020Software engineer at makandra GmbH

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]

Illustration money motivation

Opscomplete powered by makandra brand

Save money by migrating from AWS to our fully managed hosting in Germany.

  • Trusted by over 100 customers
  • Ready to use with Ruby, Node.js, PHP
  • Proactive management by operations experts
Read more Show archive.org snapshot

As an example, consider the following model.

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

Now what if we want a scope of users that are not admins? While you could declare a second scope like scope :non_admins, -> { where(role: ['guest', 'editor']) }, there are ways to use the opposite of the admins scope.

Important

All solutions that are not using a subquery the role NOT IN (...) condition will not return rows where role is NULL, since in NOT NULL equals NULL in SQL.

Option 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(...).

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

Or, pick one of the other options.

Option B: Subquery

You could just use a subquery. Doing that with scopes is easy:

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.

Option C: 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.

Option D: 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.

Arne Hartherz
October 16, 2020Software engineer at makandra GmbH
Posted by Arne Hartherz to makandra dev (2020-10-16 16:41)