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?
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
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])
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.
精彩评论