Active Record: ordering finds and also returning nulls

Question

I want to find a ordered list of runners by their results.

models

class Race < ActiveRecord::Base
 has_many :runners, :dependent => :destroy
end

class Runner < ActiveRecord::Base
  belongs_to :race
  has_one :result, :dependent => :destroy
end

class Result < ActiveRecord::Base
  belongs_to :runner
end

trying to use something like this

ordered_runners = race.runners.all(:include => :result, :order => 'results.position ASC')

position is their finishing position ie [1,2,3,4....]

but if a result is missing (nil) then the runner is not included. Is there a way to do this and return all runners?

cheers

Answer 1

This should return the runners with a null result:

race.runners.all(:include => :result, :conditions => "results IS NULL",  :order => 'results.position ASC')

Answer 2

Runners without Results are not included because :include only brings in the data minimizing the number of queries to avoid N+1 hits to the db. You want to do an outer :join to include all runners no matter if they have a result or not.

ordered_runners = race.runners.all(:joins => "left outer join results on runners.id = results.runner_id", :order => 'results.position ASC')

Check this code based on your migration column/table names and your database.