I use an annotation which counts upvotes/downvotes while returning a list of articles:
queryset = queryset.annotate(
upvotes_count=models.Sum(
models.Case(
models.When(likes__like_state=1, then=1),
default=0,
output_field=models.IntegerField()
)
)
).annotate(
downvotes_count=models.Sum(
models.Case(
models.When(likes__like_state=-1, then=1),
default=0,
output_field=models.IntegerField()
))
)
But each article also has a few categories as ManyToMany related field and I needed to return those categories comma-separated, so I wrote this function:
class GroupConcat(models.Aggregate):
function = 'GROUP_CONCAT'
template = "%(function)s(%(distinct)s %(expressions)s %(separator)s)"
def __init__(self, expression, distinct=False, separator=', ', **extra):
super(GroupConcat, self).__init__(
expression,
distinct='DISTINCT' if distinct else '',
separator="SEPARATOR '%s'" % separator,
output_field=models.CharField(),
**extra
)
And added it to my annotation:
queryset = queryset.annotate(category=GroupConcat('categories__name'))
It works fine but upvotes_count
and downvotes_count
went crazy and started to multiply(!) results by amount of categories.
So the question is: "Is there a way to use GROUP_CONCAT in Django without breaking down SUM annotations?"