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]
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 isNULL
, since inNOT NULL
equalsNULL
in SQL.
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(...)
.
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.
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.
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.
where_values_hash
Finally, you may use where_values_hash
from ActiveRecord::Relation
to construct a single SQL query which NOT
s 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:
where_values_hash
does not include those.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.