How can I aggregate strings by concatenate in Django annotate?

I have the followed model in Django:

class Click(models.Model):
    url = models.ForeignKey(Url, on_delete=models.CASCADE)
    browser = models.CharField(max_length=255)
    platform = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

I want to build a query to get the total clicks per day on the current month, along with browsers and platforms concatenated. For example:

[
  {
    'created_at__day': 28,
    'browsers': 'Chrome, Firefox',
    'platforms': 'Windows, Linux',
    'clicks': 4
  }
]

What I did until this moment was that query:

queryset = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month
).values('created_at__day', 'browser').annotate(
    clicks=Count('created_at'),
)

How can I concat every browser and platform, only grouping by created_at__day?

Solution

I think you're close, as @Shabble mentioned, StringAgg if you're using Postresql:

from django.contrib.postgres.aggregates import StringAgg

queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=StringAgg("platform", delimiter=", ", distinct=True),
    )
)

If you're not, you'd have to go with a Subquery, and write an appropriate string Aggregation for the database. I'd be something like:

# Inner query
click_platforms = Click.objects.filter(
    url=url_instance,
    created_at__month=datetime.now().month,
    created_at__day=OuterRef("created_at__day"),
).distinct()

# Outer query
queryset = (
    Click.objects
    .filter(url=url_instance, created_at__month=datetime.now().month)
    .values("created_at__day", "browser")
    .annotate(
        clicks=Count("created_at"),
        platforms=YourAggregation(click_platforms),
    )
)

I cannot write YourAggregation, it'd depend on what's available on your database.