Using NOT with Squeel

Question

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?

score 8 · Accepted Answer · answered May 24 '12 at 15:19

8

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))))"

answered May 24 '12 at 15:19

Ernie

896
5
7

2

Thanks for that! Was struggling to figure that out. Is that actually documented anywhere on the Squeel github page, or is this an undocumented feature? – brad Apr 01 '13 at 12:01
Is giving an error: `NoMethodError: undefined method `-@' for false:FalseClass` – josal Sep 09 '15 at 08:34

Using NOT with Squeel

1 Answers1