Since Rails 6.1, if we use where.not with multiple attributes, it applies logical NAND (NOT(A) OR NOT(B)) instead of NOR (NOT(A) AND NOT(B)). If you do not take care, this change will increase the matched set.
Examples
"Don't send newsletters neither to admins nor to trashed users!" becomes "Don't send newsletters to trashed admins".
User.where.not(role: 'admin', trashed: true)
# Before Rails 6.1, with NOR
=> "SELECT "users".* FROM "users" WHERE "users"."role" != 'admin' AND "users"."trashed" != TRUE"
# Equivalent:
=> "SELECT "users".* FROM "users" WHERE NOT ("users"."role" = 'admin' OR "users"."trashed" = TRUE")
# From Rails 6.1, with NAND
=> "SELECT "users".* FROM "users" WHERE NOT ("users"."role" = 'admin' AND "users"."trashed" = TRUE)"
# Use this query to keep applying NOR
User.where.not(role: 'admin').where.not(trashed: true)
=> "SELECT "users".* FROM "users" WHERE "users"."role" != 'admin' AND "users"."trashed" != TRUE"
In the following illustration, assume the left circle is "admins" and the right circle is "trashed users". The red area corresponds to the records in the resulting scope:
| NOR | NAND (Rails 6.1+) |
|---|---|
 |
 |
Using an Array as an argument is still working as expected
User.where.not(email: [nil, ''])
=> "SELECT "users".* FROM "users" WHERE NOT (("users"."email" = '' OR "users"."email" IS NULL))"
# This is equivalent to the following query. So here we are still using NOR.
=> "SELECT "users".* FROM "users" WHERE "users"."email" != '' AND "users"."email" IS NOT NULL"
Posted by Julian to makandra dev (2021-12-15 15:14)