Hello I have few typical models: class Team < AR::Base has_many :games end 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! Cameron -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Cameron Caine wrote in post #965772:> Hello > > I have few typical models: > > class Team < AR::Base > has_many :games > end > > 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! > > CameronBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thanks this certainly helps. Any reason in particular why you don''t recommend getting aggregates in the same query? Things I need so far as is results like: Win Count Loss Count Draw Count Win % Home Wins % Away Wins % Scoring Average The list goes on and on and into nested models in the game: Thanks Marnen Laibow-Koser wrote in post #965775:> Cameron Caine wrote in post #965772: >> Hello >> >> I have few typical models: >> >> class Team < AR::Base >> has_many :games >> end >> >> 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! >> >> Cameron > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org-- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Please avoid top-posting. Cameron Caine wrote in post #965778:> 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 AverageYes? And how are these calculated?> > The list goes on and on and into nested models in the game:Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 On Thu, Dec 2, 2010 at 3:36 PM, Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:> Please avoid top-posting. > > Cameron Caine wrote in post #965778: > > 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: > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > Posted via http://www.ruby-forum.com/. > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser wrote in post #965782:> Please avoid top-posting. > > Cameron Caine wrote in post #965778: >> 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.> >> >> The list goes on and on and into nested models in the game: > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org-- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Tom Tom wrote in post #965788:> 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 tomThe 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 anyway C -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Cameron Caine wrote in post #965790:> Marnen Laibow-Koser wrote in post #965782: >> Please avoid top-posting. >> >> Cameron Caine wrote in post #965778: >>> 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.Chain it on? What do you mean? Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 2 December 2010 20:48, Cameron Caine <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Tom Tom wrote in post #965788: >> 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.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. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser wrote in post #965796:> Cameron Caine wrote in post #965790: >> Marnen Laibow-Koser wrote in post #965782: >>> Please avoid top-posting. >>> >>> Cameron Caine wrote in post #965778: >>>> 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. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.