Active Record has_many generates sql with foreign key IS NULL

Question

I don't expect a model with NULL as foreign key to belong to anything!

I have the following rails app, modelling ants and ant hills (inspired by Jozef).

$ rails -v
Rails 3.2.8
$ rails new ant_hill
$ cd ant_hill

Create the ant hill and ant models. An ant can belong to an ant hill and an ant hill can have many ants.

$ rails generate model AntHill name:string
$ rails generate model Ant name:string ant_hill_id:integer
$ vim app/models/ant.rb
$ cat app/models/ant.rb
class Ant < ActiveRecord::Base
  belongs_to :ant_hill
end
$ vim app/models/ant_hill.rb
$ cat app/models/ant_hill.rb
class AntHill < ActiveRecord::Base
  has_many :ants
end
$ rake db:migrate
==  CreateAntHills: migrating =================================================
-- create_table(:ant_hills)
   -> 0.0013s
==  CreateAntHills: migrated (0.0016s) ========================================

==  CreateAnts: migrating =====================================================
-- create_table(:ants)
   -> 0.0035s
==  CreateAnts: migrated (0.0037s) ============================================

Run the following code in a console.

$ rails c
Loading development environment (Rails 3.2.8)

Create a couple of ants, persisted, that don't belong to any ant hill.

1.9.2-p290 :001 > Ant.create! name: "August"
 => #<Ant id: 1, name: "August", ant_hill_id: nil, created_at: "2012-09-27 12:01:06", updated_at: "2012-09-27 12:01:06">
1.9.2-p290 :002 > Ant.create! name: "Bertil"
 => #<Ant id: 2, name: "Bertil", ant_hill_id: nil, created_at: "2012-09-27 12:01:13", updated_at: "2012-09-27 12:01:13">

Now instantiate an ant hill, but don't save it just yet.

1.9.2-p290 :003 > ant_hill = AntHill.new name: "Storkullen"
 => #<AntHill id: nil, name: "Storkullen", created_at: nil, updated_at: nil>

I expect this ant hill to not have any ants and it doesn't.

1.9.2-p290 :004 > ant_hill.ants
 => []

I still expect the ant hill to not have any ants but now it has two.

1.9.2-p290 :005 > ant_hill.ants.count
   (0.1ms)  SELECT COUNT(*) FROM "ants" WHERE "ants"."ant_hill_id" IS NULL
 => 2

Same here, it should never generate a query containing "IS NULL" when dealing with foreign keys. I mean "belongs_to NULL" can't belong to anything, right?

1.9.2-p290 :006 > ant_hill.ants.all
  Ant Load (0.4ms)  SELECT "ants".* FROM "ants" WHERE "ants"."ant_hill_id" IS NULL
 => [#<Ant id: 1, name: "August", ant_hill_id: nil, created_at: "2012-09-27 12:01:06", updated_at: "2012-09-27 12:01:06">, #<Ant id: 2, name: "Bertil", ant_hill_id: nil, created_at: "2012-09-27 12:01:13", updated_at: "2012-09-27 12:01:13">]

After it is persisted it behaves as expected.

1.9.2-p290 :007 > ant_hill.save!
 => true
1.9.2-p290 :008 > ant_hill.ants.count
   (0.4ms)  SELECT COUNT(*) FROM "ants" WHERE "ants"."ant_hill_id" = 1
 => 0
1.9.2-p290 :009 > ant_hill.ants.all
  Ant Load (0.4ms)  SELECT "ants".* FROM "ants" WHERE "ants"."ant_hill_id" = 1
 => []

Any insight? Is this the expected behavior?

Answer 1

While it seems counterintuitive, I think this behavior makes sense given your examples. Take ant_hill.ants.count for example. Count is an ActiveRecord query method that hits the database, and you're essentially asking ActiveRecord to give you all the ants that do not belong to an ant hill. Rails is simply letting you do something you should not be able to do, and not complaining about it. Should this be raising an exception instead? Possibly.

If you really want to know how many ants belong to this ant_hill object, you should be using size. It queries the object when not persisted or when the association is already loaded, and queries the database otherwise.

ant_hill.ants.size

One way you can get around this oddity is to make ant_hill_id a required field by validating its presence.

TL;DR Avoid using ActiveRecord query interface if parent object is not persisted to database.