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

Resources

• ActiveRecord's where.not and nil | thoughtbot