Geek asks and answers

Order players on SUM their association model

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

Use case

I want to create a list of players sorted by the amount of their prize money (field in the results table). I prefer it to be in some area, so I can also filter the list in the player’s country, etc.

Performance

I have seen posts mentioning a field cache_counterfor performance. In my case , I have thousands of result records (75,000 +) , so I don’t want the calculations to be done every time someone visits the created lists.

Question

What is the best sample to solve this problem? And how to 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'm trying to accomplish is getting to the point where I can use:

@players = Player.order_by_prize

and

@players = Player.filter_by_country('USA').order_by_prize('desc')
+5
source share
1 answer

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

For more details see the rails of api - active write requests .

+10
source

More articles:


All Articles