注释总和会导致无而不是零 [英] Annotating a Sum results in None rather than zero

问题描述

我正在制作类似于您现在所在网页的质量检查网站。我试图通过他们的分数来排序答案，但没有投票的答案将他们的得分设置为无而不是0.这导致答案没有投票在页面底部负面排名的答案。

这是我的模型：

  from django.contrib.auth.models import User 
 
答案（models.Model）：
 //某些字段在这里
 pass 
 
 VOTE_CHOICES =（（-1，Down），（1，Up））
 
投票（models.Model）：
 user = models.ForeignKey ）
 answer = models.ForeignKey（Answer）
 type = models.IntegerField（choices = VOTE_CHOICES）
 
 class Meta：
 unique_together =（user，answer）
  

这是我的查询：

 answers = Answer.objects.filter（< something here>）
 .annotate（score = Sum（'vote__type'））
 .order_by（' -  score'）
  

编辑：要清楚，我想在查询中执行此操作。我知道我可以把它变成一个列表，然后把它排在我的python代码中，但是如果可能的话我想避免这种情况。

解决方案>

您可以使用 Coalesce function from django.db.models.functions like：

  answers = Answer.objects.filter（< something here>）
 .annotate（score = Coalesce（Sum（'vote__type'），0））
 .order_by（' -  score '）
  

I'm making a QA site that is similar to the page you're on right now. I'm attempting to order answers by their score, but answers which have no votes are having their score set to None rather than 0. This results in answers with no votes being at the bottom of the page below negatively ranked answers. How can I make the annotated score be zero when there are no votes for an answer?

Here's my model:

from django.contrib.auth.models import User

Answer(models.Model):
    //some fields here
    pass

VOTE_CHOICES = ((-1, Down), (1, Up))

Vote(models.Model):
    user = models.ForeignKey(User)
    answer = models.ForeignKey(Answer)
    type = models.IntegerField(choices = VOTE_CHOICES)

    class Meta:
        unique_together = (user, answer)

And here's my query:

answers = Answer.objects.filter(<something here>)
                        .annotate(score=Sum('vote__type'))
                        .order_by('-score')

edit: And to be clear, I'd like to do this in the query. I know I could turn it into a list and then sort it in my python code, but I'd like to avoid that if possible.

解决方案

You can use the Coalesce function from django.db.models.functions like:

answers = Answer.objects.filter(<something here>)
                        .annotate(score=Coalesce(Sum('vote__type'), 0))
                        .order_by('-score')

这篇关于注释总和会导致无而不是零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！