Serialize field is 'nil' but fails a 'IS NULL' query

Question

I have a serialized field in my Project model called rankings.

serialize :rankings

Here are some queries with results:

@projects = Project.where("rankings IS NULL")         -> 0 results
@projects = Project.where("rankings = ?", "")         -> 0 results
@projects = Project.where("rankings = ?", {})         -> 0 results    
@projects = Project.where("rankings = ?", "{}")       -> 0 results
@projects = Project.where("rankings = ?", {}.to_yaml) -> 0 results

Project.find(275).rankings -> nil

This is for a table with 100s of nils (of which #275 is one). What's going on?

Answer 1

Here's the answer.

To query for nil with a serialized field in Rails, you have to do:

@projects = Project.where("rankings = ?", nil.to_yaml)

Answer 2

According to this page, Rails serializes things with YAML. Playing around with this shows that the results aren't necessarily what you'd expect:

irb(main):007:0> require 'yaml'
=> true
irb(main):008:0> nil.to_yaml
=> "--- \n...\n"
irb(main):009:0> {}.to_yaml
=> "--- {}\n"

I can't say for sure that this is what you're running into, but it seems like a decent place to start. Hope it helps!

PS: I'm going to guess that using the hash form of where will generate the right query:

@projects = Project.where(:rankings => nil)