Has many relationship help in rails

Question

I have two models questions and answers. and a question has_many answers. How can I find out all the questions which don't have any answer?

Answer 1

class Question < ActiveRecord::Base
  has_many :answers

  def self.unanswered
    find_by_sql("select * from questions where id not in (select DISTINCT question_id from answers) ORDER BY updated_at DESC")
  end
end

class Answer < ActiveRecord::Base
  belongs_to :question
end

In controller

@unanswered= Question.unanswered

FOR PAGINATION

  def self.unanswered
    find_by_sql("select * from questions where id not in (select DISTINCT question_id from answers) ORDER BY updated_at DESC")
  end


@unanswered= Question.unanswered
@unanswered.paginate(:page => params[:page], :per_page => 10)

As this fetch all the recors and then use pagination Try Following

  def self.unanswered(page_no=1, per_page=10)
    find_by_sql("select * from questions where id not in (select DISTINCT question_id from answers) ORDER BY updated_at DESC").paginate(:page => page_no, :per_page => per_page)
  end


@unanswered= Question.unanswered(params[:page], params[:per_page])

Answer 2

I'd suggest adding :counter_cache to Answer model. Then you can do something like this in Question model

class Question < ActiveRecord::Base
  has_many :answers
  named_scope :unanswered, :conditions => {:answers_count => 0}
end

The Answer model would look like this

class Answer < ActiveRecord::Base
  belongs_to :question, :counter_cache => true
end

Answer 3

If you are going to adopt a straight SQL approach as described by Salil rather than a more ActiveRecord approach as suggested by Eimantas then you may want to use the following SQL

SELECT * FROM questions as q 
WHERE NOT EXISTS (SELECT * FROM answers AS a WHERE a.question_id = q.id)

As opposed to

select * from questions 
where id not in (select DISTINCT question_id from answers)

The former query performed 8 times faster for me on a table of 140000 rows with 10% of the rows having no answer. I expect this would vary from engine to engine dependant upon the % of unanswered questions.

Eimantas solution is nice if you create a column called answers_count on your questions table then ActiveRecord will maintain that for you and that should be very fast.