Django database optimization

Question

Models:

class Teamresult(models.Model):
    event = models.ForeignKey(Event)
    team_category = models.ForeignKey(Teamcategory)
    team_place= models.IntegerField()
    team_name = models.CharField(max_length=64)
    athlete_team_place = models.IntegerField()
    athlete_time = models.DurationField()
    athlete_name = models.CharField(max_length=64)
    counts = models.BooleanField()
    class Meta:
        unique_together = ('event', 'team_category', 'team_name', 'athlete_team_place')
        ordering = ('event', 'team_category', 'team_place', 'athlete_team_place')

class Teamcategory(models.Model):
    name = models.CharField(max_length=50, unique=True)
    slug = models.SlugField(unique=True)

I want to make a list (or queryset if possible) with the following, just one list item for each unique team_category (for the filtered event):

[(<first category>, <top>, <winning team>, <total time>, <average time>), (<second category>, ...), ...]

"top" refers to the the number of Counts=True results for that category.

This is the code I have so far, which is an awful trainwreck.

event_id = 1
raw_team_results = Teamresult.objects.filter(event_id=event_id, counts=True).order_by('team_place', 'athlete_team_place')
present_team_categories = Teamresult.objects.filter(event_id=event.id).values_list('team_category__name', flat=True)
present_team_categories = set(sorted(present_team_categories))
team_categories = Teamcategory.objects.filter(name__in=present_team_categories)
team_results = []
for team_category in team_categories:
    winning_team = raw_team_results.get(team_category=team_category, team_place=1, athlete_team_place=1).team_name
    top = raw_team_results.filter(team_category=team_category, team_name=winning_team).filter(counts=True).aggregate(Count('athlete_time'))['athlete_time__count']
    total_time = raw_team_results.filter(team_category=team_category, team_name=winning_team).filter(counts=True).aggregate(Sum('athlete_time'))['athlete_time__sum']
    avg_microseconds = raw_team_results.filter(team_name=winning_team).filter(counts=True).aggregate(Avg('athlete_time'))['athlete_time__avg']
    raw_avg_time = timedelta(microseconds = avg_microseconds)
    avg_time = raw_avg_time - timedelta(microseconds=raw_avg_time.microseconds)
    team_results.append((team_category, top, winning_team, total_time, avg_time))

This works, but in addition to being a mess, each cycle of the for loop hits the database 4 times, even though I would have thought it only needs data I already got earlier with raw_team_results.

Clearly I'm new at this, any suggestions to make this better?

Answer 1

Models seem pretty fine to me. To make the code more DRY try putting your heavy machinery to the models' managers:

class TeamcategoryQuerySet(models.QuerySet):
    def of_results(self, results):
        return self.filter(pk__in=results.values('team_category').distinct())


class TeamresultQuerySet(models.QuerySet):
    def winning_team_details(self, categories):
        return (self
                .filter(
                    team_category=category, team_place=1,
                    athlete_team_place=1, counts=True)
                .annotate(
                    top=Count('athlete_time'),
                    total=Sum('athlete_time'),
                    avg=Avg('athlete_time'),
                )[0]

    def of_event(self, event)
        results = self.filter(event=event, counts=True)
        for team_category in Teamcategory.objects.of_results(results):
            winner = results.winning_team_details(category)
            yield (category, winner['top'], winner['team_name'],
                   winner['total'], winner['avg'])


class Teamcategory(models.Model):
    # fields
    objects = TeamcategoryQuerySet.as_manager()


class Teamresult(models.Model):
    # fields
    objects = TeamresultQuerySet.as_manager()


def get_event_results(event)
    return Teamresult.objects.of_event(event)