Django Aggregate by QuerySet?

Question

I'm writing a league system and I want to display player rankings in each season sorted by points each player accumulated during that season.

So far I managed to do it with code similar to this:

class Player(models.Model):
    name = models.CharField(max_length=100)


class Season(models.Model):
    name = models.CharField(max_length=100)
    start_date = models.DateField()
    end_date = models.DateField()
    players = models.ManyToManyField(Player)

    def get_player_rank(self, player):
        return player.matchresult_set.filter(season=self).aggregate(points=Sum('points'))['points']

    def get_ranks(self):
        ranks = [(player, self.get_player_rank(player)) for player in self.players.all()]
        ranks.sort(key=lambda tup: tup[1])
        return ranks


class Match(models.Model):
    date = models.DateField()
    players = models.ManyToManyField(Player, through='MatchResult')
    season = models.ForeignKey(Season)


class MatchResult(models.Model):
    player = models.ForeignKey(Player)
    match = models.ForeignKey(Match)
    points = models.IntegerField()

I think the same could be achieved by a much simpler aggregation, but I just can't get the annotate() right.

I tried this but it just summed all points throughout the seasons:

class Season(models.Model):    
    def get_ranks(self):
        return self.players.annotate(points=Sum('matchresult__points')).order_by('-points')

What am I missing? I guess .extra() can be used if it would result in portable code.

Please provide the full model definitions, so we can see all the important relation also tell us what kind of result do you expect from the queryset. Thanks. — mariodev, Feb 03 '14 at 10:59

minder · Accepted Answer · 2014-02-05T10:38:05.190

This returns usable results:

Season.objects.values('name','match__matchresult__player__username').annotate(points=Sum('match__matchresult__points')).distinct()

I also needed to implement SumWithDefault to get rid of NULLs:

from django.db.models.sql.aggregates import Aggregate
from django.db.models import Aggregate as Ag

class SumWithDefaultSQL(Aggregate):
    def __init__(self, col, default=None, **extra):
        super(SumWithDefaultSQL, self).__init__(col, default=default, **extra)
        self.sql_function = 'SUM'
        if default is not None:
            self.sql_template = 'COALESCE(%(function)s(%(field)s), %(default)s)'


class SumWithDefault(Ag):
    name = 'Sum'

    def add_to_query(self, query, alias, col, source, is_summary):
        aggregate = SumWithDefaultSQL(col, source=source, is_summary=is_summary, **self.extra)
        query.aggregates[alias] = aggregate

final query:

Season.objects.values('name','match__matchresult__player__username').annotate(points=SumWithDefault('match__matchresult__points', default=0)).distinct().order_by('-points')

score -2 · Answer 2 · answered Feb 03 '14 at 13:46

-2

Django's ORM does not cover every use case.

You could store the aggregates in a separate model or fall back to raw SQL.

answered Feb 03 '14 at 13:46

Adrien

640
4
11

Or you just analyze the ORM's behavior ;) – minder Feb 05 '14 at 07:33

Django Aggregate by QuerySet?

2 Answers2