Django数据库优化

Question

型号：

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)

我想用以下内容创建一个列表(如果可能的话，也可以创建一个查询集)，每个惟一的team_category只需要一个列表项(对于过滤的事件)：

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

"top“指的是该类别的Counts=True结果的数量。

这是我到目前为止的代码，这是一个可怕的火车残骸。

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))

这是可行的，但除了混乱之外，for循环的每一个循环都会命中数据库4次，尽管我认为它只需要我之前在raw_team_results中获得的数据。

显然我是新来的，有什么建议可以让这件事变得更好吗？

Answer 1

模特们对我来说挺好的。为了使代码更加枯燥，尝试将您的重型机器交给模型经理：

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)