Django - Annotate with Case/When/Value and related objects

Question

I have the two following models:

class Post(models.Model):
    content = models.TextField()


class Vote(models.Model):
    UP_VOTE = 0
    DOWN_VOTE = 1

    VOTE_TYPES = (
        (UP_VOTE, "Up vote"),
        (DOWN_VOTE, "Down vote"),
    )

    post = models.ForeignKey(Post, related_name="votes")
    vote_type = models.PositiveSmallIntegerField(choices=VOTE_TYPES)

I would like to have a score property on Post that returns the sum of the values of the votes to that post, counting votes with UP_VOTE type as 1 and those with DOWN_VOTE as -1.

This is what I’ve tried:

    # inside Post
    @property
    def score(self):
        return (
            self.votes.all()
            .annotate(
                value=Case(
                    When(vote_type=Vote.DOWN_VOTE, then=Value(-1)),
                    When(vote_type=Vote.UP_VOTE, then=Value(1)),
                    default=Value("0"),
                    output_field=models.SmallIntegerField(),
                )
            )
            .aggregate(Sum("value"))["value__sum"]
        )

However, this yields None. More specifically, without dereferencing ["value__sum"], this returns {'value__sum': None}.

Is using Case-When-Value the correct approach to my use case? If so, what’s wrong with the code I posted?

Answer 1

The sum of an empty set will be NULL/None by default. As of django-4.0, you can work with the default=… parameter ^[Django-doc]:

from django.db.models import F, Sum

@property
def score(self):
    return self.votes.aggregate(total=Sum(-2*F('vote_type') + 1, default=0))['total']

Prior to django-4.0, you can work with Coalesce ^[Django-doc]:

from django.db.models import F, Sum, Value
from django.db.models.functions import Coalesce

@property
def score(self):
    return self.votes.aggregate(
        total=Coalesce(Sum(-2*F('vote_type') + 1), Value(0))
    )['total']

although in this simple case, you can just replace None by 0 at the Django/Python layer:

from django.db.models import F, Sum

@property
def score(self):
    return self.votes.aggregate(total=Sum(-2*F('vote_type') + 1))['total'] or 0

It might be better to use the "score" of a vote as value, so:

class Vote(models.Model):
    UP_VOTE = 1
    DOWN_VOTE = -1
    # …
    vote_type = models.SmallIntegerField(choices=VOTE_TYPES)

This will make the aggregation logic simpler, and will make it easier to later allow for example voting +5, -10, etc.