Annotating a Sum results in None rather than zero

Question

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.

FYI, there is an open [Django ticket](https://code.djangoproject.com/ticket/10929) about default values for `Sum()`. — Gary, Feb 07 '12 at 22:48

score 204 · Answer 1 · edited Oct 10 '20 at 16:54

204

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

edited Oct 10 '20 at 16:54

Dustin Wyatt

4,046
5
31
60

answered Feb 15 '16 at 16:08

nelson orland

2,367
1
12
6

2

Ironically, I guess, this answer is at the bottom for me. – andho Jul 14 '17 at 19:21
9

*IMPORTANT* `coalesce` returns `null` if there are no rows found. Unfortunately I have to find another solution – Eugene Kovalev Dec 04 '18 at 15:05
@EugeneKovalev Coalesce returns a default value if the queried value itself is null. It doesn't evaluate entire rows, right? – Adam Starrh Apr 02 '19 at 07:15
@EugeneKovalev Did you find a solution to `coalesce` returning `null` when there are no rows? – Redgren Grumbholdt Oct 07 '20 at 11:35
@kingraphaii nah, I didn't – Eugene Kovalev Oct 08 '20 at 11:34
Thanks for the reply. I managed to use Conditional Filters to make it work. – Redgren Grumbholdt Oct 08 '20 at 13:48
6

@EugeneKovalev What? No it does not. – ruohola Dec 08 '20 at 17:33
@ruohola my bad I haven't put enough details in that comment. It depends on a DB that is being used, not on Django. I was using PostgreSQL back then. – Eugene Kovalev Dec 23 '20 at 19:27
@EugeneKovalev Any idea on PostgreSQL with zero rows ? – Akshay Hazari Jan 06 '21 at 09:37
@EugeneKovalev could you edi your first comment, because has too many upvotes and the other comments are hide by default. This will help on the reading/finding answers. – titusfx Jan 15 '22 at 10:46
One way to avoid getting `null` could be to move the filter query in the parameter `filter=` inside the `annotate` method instead of the `.filter` method of object itself. – Hammad Jun 06 '22 at 09:46

score 3 · Answer 2 · answered Feb 09 '23 at 21:47

3

from django's documentation for Coalesce:

Prevent an aggregate Sum() from returning None. The aggregate default argument uses Coalesce() under the hood.

So instead of using Coalesce, we can use the "default" argument:

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

answered Feb 09 '23 at 21:47

lastmaj

146
2
8

1

This should be marked as the top solution, neat and straight to the point – Brian Obot Jul 07 '23 at 19:55

score -5 · Answer 3 · answered May 28 '11 at 11:35

-5

What about you use custom Manager? For example:

AnswerManager(models.Manager):
    def all_with_score(self):
       qs = self.get_query_set().annotate(score=Sum('vote__type'))
       # Here, you can do stuff with QuerySet, for example
       # iterate over all Answers and set 'score' to zero if None.

Answer(models.Model):
    //some fields here
    objects = AnswerManager()

Then, you can use:

>>> answers = Answer.objects.all_with_score().order_by('-score')

answered May 28 '11 at 11:35

vasco

1,502
1
16
19

4

The question asked how to replace the None values with 0 for the built in Sum aggregate function. This doesn't do that – Patrick Jun 15 '14 at 00:37

Annotating a Sum results in None rather than zero

3 Answers3

Linked