[Django]-Annotating a Sum results in None rather than zero

by

207👍

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'))
👤nelson orland

6👍

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

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

Leave a comment