On Thu, 2006-07-13 at 10:48 -0400, Jason Murray wrote: > I'm reposting this because I was told my last post got lost in an existing > thread. Sorry about that. > > I figured out my syncdb question. It turns out that as long as the model is > defined correctly things go well. And the output from syncdb will help you > with any trivial troubles you might have with your model. Nice! > > Another question for you all. > > I want to duplicate the queries that the existing app does. The site > maintains the results, standings, and stats for a baseball league. > It has queries like: > > select number, name, win, loss, tie, streak, streak_type, > win/(win+loss+tie)*1.000 wpct, win*2+tie points, win+loss+tie GP, > gamesinseason.games - (win+loss+tie) GL from stand_overall, gamesinseason, > team where team.year = %s && gamesinseason.year = team.year && > stand_overall.team = team.ID order by points desc, win desc, tie desc, loss > > and > > select First_name, Last_name, sum(ab) ab, sum(runs) r, sum(1b) s, s > um(2b) d, sum(3b) t, sum(hr) hr, sum(hbp) hbp, sum(bb) bb, sum(ko) ko, > sum(1b+2b+3b+hr) hits, (sum(1b)+sum(2b)+sum(3b)+sum(hr))/sum(ab)*1.0000 avg, > sum(bb)/(sum(bb)+sum(ab))*1.0000 bb, > (sum(1b)+sum(2b)+sum(3b)+sum(hr)+sum(bb)+sum(hbp))/(sum(ab)+sum(bb)+sum(hbp))*1.0000 > ob, (sum(1b)+2*sum(2b)+3*sum(3b)+4*sum(hr))/sum(ab)*1.0000 as slug, > sum(runs)/(sum(1b)+sum(2b)+sum(3b)+sum(hr)+sum(bb)+sum(hbp))*1 > .0000 score, sum(ko)/(sum(ab)+sum(bb)+sum(hbp))*1.0000 kperapp from player, > offensive_stats, game where player.ID = offensive_stats.player && game.ID = > offensive_stats.game && player.First_name = %s && player.Last_name = %s > group by First_name, Last_name > > My question revolves around how I should get similar results in django. From > what I can tell I should be subclassing a Manager so I can return my own > QuerySets. I don't think the writing custom SQL with connection.cursor() is > the way to go.
If you are wanting to use aggregates in the select clauses, then you need to write custom SQL to some extent. The extra() method on QuerySets will help you with some of this, since you can inject extra things into the select clause that way (see the DB API documentation examples). Unfortunately, the "group by" clause sinks you. You cannot do grouping with query sets (at the moment, at least). So you are back to writing custom SQL for those. Regards, Malcolm --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~----------~----~----~----~------~----~------~--~---
