-1

To get started with Django, I'm using it to build a small site for my softball team where we can list the stats of each player. For the model I've defined 2 tables - Player and Statline where the player in Statline is the foreign key.

class Player(models.Model):
    name = models.CharField(max_length=32)
    gender = models.CharField(max_length=1, choices=GENDER_CHOICES)
    bats = models.CharField(max_length=1, choices=HANDED_CHOICES)
    throws = models.CharField(max_length=1, choices=HANDED_CHOICES)
    position = models.CharField(max_length=2, choices=POSITION_CHOICES)
    pitches = models.CharField(max_length=1, choices=PITCHES_CHOICES)
    hometown = models.CharField(max_length=32)

    def __unicode__(self):
        return self.name

class StatLine(models.Model):
    season_name = models.CharField(max_length=12, choices=SEASON_CHOICES)
    player = models.ForeignKey(Player)
    at_bats = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    singles = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    doubles = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    triples = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    homeruns = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    runs = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    rbis = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)
    rboe = models.IntegerField(max_length=2, choices=NUMBER_CHOICES, default=0)

What I'm looking to do is do a query where I sum up all of the stats for each player and group it by player. That way I have a running tally of all of our stats for each season. In SQL when I built the site in PHP, it was:

FROM Stats
INNER JOIN Players ON Players.player_id = Stats.player_id 
WHERE season_name = 'Spring 2012'
GROUP BY Players.player_name

I'm confused on how to use Django to perform the same query. Any help would be much appreciated!

rails_newbie
  • 53
  • 1
  • 10

1 Answers1

1

Django's documentation about aggregation should be sufficient.

I don't know why you're grouping by player_name because you don't state which fields you want to have selected and if there should be an aggregation somewhere. Is it possible to have several StatLine objects for a single Player?

In case you want all homeruns of a particular season to be summed up for each player, you can use this snippet:

from django.db.models import Sum
Player.objects.filter(
      statline__season_name="Spring 2012"
   ).annotate(
      sum_homeruns=Sum(statline__homeruns),
      sum_abats=Sum(statline__abats),
      sum_singles=Sum(statline__singl‌​es),
      […]
)

As stated in the docs, the order of filter() and annotate() is of importance.

jnns
  • 5,148
  • 4
  • 47
  • 74
  • OK that helps a ton. I'll give it a shot. The purpose is to have the stats as they would normally appear on the back of a baseball card: Player Name, Hits, Singles, Doubles, etc. - for a given season. – rails_newbie May 11 '12 at 15:13
  • OK I'm still stuck. I've read through all the documentation but I'm still having an issue creating the query I need. As per the first question - yes all statlines can be thought of stats for an individual game. so for the table I want to produce, it needs to add up all of the statlines for a given player. So the query should be filtered by player and then have the sum of each of the other statline columns. Can I chain the annontatations e.g. .annotate(sum_abats=Sum(statline__abats).annontate(sum_singles=Sum(statline__singles)...? – rails_newbie May 18 '12 at 11:47
  • Yes, you can add multiple annotations. See my modified example above. – jnns May 18 '12 at 11:53