Using Case-When-Value with annotate on related object

Using Django Using the ORM
1

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.

2

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
3

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

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

5

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.

6

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.

7

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?