CODEWITHSUNDEEP
ruby-on-railsactiverecord
Matthias Michael Engh
Matthias Michael Engh

Reputation: 1259

active record where not doesn't include nil

This question builds on active record where method and not

Item.where("color != ?", 'red'), or an analog with .where.not doesn't return nil values. How do I query to include those as well?

Upvotes: 5

Views: 3317

Answers (1)

Nathan
Nathan

Reputation: 8036

The underlying issue is how SQL databases make comparisons with NULL, not how Rails or ActiveRecord work. If you want records whose color is NULL, you have to explicitly include them with something like where color IS NULL. NULL is not considered to be "not red". Ultimately, to get what you want, you need an or in the SQL to cover both cases.

In ActiveRecord, you need this:

Item.where("color != ? or color is null", 'red')

You could also do this:

Item.where("color != ?", 'red').or(Item.where(color: nil))

Upvotes: 10

Related Questions