Rails - using :include to find objects based on their child's attributes

Question

I have a sentence and correction model

class Sentence < ActiveRecord::Base
    has_one :correction

class Correction < ActiveRecord::Base
   belongs_to :sentence

and I'm trying find all sentences which don't have a correction. To do this I'm simply looking for corrections which don't exist i.e. whose id = nil. But it is failing and i can't figure out why

 Sentence.find :all, :include => :correction, :conditions => {:correction => {:id => nil}}

from (irb):4>> Sentence.find :all, :include => :correction, :conditions => {:correction => {:id => nil}}

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'correction.sentence_id' in 'where clause': SELECT * FROM sentences WHERE (correction.sentence_id IS NULL)

Perhaps its the syntax or maybe just the overall approach. Can anyone help?

Answer 1

You can use this:

Sentence.all(:include => :correction, 
  :conditions => "corrections.sentence_id IS NULL")

Or this:

Sentence.all(
  :joins => "LEFT OUTER JOIN corrections AS A ON A.sentence_id = sentences.id`, 
  :conditions => "A.sentence_id IS NULL")

Second approach is efficient than the first even though it is long and ugly.

Finally I prefer this one:

Sentence.all(:conditions => "NOT EXISTS (SELECT * 
                                         FROM   corrections A
                                         WHERE  A.sentence_id = sentences.id")