根据关联模型的总和对玩家进行排序 [英] Order Players on the SUM of their association model

问题描述

我有一个包含 6500 个 players 的数据库，每个玩家平均有 15 个游戏 results.

I have a database with 6500 players and each player has an average of 15 game results.

用例

我想生成一个玩家列表，按照他们的奖品钱的sum排序(结果表中的一个字段).我更喜欢它在某种范围内，所以我还可以过滤玩家所在国家/地区的列表等.

I want to generate a list of players, ordered by the sum of their prize money (a field in the results table). I prefer this to be in some sort of scope, so I can also filter the list on the player's country, etc.

性能

我看到一些帖子提到了 cache_counter 字段以提高性能.就我而言，我有数千条结果记录 (75.000+)，所以我不希望每次有人访问生成的列表时都进行计算.

I have seen posts that mention a cache_counter field for performance. In my case I have thousands of result records (75.000+) so I don't want the calculations being done every time someone visits the generated listings.

问题

解决这个问题的最佳模式是什么?以及如何实现它?

What is the best pattern to solve this? And how do I implement it?

模型

class Player < ActiveRecord::Base
  has_many :results
end

class Result < ActiveRecord::Base
  belongs_to :player
end

架构

  create_table "players", :force => true do |t|
    t.string   "name"
    t.string   "nationality"
  end

  create_table "results", :force => true do |t|
    t.integer  "player_id"
    t.date     "event_date"
    t.integer  "place"
    t.integer  "prize"
  end

更新

我想要完成的是达到我可以使用的程度:

What I am trying to accomplish is getting to a point where I can use:

@players = Player.order_by_prize

和

@players = Player.filter_by_country('USA').order_by_prize('desc')

推荐答案

你应该可以使用这样的东西:

You should be able to use something like this:

class Player
 scope :order_by_prize, joins(:results).select('name, sum(results.prize) as total_prize').order('total_prize desc')  

参考 rails api - 活动记录查询了解详情.

Refer rails api - active record querying for details.

这篇关于根据关联模型的总和对玩家进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！