Using NOT operator with Squeel?

Question

How do I write a SQL query using Squeel?

    SELECT * FROM documents where (NOT document.deleted OR document.deleted IS NULL)

I tried:

    Document.where { (-deleted) | (deleted == nil) }

but get this error:

    NoMethodError: undefined method `|' for deleted.-@:Squeel::Nodes::KeyPath

what you mean by `NOT document.deleted`? – gabrielhilal Feb 18 '13 at 12:15 — gabrielhilal, Feb 18 '13 at 12:15
Thats mean deleted not equal true – mma Feb 18 '13 at 12:32 — mma, Feb 18 '13 at 12:32

score 2 · Answer 1 · answered Feb 18 '13 at 12:40

NOT will reverse the returned set, so you must define what you are comparing.

(-deleted) #there is nothing to reverse

Based in your comment, I think you mean deleted not equal true, so:

Document.where { -(deleted == true) | (deleted == nil) }

However, reverse of true is false, right? So, you can say:

Document.where { (deleted == false) | (deleted == nil) }

score 2 · Accepted Answer · answered Feb 18 '13 at 12:41

2

It's very important to notice that ActiveRecord stores boolean values as string flags in the database. Therefore, in the case you mentioned, document.deleted equals 'f' which is a truthy value. That's why you can't simply use the NOT operator.

Perhaps what you're looking for is:

Document.where { deleted.eq(false) | deleted.eq(nil) }

Cheers

answered Feb 18 '13 at 12:41

Andre Bernardes

1,623
12
11

Just a quick advice: in your migrations, make your boolean columns default to `false`. Then you can avoid all the annoying `| foo.eq(nil)` clauses. – Andre Bernardes Feb 18 '13 at 12:45

Using NOT operator with Squeel?

2 Answers2