CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
sscirrus
sscirrus

Reputation: 56719

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

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?

Upvotes: 4

Views: 927

Answers (3)

shedd
shedd

Reputation: 4218

In Rails 4 with Postgres, Rails seems to populate an actually string of "null" for serialized columns that are nil.

That is, Rails makes the field appear to be nil but you have to specifically query for "null" to get the right result.

So under Rails 4, the accepted answer no longer works, but this works for me:

@projects = Project.where("rankings = 'null'")

Upvotes: 2

sscirrus
sscirrus

Reputation: 56719

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)

Upvotes: 3

Xavier Holt
Xavier Holt

Reputation: 14619

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)

Upvotes: 2

Related Questions