2

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?"

Alexey Kislitsin
  • 726
  • 7
  • 12
  • related: https://stackoverflow.com/a/38017535, https://stackoverflow.com/q/10340684 – djvg Jul 15 '21 at 08:36

1 Answers1

0

Very nice solution. But to operate with group by field you should use order_by statement. for example:

Store.objects.all().values('value').order_by('value').annotate(stores=GroupConcat('id'))

would generate sql statement

SELECT store.value, GROUP_CONCAT(store.id SEPARATOR ",") AS stores FROM store WHERE store.value > 0 GROUP BY store.value ORDER BY store.value ASC

and result would be value, stores 1 "16,27"

Without order_by it would be like this:

SELECT store.value, GROUP_CONCAT(store.id SEPARATOR ",") AS stores FROM store WHERE store.value > 0 GROUP BY store.id ORDER BY store.value ASC

and result would be value, stores 1 16 2 27

Igor Kremin
  • 355
  • 4
  • 8