2

I am trying to search to see if a posts value is either false or nil. 

Post.where.not(:top_card => true)

This only returns the false values. I have confirmed that if I do either of the following it returns the correct values

Post.where(:top_card => false)

or

Post.where(:top_card => nil)

Any idea how I can get the posts with either the nil and false value?

Am I using the wrong method to search?

Tall Paul
  • 2,398
  • 3
  • 28
  • 34
  • 1
    Try with `Post.where(top_card: [false, nil]`. – Vucko Dec 21 '14 at 23:46
  • @Vucko That worked perfectly, how does the [] work? Does it treat it like an array that it just iterates over finding all the values for? Also if you submit that as an answer I will make it as correct – Tall Paul Dec 21 '14 at 23:54

2 Answers2

3

Use like Post.where(top_card: [false, nil].

Which will produce SELECT * FROM posts WHERE (posts.top_card IN (false, nil))

See more at Rails guides - Subset Conditions.

Vucko
  • 20,555
  • 10
  • 56
  • 107
  • I think the following should also work: `Post.where("top_card != ?", true)` – SHS Dec 22 '14 at 00:41
  • I am curios to know why code `Post.where.not(:top_card => true)` in the question does not work ? – Vijay Meena Jul 08 '16 at 05:04
  • It should work. Create a new question with you issue and post your code. – Vucko Jul 08 '16 at 06:17
  • @Humza that does not work. The accepted answer is fine for a boolean value where possibilities are very few, but does not help on a string field e.g. `User.where.not(status: "active")` will not return users with status nil – ryan2johnson9 Mar 06 '18 at 08:33
  • @VijayMeena see [this post for an explanation](https://forum.upcase.com/t/why-does-where-not-on-string-columns-ignores-null-values/3167) – ryan2johnson9 Mar 06 '18 at 08:54
1

Post.where("top_card != ? OR top_card IS NULL")

NOTE that this will work with string fields while the accepted answer by @Vucko will not. i.e. in cases where you cannot put an exhaustive list of all options in the query.

e.g. this will work

User.where.not("status != ? OR status IS NULL", "active")

thanks to this forum

ryan2johnson9
  • 724
  • 6
  • 21