Rails根据列的值进行计数 [英] Rails order by count based on values of column

问题描述

Rails 5.1.2

我有两个学生模型 Student 和奖励这样：

I have a two models Student and Award this way:

class Student < ApplicationRecord
  has_many :awards
end

class Award < Application Record
  belongs_to :students

  # Categories
  scope :attendance, -> { where(category: 0) }
  #...(other award categories)
  scope :talent,     -> { where(category: 8) }
  # Ranks
  # Gold
  scope :rank_1, -> { where(rank: 1) }
  # Silver
  scope :rank_2, -> { where(rank: 2) }
  # Bronze
  scope :rank_3, -> { where(rank: 3) }
end

奖项具有以下列：等级和类别。

现在，我想获得给定类别的顶级学生。这样做的标准是，按金奖的次数（等级 1 ）排序，然后按银的次数（等级 2 ）奖励，然后按青铜（排名 3 ）奖励的顺序排序。

Now, I want to get the top student for a given category. The criteria for this is, order by count of "gold" awards (rank 1), then order by count of "silver" (rank 2) awards, and then order by count of "bronze" (rank 3) awards.

因此，如果我要让学生满足类别 0 （由（如上模型中所述的出勤范围），这就是我认为查询应如下的样子：

So, if I were to get the Student who meets the top criteria for category 0 (which is handled by the attendance scope as described in the model above), this is what I thought the query should look like:

Student.joins(:awards).where(awards: { category: 0 }).group('students.id').order('COUNT(awards.rank == 1) DESC', 'COUNT(awards.rank == 2) DESC', 'COUNT(awards.rank == 3) DESC').take

但是，这将返回奖励数量最高的学生，而不管其排名如何。因此，例如，如果我删除了 take ，则订单如下所示：

However, this returns the Student with the highest count of awards, regardless of rank. So for example, if I remove take, the order looks like this:

# |St.ID | Gold  | Slvr. | Brnz. |
----------------------------------
1 |  12  |   4   |   12  |   8   |
----------------------------------
2 |   1  |   9   |   0   |   4   |
----------------------------------
3 |   6  |   9   |   1   |   0   |
----------------------------------
4 |  18  |   5   |   2   |   2   |
----------------------------------
 ...

所以，我要得到的订单是ID 12、1、6、18等。 。，当其ID应该为 6、1、18、12 ...... 。

So, the order I'm getting are IDs 12, 1, 6, 18, ..., when it should be IDs 6, 1, 18, 12, ....

我意识到 order（'COUNT（awards.rank == 1）DESC'，'COUNT（awards.rank == 2）DESC'，'COUNT（awards.rank == 3 ）DESC'）部分仅按奖励总数计算（而不是在 rank 列中具有特定值的奖励计数）。

I realize the order('COUNT(awards.rank == 1) DESC', 'COUNT(awards.rank == 2) DESC', 'COUNT(awards.rank == 3) DESC') part is simply ordering by the count of awards total (rather than count of a awards with a particular value in column rank).

我可以通过为每个奖项类别添加计数器缓存来轻松解决此问题，但这不是一个优雅也不灵活的解决方案。

I can easily solve this by adding a counter cache for each category of awards, but that isn't an elegant nor flexible solution.

在此查询返回成功的结果之后，我将再次搜索数据库以查找所有分数相同（可能有平局）的学生。我不知道在一个查询中执行所有这些操作的方法（也许在获取每个等级的值之后通过子查询）。

As a bonus, after this query returns a successful result, I will search the database again to find all students who have the same score (as there could be ties). I'm not aware of a way to do all this in one query (perhaps by means of subqueries after getting the values for each rank).

推荐答案

我认为您的问题可能是双重平等？

I think your problem might be the double equal??

编辑：这是一种更正确的方法（假设使用MySQL）：

This is a more proper way (Assuming MySQL):

 Student.joins(:awards).where(awards: { category: 0 }).group('students.id').order('COUNT(if(awards.rank = 1)) DESC', 'COUNT(if(awards.rank = 2)) DESC', 'COUNT(if(awards.rank = 3)) DESC').take

首先尝试不使用它的代码，如果这样做不起作用，则可能需要尝试以下操作：

First try your code without it, if that doesn't work, you might want to try this:

SELECT students.id, gold, silver, bronze) FROM students 
JOIN ON 
  (SELECT students.id as id COUNT(awards) as bronze
  FROM students JOIN awards ON students.id = awards.student_id
  WHERE awards.rank = 1
  GROUP BY students.id) q1 q1.id = students.id
JOIN ON 
  (SELECT students.id as id COUNT(awards) as silver
  FROM students JOIN awards ON students.id = awards.student_id
  WHERE awards.rank = 2
  GROUP BY students.id) q2 q2.id = students.id
JOIN ON 
  (SELECT students.id as id COUNT(awards) as gold
  FROM students JOIN awards ON students.id = awards.student_id
  WHERE awards.rank = 3
  GROUP BY students.id) q3 q3.id = students.id
ORDER BY gold, silver, bronze

这篇关于Rails根据列的值进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！