Using Case-When-Value with annotate on related object

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? Thanks in advance.

This is probably happening due to the post you’re trying to get the score of does not have any votes.
If you’re familiar with the django shell, you can try:

post = Post.objects.create(content="Testing")
post.votes.create(vote_type=Vote.UP_VOTE)
post.votes.create(vote_type=Vote.UP_VOTE)
post.votes.create(vote_type=Vote.UP_VOTE)
post.votes.create(vote_type=Vote.DOWN_VOTE)
post.votes.create(vote_type=Vote.UP_VOTE)
post.votes.create(vote_type=Vote.UP_VOTE)
print(post.score)
1 Like

Yeah, that was pretty silly on my part. Thank you for pointing out. Fixed adding a default parameter like this:

    @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(score=Sum("value", default=0))["score"]
        )

As an additional step: what if I wanted to be able to annotate a queryset of Posts with this property? For example, to order by score.

How would I need to modify this in order to be able to use it such as this:

Post.objects.all().annotate(score=
     # not sure how to adapt the Case/When/Value + aggregation here
)

I will give you my opinion here:

What if your Vote model was like this?

class Vote(models.Model):
    UP_VOTE = 1  # <-
    DOWN_VOTE = -1  # <-

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

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

Then to calculate the score you only need to Sum them.

@property
def score(self):
    return self.votes.aggregate(vote_score=Sum("vote_count"))["vote_score"]

And to annotate on the queryset:
Post.objects.annotate(vote_score=Sum("votes__vote_type"))

An additional note:
I named vote_score instead of score so it doesn’t clash with the score property in the Post model

This could make it simpler, but I like the idea of decoupling the vote type (in this case a number, but in principle just an enum constant) from the vote value (a number).

What if I want to add more vote types which have the same value? For example, I might want to add a “heart” reaction or “laugh” reaction which also gives one point like the “thumbs up”/upvote, but it clearly has to be a different vote type although it gives the same score.

this is close to what I’m looking for. Is there a way to use Case/When/Value inside that Sum? I can’t figure out what it’d look like.

I think that you first need to annotate the Case, and then do the sum refering that using the F clause

    @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(score=Sum(F("value"), default=0))["score"]
        )

You can do that inside the annotate as well.

The one thing I’m missing is: how would I annotate the Case when it doesn’t refer to a Post, which would be the model my queryset contains, but rather to its votes?