How can I aggregate strings by concatenate in Django annotate?

Question

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?

Loats

Answer 1

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.

Mcshane

Answer 2

It's not clear exactly what data you have that you'd expect to produce the result you want, but if you're using Postgresql as your database, StringAgg or ArrayAgg might be what you're looking for.

It gets a bit more involved if you want to exclude duplicates from your aggregated strings though.

Tasha