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?

Alex Morozov · Accepted Answer · 2016-01-15 19:27:20Z

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)

edited Jan 15, 2016 at 19:27

answered Jan 15, 2016 at 18:24

Alex Morozov

6,01327 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3449833 Over a year ago

This looks pretty cool, I'll give it a shot. Where would the QuerySet classes usually be stored, in models.py? Thank!

Alex Morozov Over a year ago

Yes, it's better to keep managers close to models, so that you don't have to navigate away.

user3449833 Over a year ago

ERROR: 'TeamcategoryQuerySet' object has no attribute 'objects'

Alex Morozov Over a year ago

Yes, that objects trail has slipped through ). Try the updated answer.

user3449833 Over a year ago

Fixed a few typos and changed the "." to [''] in the yield. So close. Issue now is that team_name is not something that's getting returned by winning_team_details.

|

Collectives™ on Stack Overflow

Django database optimization

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related