Rails Active Records - where, where-not and nil
Today my coworker pointed out that confusing behavior of where.not query dealing with nil. We need extra care when filtering SQL queries with nil. I'll put it together for future reference.
all records
User.all.count
#=> 1509
nil | non-nil
Records that have nil comment id
# The "nil" group
User.where(comment_id: nil).count
#=> 191
Records that have non-nil comment id
# The "non-nil" group
User.where.not(comment_id: nil).count
#=> 1318
matching id | the rest
Records that has comment_id 839
User.where(comment_id: [839]).count
#=> 8
Records that does not have comment_id 839
User.where.not(comment_id: [839]).or(User.where(comment_id: nil)).count
#=> 1501
WARNING: The following does not do the job for this type of grouping.
# Bad example
User.where.not(comment_id: [839]).where.not(comment_id: nil).count
User.where.not(comment_id: [nil, 839]).count
matching id | not matching non-nil | nil id
Records that has comment_id 839
# find records that has comment_id 839
User.where(comment_id: [839]).count
#=> 8
Records that do not have comment_id 839 AND that do not have nil comment_id
User.where.not(comment_id: [839]).where.not(comment_id: nil).count
#=> 1310
Records that have nil comment id
User.where(comment_id: nil).count
#=> 191