Annotating a Sum results in None rather than zero

DjangoDjango ModelsDjango Orm

Django Problem Overview


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.

Django Solutions


Solution 1 - Django

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

Solution 2 - Django

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJackieView Question on Stackoverflow
Solution 1 - Djangonelson orlandView Answer on Stackoverflow
Solution 2 - DjangovascoView Answer on Stackoverflow