Rails Not query on entire Where clause

Question

Is there a straight-forward way to negate an entire where expression using ActiveRecord/ARel? It seems that where.not(), when given a hash of arguments, negates each expression individually, as opposed to negating the entire thing with a single SQL NOT.

Rails Example

Thing.where.not(attribute1: [1,2], attribute2: [3,4], attribute3: [5,6])

Would produce SQL:

select * from things where attribute1 NOT IN (1,2) AND attribute2 NOT IN (3,4) AND attribute3 NOT IN (5,6)

This isn't what I'm trying to do though. I want to negate the entire where clause with a single NOT.

select * from things where NOT(attribute1 IN (1,2) AND attribute2 IN (3,4) AND attribute3 IN (5,6))

In boolean notation, Rails seems to favor negating each component of the WHERE clause like this:

!(a) && !(b) && !(c)

But I want to negate the entire expression:

! [ (a) && (b) && (c) ]

Using DeMorgan's Law, I could write my query as !a || !b || !c, but that will result in some rather long and ugly code (less long with Rails 5 and or, but still ugly). I was hoping there is some syntactic sugar I'm missing using ActiveRecord or ARel?

Background Story

I'm writing a Ransack Equality Scope (e.g. _eq) to search for a condition and its condition's opposite.

scope :can_find_things_eq, ->(boolean = true) {
  case boolean
    when true, 'true'
      where(conditions_for_things)
    when false, 'false'
      where.not(conditions_for_things)
    end
  }

def self.ransackable_scopes(_auth_object = nil)
  %i[can_find_things_eq]
end

If I use my Rails 5 suggestion above and my example I started out with, I can get my negation query to work...but the code is long and ugly.

where.not(attribute1: [1,2]).or(where.not(attribute2:
  [3,4)).or(where.not(attribute3: [5,6))

Chaining Ors and WhereNots works, but its not very readable. Is there a better way to negate this where other than having to negate it manually/logically using DeMorgan's Law?

Thanks!

Stenography

did you try Thing.where.not(Thing.where(attribute1: [1,2], attribute2: [3,4], attribute3: [5,6])) ? – Immersionism

No, because your inner query generates AND nodes, not OR nodes. Please refer to my first example. – Stenography

Stumbled across this SO. Not sure if the following will help as I don't know any of the technologies tagged other than SQL. However, if the booleans can be forced to behave as 1s (true) and 0s (false) by doing arithmetic on them, then if any is false, multiplying them all together will mean multiplying by zero and therefore yield zero. So ![(a) && (b) && (c)] would become equivalent to the test (a)*(b)*(c) == 0. – Hypotaxis

Answer 1

If you are using Rails 7, you can use invert_where. This will form the query exactly as you need

! [ (a) && (b) && (c) ]

Craner

Excellent suggestion. Rails 7 is still alpha but I will certainly remember this! – Stenography

I am glad you found this helpful. Here is an amazing blog on the same if you want to know more on it. Thanks! – Craner

Answer 2

You can produce the desired result with Arel as follows:

table = Thing.arel_table
clause = Arel::Nodes::Not.new(
  table[:attribute1].in([1,2]).and(
    table[:attribute2].in([2,3]).and(
      table[:attribute3].in([5,6])
    )))
Thing.where(clause)

Or

Thing.where.not(
  table[:attribute1].in([1,2]).and(
    table[:attribute2].in([2,3]).and(
      table[:attribute3].in([5,6])
    )))

Mcnabb

Answer 3

With invert_where in rails 7+

Thing.where(attribute1: [1, 2], attribute2: [3, 4], attribute3: [5, 6)).invert_where

Be careful it inverts all condition before it

Whooper