active record where not doesn't include nil

Question

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?

Answer 1

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