Aggregation¶

Following relationships backwards¶

In a way similar to Lookups that span relationships, aggregations and annotations on fields of models or models that are related to the one you are querying can include traversing “reverse” relationships. The lowercase name of related models and double-underscores are used here too.

For example, we can ask for all publishers, annotated with their respective total book stock counters (note how we use ‘book’ to specify the Publisher->Book reverse foreign key hop):

>>> from django.db.models import Count, Min, Sum, Max, Avg
>>> Publisher.objects.annotate(Count('book'))

(Every Publisher in the resulting QuerySet will have an extra attribute called book__count.)

We can also ask for the oldest book of any of those managed by every publisher:

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

(The resulting dictionary will have a key called 'oldest_pubdate'. If no such alias was specified, it would be the rather long 'book__pubdate__min'.)

This doesn’t apply just to foreign keys. It also works with many-to-many relations. For example, we can ask for every author, annotated with the total number of pages considering all the books he/she has (co-)authored (note how we use ‘book’ to specify the Author->Book reverse many-to-many hop):

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

(Every Author in the resulting QuerySet will have an extra attribute called total_pages. If no such alias was specified, it would be the rather long book__pages__sum.)

Or ask for the average rating of all the books written by author(s) we have on file:

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

(The resulting dictionary will have a key called 'average__rating'. If no such alias was specified, it would be the rather long 'book__rating__avg'.)

filter() and exclude()¶

Aggregates can also participate in filters. Any filter() (or exclude()) applied to normal model fields will have the effect of constraining the objects that are considered for aggregation.

When used with an annotate() clause, a filter has the effect of constraining the objects for which an annotation is calculated. For example, you can generate an annotated list of all books that have a title starting with “Django” using the query:

>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

When used with an aggregate() clause, a filter has the effect of constraining the objects over which the aggregate is calculated. For example, you can generate the average price of all books with a title that starts with “Django” using the query:

>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

>>> Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)

>>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

order_by()¶

Annotations can be used as a basis for ordering. When you define an order_by() clause, the aggregates you provide can reference any alias defined as part of an annotate() clause in the query.

For example, to order a QuerySet of books by the number of authors that have contributed to the book, you could use the following query:

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

values()¶

Ordinarily, annotations are generated on a per-object basis - an annotated QuerySet will return one result for each object in the original QuerySet. However, when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.

For example, consider an author query that attempts to find out the average rating of books written by each author:

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

This will return one result for each author in the database, annotated with their average book rating.

However, the result will be slightly different if you use a values() clause:

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

In this example, the authors will be grouped by name, so you will only get an annotated result for each unique author name. This means if you have two authors with the same name, their results will be merged into a single result in the output of the query; the average will be computed as the average over the books written by both authors.

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))

Item.objects.values("data").annotate(Count("id")).order_by()

Aggregating annotations¶

You can also generate an aggregate on the result of an annotation. When you define an aggregate() clause, the aggregates you provide can reference any alias defined as part of an annotate() clause in the query.

For example, if you wanted to calculate the average number of authors per book you first annotate the set of books with the author count, then aggregate that author count, referencing the annotation field:

>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}