5

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')
Fred Fickleberry III
  • 2,439
  • 4
  • 34
  • 50

1 Answers1

10

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.

Salil
  • 9,534
  • 9
  • 42
  • 56
  • 4
    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? – Fred Fickleberry III May 10 '12 at 10:54
  • 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. – Fred Fickleberry III May 10 '12 at 10:56
  • 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 :) – Fred Fickleberry III May 10 '12 at 11:02
  • 1
    I am surprised that it needs "group('players.id') clause. Need to find out more about why it does so. Thanks for sharing :) – Salil May 11 '12 at 00:31
  • 3
    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. – brad Dec 30 '12 at 10:39