Reputation: 1259
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
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