class Game < AR::Base
belongs_to :team
has_many :events
end
etc..
When I list the team.games I want to be able to aggregate some of the
data (games won, lost etc). I could do this using scopes, but there are
so many different calculations it would result in a lot of queries.
As I am already fetching all the team games in one DB call, should I be
doing the calculations using just plain ruby on the fly, or even on the
client side using JSON/JS? Or is it better in fact to push everything
through SQL.
I'm not sure of the best plan of attack to do these calculations given
that they could well involve pulling data from the children of each game
as well (e.g events).
Many thanks in advance for any insight. I am pulling my hair out here!
class Game < AR::Base
belongs_to :team
has_many :events
end
etc..
When I list the team.games I want to be able to aggregate some of the
data (games won, lost etc). I could do this using scopes, but there are
so many different calculations it would result in a lot of queries.
As I am already fetching all the team games in one DB call, should I be
doing the calculations using just plain ruby on the fly, or even on the
client side using JSON/JS? Or is it better in fact to push everything
through SQL.
Use SQL as it was meant to be used! The DB can do the aggregate
calculations faster than Rails could. You can even have the aggregates
returned in the same query with everything else (though I'm not sure I'd
advise that).
ActiveRecord::Calculations provides a nice interface to SQL aggregate
functions (at least in Rails 2 -- Arel may take care of this in Rails
3).
I'm not sure of the best plan of attack to do these calculations given
that they could well involve pulling data from the children of each game
as well (e.g events).
What are the calculations that you currently need?
Many thanks in advance for any insight. I am pulling my hair out here!
Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?
I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.
Things I need so far as is results like:
Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average
Yes? And how are these calculated?
The list goes on and on and into nested models in the game:
cameron, maybe u can throw in a kinda of a team-statistics table which holds all the aggregated values? and update / insert them once u enter the results of a game…
not always 3rd-DB-Form, but often preferrable.
regards tom
Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?
I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.
Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.
Things I need so far as is results like:
Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average
Yes? And how are these calculated?
So far the win/loss is calculated from the field of 'home_score' and
'away_score'
I could easily add an outcome integer column (0,1,2) to get an index on
it.
so it could be like
class Game < AR::Base
scope :win, where(:outcome => 0)
@team.games.win
end
Not sure if I could chain a sum/average onto that. Will have to look
into it.
cameron, maybe u can throw in a kinda of a team-statistics table which
holds
all the aggregated values? and update / insert them once u enter the
results
of a game...
not always 3rd-DB-Form, but often preferrable.
regards tom
The problem is that the team stats will be calculated based on different
date ranges and therefore I can't really cache a specific set. The
individual game stats might be more feasible.
Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?
I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.
Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.
Any good SQL reference will show you how to do this. It will probably
be somewhat less efficient if you have a lot of rows, because it has to
put the aggregate value in each returned record, whereas if you did it
in a separate query, it wouldn't have to return all that redundant data.
Things I need so far as is results like:
Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average
Yes? And how are these calculated?
So far the win/loss is calculated from the field of 'home_score' and
'away_score'
I could easily add an outcome integer column (0,1,2) to get an index on
it.
That's a performance hack, and you probably don't need it: it should be
very fast to have the DB calculate the difference of the two values on
the fly.
so it could be like
class Game < AR::Base
scope :win, where(:outcome => 0)
@team.games.win
end
Not sure if I could chain a sum/average onto that. Will have to look
into it.
I would advise against storing calculated values in the database at
the start. It adds complexity to the code and subtle bugs can be
introduced that may not be seen immediately. If performance becomes
an issue at some point in the future then that is the time to do it.
Though even then it may not be the best way to improve performance.
Bottlenecks in apps are rarely at the points that were imagined at the
design stage.
Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?
I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.
Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.
Any good SQL reference will show you how to do this. It will probably
be somewhat less efficient if you have a lot of rows, because it has to
put the aggregate value in each returned record, whereas if you did it
in a separate query, it wouldn't have to return all that redundant data.
I think this might come in handy in this case. Either way I am going to
go dive into some code.
Many thanks for your help. This has really helped my thinking on the
issue.