#players = select distinct(player) from actions;
players = repository(:default).adapter.query('select distinct(player) from actions')
@players = []
players.each do |name|
player = {}
player["name"] = name
player["won"] = Action.sum(:amount, :conditions => ["player = '#{name}' and amount != 'NULL' and (type = 'award' or type = 'return')"])
player["lost"] = Action.sum(:amount, :conditions => ["player = '#{name}' and amount != 'NULL' and type != 'award' and type != 'return'"])
@players << player
end
Refactorings
No refactoring yet !
Adam
February 11, 2009, February 11, 2009 01:32, permalink
1 rating.
Login to rate!
class Action < ActiveRecord::Base
class << self
def players(options = {})
connection.select_all(construct_player_sql(options))
end
private
def construct_player_sql(options = {})
wins = construct_player_sum_sql(:amount, :conditions => "type = 'award' OR type = 'return'")
loss = construct_player_sum_sql(:amount, :conditions => "type != 'award' AND type != 'return'")
with_scope(:find => options) do
construct_finder_sql(
:select => "DISTINCT(player) AS name, (#{wins}) AS won, (#{loss}) AS lost",
:from => "#{quoted_table_name} t1",
:conditions => "amount != 'NULL'"
)
end
end
def construct_player_sum_sql(column_name, options = {})
with_scope(:find => { :conditions => 'player = t1.player'}) do
construct_calculation_sql(:sum, column_name, options)
end
end
end
end
@players = Action.players
pypiyush.myopenid.com
February 21, 2009, February 21, 2009 05:26, permalink
No rating.
Login to rate!
@players = Action.player_statistics # Invoke it from controller
class Action < ActiveRecord::Base
named_scope :wins, :conditions => ["type = 'award' OR type = 'return'"]
named_scope :loss, :conditions => ["type != 'award' AND type != 'return'"]
def self.player_statistics
players = find(:all, :distinct => 'players')
unless players.blank?
@players = []
player_statistics = {}
players.each do |p|
player_statistics['name'] = p.name
player_statistics['wins'] = wins.sum(:amount)
player_statistics['loss'] = loss.sum(:amount)
end
@players << player_statistics
end
@players.nil? ? (return nil) : @players
end
end
I have an actions table that has an amounts column that I want to sum for each player listed in that table.
This looks like it will get out of hand fast considering that each user of the website has his own set of action rows
and they can contain hundreds if not thousands of players each. Obviously this will be paginated to 10 or so at a time but
I was curious as to whether or not there is more optimization that could go on here.