Darren, This seems like quite a complex problem and I'm not sure whether the Django ORM would be up to it. I've had a lot of trouble when using multiple annotations on Querysets when JOINs are involved as Django does the wrong thing.
Have you considered using a schemaless DB like MongoDB for your data storage rather than a relational model? Your data does look quite relational though, but you might find some of the map/reduce functionality good. Euan On May 28, 5:50 am, darren <backdoc...@gmail.com> wrote: > Needing to get this done, I went with SQL. The sql seems rather > inefficient. But, my data set will be rather small. I posted what I > went with on Pastebin: http://pastebin.com/VmiYNXan(good for 24 > hrs). > > I don't have much data loaded (just one tournament). But, you can see > the resulting table of data here: rankintornadoes.com. > > I would still like to know if this would be the recommended Django > way. Any advice would be welcome. > > Darren > > On Thu, May 27, 2010 at 2:19 PM, darren <backdoc...@gmail.com> wrote: > > I'm looking for ideas on the best way to approach this problem. > > Should I write raw SQL, stay with the ORM or even mix Javascript with > > either ORM or SQL? > > > I want to keep up with player stats for a baseball team. I've been > > reading up on aggregates and annotations. And, I'm getting somewhere > > with it. But, not quite all the way yet. I also wrote some SQL that > > accomplishes most everything I need all at once. I think I could run > > with it, too. > > > To calculate things like "on base percentage" or "batting average", I > > need to mix math both vertically and horizontally in the table. One > > way I thought about handling this is to build an HTML table of data > > using a queryset based on this example in the Django documentation: > > > Book.objects.aggregate(Avg('price'), Max('price'), Min('price')) > > > except, I would need something that summed 3 or 4 different columns. > > > At that point, I could use javascript to Sum() the total of each and > > calculate averages. So, a table would be built like: > > > A B C D E > > F > > G H > > Player Name | Hits | Strike Outs | Walks | Fouled Out | > > Javascript Calcuated Total at Bats | Javascript Calc. Batting AVG | > > Javasctipt On Base % > > Player 1 3 2 5 2 > > A+B+C+D+E > > B/F (B+D)/F > > Player 2 4 2 5 1 > > A+B+C+D+E > > B/F (B+D)/F > > > My models contain 3 tables that join people, tournaments and an "at > > bat". The "AtBat" class is where the statistical data will be > > recorded. The model looks like this. So, I'm saving one result per > > record: > > > 101 class AtBat(models.Model): > > 102 atbat_id = models.AutoField(primary_key=True) > > 103 player = models.ForeignKey(Person, to_field='f_name', > > verbose_name='Player', limit_choices_to={'relationship' : 'Player' }) > > 104 game = models.ForeignKey(Score, to_field='scores_id', > > verbose_name='Game') > > 105 result = models.CharField('Result', choices=(('H', 'Hit'), > > ('BB', 'Walk'), ('K', 'Strike Out'), ('FO', 'Ground or Fly Out'), > > ('Sacrifice', 'Sacrafice')), max_length=10) > > > One of my goals would be to allow users to select certain players, > > date ranges or games to filter the results. I'm not sure how that > > might impact the solution. > > > Any suggestions? -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
