Django .aggregate() on .annotate()

Question

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?

This should work. Can you show the traceback? Can you try with a single aggregate/annotate? — knbk
– knbk, Commented Jun 22, 2015 at 16:14
@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. — MauroMSL
– MauroMSL, Commented Jun 22, 2015 at 16:25
@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. — MauroMSL
– MauroMSL, Commented Jun 23, 2015 at 8:32
Perhaps because your annotate and aggregate fields have the same names? Maybe try ...aggregate(total_views=Sum('views')... — medmunds
– medmunds, Commented Jul 13, 2015 at 23:13

Bruno A. · Accepted Answer · 2019-10-13 21:39:00Z

13

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')
)

edited Oct 13, 2019 at 21:39

answered Jul 14, 2015 at 10:22

Bruno A.

1,88517 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alvand_ashhadi · Accepted Answer · 2022-07-25 11:51:57Z

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

Kachinga · Accepted Answer · 2023-01-21 11:50:01Z

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.

Collectives™ on Stack Overflow

Django .aggregate() on .annotate()

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related