Django Query Optimization计算相关字段的相关字段

debugcn 发表于 Dev

格兰特·鲍威尔

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

（为清楚起见，下面的代码已被截断和编辑）

models.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

views.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

我正在尝试在（>>>>）标记的视图中优化查询，以使我可以返回每个rushee的票数，而不必每次都运行单独的查询！

其他信息：sqlite后端，rushees的数量远远超过应用程序的数量，我们仅对确实有应用程序的rushees进行投票

日光浴

您可以使用条件表达式在一个查询中完成全部操作：

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没有申请者的投票记录-但是如果有投票，则可以轻松地将其过滤掉。