Django查询优化计算相关字段的相关字段

Question

我正在为我的兄弟会编写一个 Django 应用程序来对rushee进行投票，我正在尝试优化我的一个查询，该查询可以计算选票并打印出计数以及来自应用程序的信息。 Django 调试工具栏告诉我有很多重复的查询

(为了清晰起见，下面的代码已被截断和编辑)

模型.py

 votechoices = ((1, "Yes"),(2, "No"),(3, "Abstain"))

 class Vote(models.Model):
      brother = models.ForeignKey(Brother)
      rushee = models.ForeignKey(Rushee)
      choice = models.IntegerField(choices=votechoices, default=3)

 class Rushee(models.Model):
      first_name = models.CharField(max_length=40)
      last_name = models.CharField(max_length=40)
      #ETC, ETC

 class Application(models.Model):
      rushee = models.ForeignKey(Rushee)
      address = models.CharField(max_length=200)
      cellphone = models.CharField(max_length=30)
      #ETC, ETC

View .py

def getvotecount(request):

     # get all the applications ( we only vote on people who have an application)
     applicationobjs = Application.objects.select_related('rushee').all()

     # iterate through the applications and count the votes
     for app in applicationobjs.iterator():

     #>>>> This Query below is a seperate query everytime! So that means that If we have 200 rushees there are 200 queries!
          counts = Vote.objects.filter(rushee=app.rushee).values('choice').annotate(count=Count('choice'))

          votesfor = sum([x['count'] for x in counts if x['choice'] == 1])
          votesagainst = sum([x['count'] for x in counts if x['choice'] == 2])

          result = [app.rushee.first_name + ' ' + app.rushee.last_name,
                    app.address, app.cellphone,
                    str(votesfor), str(votesagainst),]

    # Uninteresting stuff below that will group together and return the results

我正在尝试优化标有 (>>>>) 的 View 中的查询，以便我可以返回每个rushee 的票数，而无需每次都运行单独的查询!

附加信息:sqlite 后端，rushee 的数量比应用程序的数量多得多，我们只对有应用程序的rushee 进行投票

Answer 1

您可以使用conditional expressions在一个查询中完成这一切:

from django.db.models import Case, IntegerField, Sum, When

rushees = Rushee.objects.annotate(
    votes_for=Sum(
        Case(
            When(vote=1, then=1),
            default=0,
            output_field=IntegerField(),
        )
    ),
    votes_against=Sum(
        Case(
            When(vote=2, then=1),
            default=0,
            output_field=IntegerField(),
        )
    )
)

结果查询集中的 rushees 将具有 votes_for 和 votes_against 属性以及每个属性的计数。这假设没有记录针对没有申请的 rushees 的投票 - 但如果有，那么您可以轻松过滤掉这些投票。