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?
CodePudding user response:
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.