分组后如何在Rails中进行排名

Question

在我使用Postgres的Rails 6应用程序中，我有一个名为UserCategories的表。

| id | user_id | category_id | points| rank |

我想做的是：

1. 按category_id对记录进行分组
2. 按点对每个category_id的记录进行排序(desc(
3. 根据记录在category_id中的顺序更新排名字段

示例(所需等级由每个类别id的点数确定(：

| id | user_id | category_id | points| rank |
| 1  |    1    |    1        |   2   |     |  #  I want rank to be 1
| 2  |    2    |    1        |   1   |     |  #  I want rank to be 2
| 3  |    1    |    2        |   3   |     |  #  I want rank to be 1
| 4  |    2    |    2        |   3   |     |  #  I want rank to be 1

我的模型方法：

def self.calculate_user_category_ranks
@user_categories = UserCategory.select(:id, :points, :user_id, :category_id, :rank).all.order(points: :desc).group_by(&:category_id)
# returns: 
#   {2=>[#<UserCategory:0x000000000de8be00 id: 2, user_id: 1, category_id: 2, points: 3, rank: 0>, #<UserLeague:0x000000000de8bce8 id: 4, user_id: 2, category_id: 2, points: 3, rank: 0>],
1=>[#<UserCategory:0x000000000de8bbf8 id: 1, user_id: 1, category_id: 1, points: 2, rank: 0>, <UserLeague:0x000000000de8bb30 id: 3, user_id: 2, category_id: 1, points: 1, rank: 0>]}
rank = 0
points_counter = 0
@user_categories.each do |id, points|
uc = UserCategory.find(id)

if points != point_counter
rank += 1
point_counter = points
end
uc.rank = rank
uc.save
end
end

在我执行这个代码之后：

| id | user_id | category_id | points| rank |
| 1  |    1    |    1        |   2   |  2  |  #  I want rank to be 1
| 2  |    2    |    1        |   1   |  0  |  #  I want rank to be 2
| 3  |    1    |    2        |   3   |  1  |  #  I want rank to be 1
| 4  |    2    |    2        |   3   |  0  |  #  I want rank to be 1

有人能帮我确定我做错了什么吗？

Answer 1

出于效率的原因，您可能会选择将rank作为数据库列，但数据库规范化的原则表明它是"不良做法"；具有一列，该列的值可以根据表中的其他列进行计算。因此，认识到出于效率的原因，您可能不接受此解决方案，让我建议，对于UserCategory的任何特定实例，您都可以在Ruby中确定其级别：

class UserCategory < ApplicationRecord
scope :in_the_same_category, ->(category_id) { where("category_id = ?", category_id }
def in_my_category
UserCategory.in_the_same_category(category_id)
end
def rank
in_my_category.
sort_by(&:points).
reverse.
map(&:points).
uniq.
index(points) + 1
end
end

Answer 2

从开始

UserCategory.group(:category_id)

然后建立

如果您确实需要限制返回的字段或添加where子句，则

UserCategory.where(some_field: "some_value").select(:id, :points, :user_id, :category_id, :rank).group(:category_id).order(:points :desc)

https://apidock.com/rails/ActiveRecord/QueryMethods/group更多信息