CODEWITHSUNDEEP
djangoormdjango-queryset
Dash Winterson
Dash Winterson

Reputation: 1295

Is the django documentation wrong here?

https://docs.djangoproject.com/en/2.2/ref/models/expressions/#aggregate-expressions

It seems to mention aggregate functions but then use annotations as an example, am I going crazy or is there something I'm missing here? I'm having a hell of time trying to figure it out!

Upvotes: 1

Views: 31

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477190

You can use aggregate functions like Count [Djanog-doc], Sum [Django-doc], etc. both in an .annotate(..) [Django-doc] and .aggregate(..) function call.

But the two are not the same. An .aggregate(..) means that we calculate the COUNT(*), SUM(*), etc. over the entire queryset. .aggregate(..) will make an eager database call, and return a dictionary that contains the keys, that map to the value that is obtained from the COUNT(*), etc.

The .annotate(..) on the other hand is used to make aggregates per object. So it will translate this to a query with a GROUP BY part. It will add extra attributes to the model objects (or other objects) that arise from the queryset. An .annotate(..) produces a new queryset, it will thus lazily query the database.

Example: Say we have the following model:

class Author(models.Model):
    name = models.CharField(max_length=128)

class Book(models.Model):
    name = models.CharField(max_length=128)
    author = models.ForeignKey(Author, on_delete=models.PROTECT)

Then we can count the number of Books for all author's with a name that starts with 'Alice' with:

from django.db.models import Count

Author.objects.filter(name__startswith='Alice').aggregate(books=Count('book'))

Here we thus will retrieve a dictionary, like {'books': 1425}, that contains the total number of books written by an Author who's name starts with 'Alice'. The query query will look like:

SELECT COUNT(book.id) AS books
FROM author
LEFT OUTER JOIN book ON book.author_id = author.id
WHERE author.name LIKE 'Alice%'

So we here count all the books, and obtain one result.

We can however use .annotate(..) as well:

from django.db.models import Count

Author.objects.filter(name__startswith='Alice').annotate(books=Count('book'))

This is a queryset that will, if evaluated, result in a collection of Authors. Each Author that arises from this queryset will have an extra attribute .book that contains the number of books that author has written.

Here the query will thus look like:

SELECT author.*, COUNT(book.id) AS books
FROM author
LEFT OUTER JOIN book ON book.author_id = author.id
WHERE author.name LIKE 'Alice%'
GROUP BY author.id

Upvotes: 1

Related Questions