How to write a where.not query with multiple conditions in rails 4

Question

What I want to express is this:

Model.where("a IS NOT NULL `**`OR`**` b IS NOT NULL")

In Rails 4, where.not clause is enabled:

Model.where.not(a: nil, b: nil)

However, This expression equals

Model.where("a IS NOT NULL `**`AND`**` b IS NOT NULL")

How can I express

"a IS NOT NULL `**`OR`**` b IS NOT NULL"

with where.not clause?

Answer 1

You could create your query as you've tried with where.not for the a and b attribute:

query = Model.where.not(a: nil, b: nil)

And then use inject in the where values for the query created before by passing the or operator:

Model.where(query.where_values.inject(:or))

This will give you a query like:

SELECT "model".*
FROM "model"
WHERE (
  "model"."a" IS NOT NULL OR
  "model"."b" IS NOT NULL
)

Despite of the first one which gives you something using the AND operator, like:

SELECT "model".*
FROM "model"
WHERE ("model"."a" IS NOT NULL) AND
      ("model"."b" IS NOT NULL)

Answer 2

Important: Rails 6.1 changes the behavior of where.not with multiple attributes!

---

Up to Rails 5.2 - NOR ( NOT(A) AND NOT(B) ).

Up to Rails 5.2, if we use where.not with multiple attributes, it applies logical NOR (NOT(A) AND NOT(B)) in WHERE clause of the query.

Post.where.not(source_type: "Feed", source_id: 100).to_sql

# => SELECT "posts".* FROM "posts" WHERE "posts"."source_type" != 'Feed' AND "posts"."source_id" != 100

Rails 6 - NOR ( NOT(A) AND NOT(B) ) with deprecation.

Rails 6 adds a deprecation warning.

Post.where.not(source_type: "Feed", source_id: 100)

DEPRECATION WARNING: NOT conditions will no longer behave as NOR in Rails 6.1.
To continue using NOR conditions, NOT each conditions manually 
(`.where.not(:source_type => ...).where.not(:source_id => ...)`).

Rails 6.1+ - NAND ( NOT(A) OR NOT(B) ).

Rails 6.1+ will change where.not working to NAND (NOT(A) OR NOT(B)).

Post.where.not(source_type: "Feed", source_id: 100).to_sql

# => SELECT "posts".* FROM "posts" WHERE ("posts"."source_type" != 'Feed' OR "posts"."source_id" != 100)

---

Sources:

• Rails 6 deprecates where.not as NOR & Rails 6.1 as NAND.
• Link to the corresponding PR.
• Link to the relevant discussion.

Answer 3

There is no rails-way to do this in rails 4.

You could try rails_or which give you or and or_not method:

Model.where.not(a: nil).or_not(b: nil)

Or upgrade to rails 5 and write the query as:

Model.where.not(a: nil).or(Model.where.not(b: nil))