Today I Learned

A Hashrocket project

ActiveRecord not is not boolean algebra negation

Today I learned that rails ActiveRecord not query is not to be considered a boolean algebra negative. Let’s see by an example:

User.where(name: "Jon", role: "admin")

This will produce a simple sql query:

SELECT "admins".*
FROM "admins"
WHERE "admins"."name" = $1
  AND "admins"."role" = $2
[["name", "Jon"], ["role", "admin"]]

If we get the same where clause and negate it:

User.where.not(name: "Jon", role: "admin")

Then we get:

SELECT "admins".*
FROM "admins"
WHERE "admins"."name" != $1
  AND "admins"."role" != $2
[["name", "Jon"], ["role", "admin"]]

But I was expecting a query like:

SELECT "admins".*
FROM "admins"
WHERE ("admins"."name" != $1
   OR "admins"."role" != $2)
 [["name", "Jon"], ["role", "admin"]]

So if you want to produce a query like that you’ll have to build on your own:

User.where.not(name: "Jon").or(User.where.not(role: "admin"))

Just beaware of that when using not with multiple where clauses.

Looking for help? Hashrocket has been an industry leader in Ruby on Rails since 2008. Rails is a core skill for each developer at Hashrocket and we'd love to take a look at your project. Contact us and find out how we can help you.