Query that joins child model results item erroneously shown multiple times

Question

I have the following models, each a related child of the previous one (I excluded other model methods and declarations for brevity):

class Course < ActiveRecord::Base 
  has_many :questions

  scope :most_answered, joins(:questions).order('questions.answers_count DESC') #this is the query causing issues  
end 

class Question < ActiveRecord::Base 
  belongs_to :course, :counter_cache => true
  has_many: :answers

end 

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

Right now I only have one Course populated (so when I run in console Course.all.count, I get 1). The first Course currently has three questions populated, but when I run Course.most_answered.count (most_answered is my scope method written in Course as seen above), I get 3 as the result in console, which is incorrect. I have tried various iterations of the query, as well as consulting the Rails guide on queries, but can't seem to figure out what Im doing wrong. Thanks in advance.

Answer 1

From what I can gather, your most_answered scope is attempting to order by the sum of questions.answer_count.

As it is there is no sum, and since there are three answers for the first course, your join on to that table will produce three results.

What you will need to do is something like the following:

scope :most_answered, joins(:questions).order('questions.answers_count DESC')
  .select("courses.id, courses.name, ..., SUM(questions.answers_count) as answers_count")
  .group("courses.id, courses.name, ...")
  .order("answers_count DESC")

You'll need to explicitely specify the courses fields you want to select so that you can use them in the group by clause.

Edit:

Both places where I mention courses.id, courses.name, ... (in the select and the group), you'll need to replace this with the actual columns you want to select. Since this is a scope it would be best to select all fields in the courses table, but you will need to specify them individually.