连接子模型结果项的查询多次错误显示 [英] Query that joins child model results item erroneously shown multiple times

问题描述

我有以下模型，每个模型都是前一个模型的相关子代(为简洁起见，我排除了其他模型方法和声明):

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

现在我只填充了一个 Course(所以当我在控制台 Course.all.count 中运行时，我得到 1).第一个 Course 目前填充了三个 questions，但是当我运行 Course.most_answered.count(most_answered 是我的如上所示，在 Course 中编写的范围方法)，我在控制台中得到 3 作为结果，这是不正确的.我尝试了查询的各种迭代，并咨询了 Rails 查询指南，但可以似乎不明白我做错了什么.提前致谢.

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.

推荐答案

据我所知，您的 most_answered 范围正在尝试按 questions.answer_count 的总和排序.

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

实际上没有sum，并且由于第一门课程有三个答案，因此您加入该表将产生三个结果.

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.

您需要做的事情如下:

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")

您需要明确指定要选择的课程字段，以便您可以在 group by 子句中使用它们.

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

我提到 courses.id, course.name, ... 的两个地方(在选择和组中)，您需要将其替换为您想要选择的实际列.由于这是一个范围，因此最好选择课程表中的所有字段，但您需要单独指定它们.

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.

这篇关于连接子模型结果项的查询多次错误显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！