menu

Questions & Answers

Django .aggregate() on .annotate()

Is it possible to aggregate the annotations of a queryset?

Models:

class Article(models.Model):
    title = models.CharField(max_length=255)
    body = models.TextField()

class State(models.Model):
    article = Models.ForeignKey(Article)
    date = DateField()
    views = IntegerField()
    downloads = IntegerField()

I'm trying to do the following:

articles = metrics_models.Article.objects.filter(
    state__date__month=month,
    state__date__year=year
).annotate(
    views=Min('state__views'),
    downloads=Min('state__downloads')
).aggregate(
    views=Sum('views'),
    downloads=Sum('downloads')
)

error:

Exception Type: DatabaseError
Exception Value:    
column "downloads" does not exist
LINE 1: SELECT SUM(downloads), SUM(views) FROM (SELECT "metrics_arti...

When running this, I get a DatabaseError as django tries to do the aggregation on the 'views' and 'download' database columns instead of doing it on the annotations.

Is there any other way to do this aggregation on the QuerySet annotations?

Comments:
2023-01-22 00:55:10
This should work. Can you show the traceback? Can you try with a single aggregate/annotate?
2023-01-22 00:55:10
@knbk Traceback: column "views" does not exist LINE 1: SELECT SUM(views) FROM (SELECT "metrics_article"."id" AS "id... I tried with one single annotate/aggregate and still the same error.
2023-01-22 00:55:10
TLDR; downloads=Sum('state__downloads') missing state__ ???
2023-01-22 00:55:10
@DuD. That would aggregate all the state items, what I'm trying to do is to only aggregate the states within the annotation (which are the ones with the minimum value per each article.
2023-01-22 00:55:10
Perhaps because your annotate and aggregate fields have the same names? Maybe try ...aggregate(total_views=Sum('views')...
Answers(3) :

I think medmunds is right, you cannot re-use the same name for annotate and aggregate alias. This is what you want to do, I think:

articles = metrics_models.Article.objects.filter(
    state__date__month=month,
    state__date__year=year
).annotate(
    min_views=Min('state__views'),
    min_downloads=Min('state__downloads')
).aggregate(
    sum_min_views=Sum('min_views'),
    sum_min_downloads=Sum('min_downloads')
)

based on your original query, i think this would work.

from django.db.models import ExpressionWrapper, Min, OuterRef

articles = (
    Article.objects.filter(state__date__month=month, state__date__year=year)
    .annotate(
        views=ExpressionWrapper(
            Min(
                State.objects.filter(article_id=OuterRef("id")).values_list(
                    "views", flat=True
                )
            ),
            output_field=models.IntegerField(),
        ),
        downloads=ExpressionWrapper(
            Min(
                State.objects.filter(article_id=OuterRef("id")).values_list(
                    "downloads", flat=True
                )
            ),
            output_field=models.IntegerField(),
        ),
    )
    .aggregate(views=Sum("views"), downloads=Sum("downloads"))
)

When you use the same field name for both the annotate() method and the aggregate() method, you are creating a new field with that name on each object in the queryset and then trying to get the sum of that field.

However, the new field created by the annotate() method is not the same as the field being passed to the aggregate() method.

The annotate() method creates a new field on each object in the queryset that contains the result of the computation, and this field is not stored in the database, it only exists in the queryset in memory.

On the other hand, the field passed to the aggregate() method is a reference to a field stored in the database/queryset, and it's used to get the sum of the values stored in the database.

So when you use the same field name for both annotate() and aggregate() methods, you are trying to sum up the values of a field that only exists in memory (the one you declared on the aggregate method overrides the one from annotate), not in the database/queryset. This is why you are not getting the expected result.