Search code examples
ruby-on-railsrubysqueel

Using NOT with Squeel

Say I have a set of conditions:

Person.where{(name =~ 'Ernie%') & (salary < 50000) | (name =~ 'Joe%') & (salary > 100000)}

...which will generate SQL as follows:

SELECT "people".* FROM people
  WHERE ("people"."name" LIKE 'Ernie%' AND "people"."salary" < 50000)
  OR  ("people"."name" LIKE 'Joe%' AND "people"."salary" > 100000)

How do I go about inverting the returned set, i.e. updating the Squeel to return the following sql:

SELECT "people".* FROM people
  WHERE NOT(("people"."name" LIKE 'Ernie%' AND "people"."salary" < 50000)
  OR  ("people"."name" LIKE 'Joe%' AND "people"."salary" > 100000))

This is a little contrived, as my issue also involves a dynamically generated set of conditions - I can't change the actual conditions, but just need them wrapping in a NOT().

Anyone any ideas or suggestions on where to look?

Solution

  • You'll want to use the unary minus operator.

    Person.where{-((name =~ 'Ernie%') & (salary < 50000) | (name =~ 'Joe%') & (salary > 100000))}.to_sql
=> "SELECT \"people\".* FROM \"people\"  WHERE (NOT (((\"people\".\"name\" LIKE 'Ernie%' AND \"people\".\"salary\" < 50000) OR (\"people\".\"name\" LIKE 'Joe%' AND \"people\".\"salary\" > 100000))))"