Order Players on the SUM of their association model

Question

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

Use case

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.

Performance

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.

Question

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

Models

class Player < ActiveRecord::Base
  has_many :results
end

class Result < ActiveRecord::Base
  belongs_to :player
end

Schemas

  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

Update

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

@players = Player.order_by_prize

and

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

Bozcaada

Answer 1

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

Refer rails api - active record querying for details.

Unwept

I had to make some adjustments to your suggestion, and now it accurately orders them by sum of their prizes: scope :order_by_prize, joins(:results).select('players.id, players.name, sum(results.prize) as total_prize').group('players.id').order('total_prize desc'). However, the results only show player.id and player.name, not the total_prize result? – Bozcaada

Btw, I made those adjustments because I am using PostgreSQL and in the console it told me to include a group by clause after running your code. – Bozcaada

Nevermind! In the console the total_prize doesn't show on screen in the resulting output, but the attribute is correctly set and available. Thank you so much :) – Bozcaada

I am surprised that it needs "group('players.id') clause. Need to find out more about why it does so. Thanks for sharing :) – Unwept

The group('players.id') clause creates individual sums for all of the players. Without it, all of the prizes for all of the players are added together. – Piste