james james - 5 months ago 19
Ruby Question

Rails ActiveRecord find children where attribute is NOT a given value

Given that a

Parent
has many
Child
s with
status_id
attribute, I want to find all the children that do NOT have a
status_id:1
. In other words, the
status_id
could be
nil
or a different value. But I'm seeing some interesting behavior:

Parent.find(1).childs.where(status_id:nil)
=> #<ActiveRecord::AssociationRelation [#<Child id: 1, status_id: nil ...>]

Parent.find(1).childs.where.not(status_id:1)
=> #<ActiveRecord::AssociationRelation []>

Answer

This post suggests that SQL treats NULL, the absence of something, as something that can't be equal to something that exists.

10 things in MySQL that won’t work as expected has an example which requires using "IS" for null check, something like below.

Parent.find(1).childs.where("status_id != ? or status_id is null", 1)