# 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

```rb
User.all.count
#=> 1509
```

## nil | non-nil

Records that have nil comment id

```rb
# The "nil" group
User.where(comment_id: nil).count
#=> 191
```

Records that have non-nil comment id

```rb
# The "non-nil" group
User.where.not(comment_id: nil).count
#=> 1318
```

## matching id | the rest

Records that has comment_id 839

```rb
User.where(comment_id: [839]).count
#=> 8
```

Records that does not have comment_id 839

```rb
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.

```rb
# 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

```rb
# 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

```rb
User.where.not(comment_id: [839]).where.not(comment_id: nil).count
#=> 1310
```

Records that have nil comment id

```rb
User.where(comment_id: nil).count
#=> 191
```

## Resources

- [ActiveRecord's where.not and nil | thoughtbot](https://robots.thoughtbot.com/activerecord-s-where-not-and-nil)