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
orA != 0
orA IS NULL
. Don't forget the third case!
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 inNOT NULL
equalsNULL
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 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:
- 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 emptywhere_values_hash
.
This should be the least preferred option. Often times, you are better off using invert_where
(if available), Arel or a subquery.