Rails根据列的值进行计数

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