| 1 |
=========== |
| 2 |
Aggregation |
| 3 |
=========== |
| 4 |
|
| 5 |
.. currentmodule:: django.db.models |
| 6 |
|
| 7 |
The topic guide on :doc:`Django's database-abstraction API </topics/db/queries>` |
| 8 |
described the way that you can use Django queries that create, |
| 9 |
retrieve, update and delete individual objects. However, sometimes you will |
| 10 |
need to retrieve values that are derived by summarizing or *aggregating* a |
| 11 |
collection of objects. This topic guide describes the ways that aggregate values |
| 12 |
can be generated and returned using Django queries. |
| 13 |
|
| 14 |
Throughout this guide, we'll refer to the following models. These models are |
| 15 |
used to track the inventory for a series of online bookstores: |
| 16 |
|
| 17 |
.. _queryset-model-example: |
| 18 |
|
| 19 |
.. code-block:: python |
| 20 |
|
| 21 |
from django.db import models |
| 22 |
|
| 23 |
|
| 24 |
class Author(models.Model): |
| 25 |
name = models.CharField(max_length=100) |
| 26 |
age = models.IntegerField() |
| 27 |
|
| 28 |
|
| 29 |
class Publisher(models.Model): |
| 30 |
name = models.CharField(max_length=300) |
| 31 |
|
| 32 |
|
| 33 |
class Book(models.Model): |
| 34 |
name = models.CharField(max_length=300) |
| 35 |
pages = models.IntegerField() |
| 36 |
price = models.DecimalField(max_digits=10, decimal_places=2) |
| 37 |
rating = models.FloatField() |
| 38 |
authors = models.ManyToManyField(Author) |
| 39 |
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE) |
| 40 |
pubdate = models.DateField() |
| 41 |
|
| 42 |
|
| 43 |
class Store(models.Model): |
| 44 |
name = models.CharField(max_length=300) |
| 45 |
books = models.ManyToManyField(Book) |
| 46 |
|
| 47 |
Cheat sheet |
| 48 |
=========== |
| 49 |
|
| 50 |
In a hurry? Here's how to do common aggregate queries, assuming the models |
| 51 |
above: |
| 52 |
|
| 53 |
.. code-block:: pycon |
| 54 |
|
| 55 |
# Total number of books. |
| 56 |
>>> Book.objects.count() |
| 57 |
2452 |
| 58 |
|
| 59 |
# Total number of books with publisher=BaloneyPress |
| 60 |
>>> Book.objects.filter(publisher__name="BaloneyPress").count() |
| 61 |
73 |
| 62 |
|
| 63 |
# Average price across all books, provide default to be returned instead |
| 64 |
# of None if no books exist. |
| 65 |
>>> from django.db.models import Avg |
| 66 |
>>> Book.objects.aggregate(Avg("price", default=0)) |
| 67 |
{'price__avg': 34.35} |
| 68 |
|
| 69 |
# Max price across all books, provide default to be returned instead of |
| 70 |
# None if no books exist. |
| 71 |
>>> from django.db.models import Max |
| 72 |
>>> Book.objects.aggregate(Max("price", default=0)) |
| 73 |
{'price__max': Decimal('81.20')} |
| 74 |
|
| 75 |
# Difference between the highest priced book and the average price of all books. |
| 76 |
>>> from django.db.models import FloatField |
| 77 |
>>> Book.objects.aggregate( |
| 78 |
... price_diff=Max("price", output_field=FloatField()) - Avg("price") |
| 79 |
... ) |
| 80 |
{'price_diff': 46.85} |
| 81 |
|
| 82 |
# All the following queries involve traversing the Book<->Publisher |
| 83 |
# foreign key relationship backwards. |
| 84 |
|
| 85 |
# Each publisher, each with a count of books as a "num_books" attribute. |
| 86 |
>>> from django.db.models import Count |
| 87 |
>>> pubs = Publisher.objects.annotate(num_books=Count("book")) |
| 88 |
>>> pubs |
| 89 |
<QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]> |
| 90 |
>>> pubs[0].num_books |
| 91 |
73 |
| 92 |
|
| 93 |
# Each publisher, with a separate count of books with a rating above and below 5 |
| 94 |
>>> from django.db.models import Q |
| 95 |
>>> above_5 = Count("book", filter=Q(book__rating__gt=5)) |
| 96 |
>>> below_5 = Count("book", filter=Q(book__rating__lte=5)) |
| 97 |
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5) |
| 98 |
>>> pubs[0].above_5 |
| 99 |
23 |
| 100 |
>>> pubs[0].below_5 |
| 101 |
12 |
| 102 |
|
| 103 |
# The top 5 publishers, in order by number of books. |
| 104 |
>>> pubs = Publisher.objects.annotate(num_books=Count("book")).order_by("-num_books")[:5] |
| 105 |
>>> pubs[0].num_books |
| 106 |
1323 |
| 107 |
|
| 108 |
Generating aggregates over a ``QuerySet`` |
| 109 |
========================================= |
| 110 |
|
| 111 |
Django provides two ways to generate aggregates. The first way is to generate |
| 112 |
summary values over an entire ``QuerySet``. For example, say you wanted to |
| 113 |
calculate the average price of all books available for sale. Django's query |
| 114 |
syntax provides a means for describing the set of all books: |
| 115 |
|
| 116 |
.. code-block:: pycon |
| 117 |
|
| 118 |
>>> Book.objects.all() |
| 119 |
|
| 120 |
What we need is a way to calculate summary values over the objects that |
| 121 |
belong to this ``QuerySet``. This is done by appending an ``aggregate()`` |
| 122 |
clause onto the ``QuerySet``: |
| 123 |
|
| 124 |
.. code-block:: pycon |
| 125 |
|
| 126 |
>>> from django.db.models import Avg |
| 127 |
>>> Book.objects.all().aggregate(Avg("price")) |
| 128 |
{'price__avg': 34.35} |
| 129 |
|
| 130 |
The ``all()`` is redundant in this example, so this could be simplified to: |
| 131 |
|
| 132 |
.. code-block:: pycon |
| 133 |
|
| 134 |
>>> Book.objects.aggregate(Avg("price")) |
| 135 |
{'price__avg': 34.35} |
| 136 |
|
| 137 |
The argument to the ``aggregate()`` clause describes the aggregate value that |
| 138 |
we want to compute - in this case, the average of the ``price`` field on the |
| 139 |
``Book`` model. A list of the aggregate functions that are available can be |
| 140 |
found in the :ref:`QuerySet reference <aggregation-functions>`. |
| 141 |
|
| 142 |
``aggregate()`` is a terminal clause for a ``QuerySet`` that, when invoked, |
| 143 |
returns a dictionary of name-value pairs. The name is an identifier for the |
| 144 |
aggregate value; the value is the computed aggregate. The name is |
| 145 |
automatically generated from the name of the field and the aggregate function. |
| 146 |
If you want to manually specify a name for the aggregate value, you can do so |
| 147 |
by providing that name when you specify the aggregate clause: |
| 148 |
|
| 149 |
.. code-block:: pycon |
| 150 |
|
| 151 |
>>> Book.objects.aggregate(average_price=Avg("price")) |
| 152 |
{'average_price': 34.35} |
| 153 |
|
| 154 |
If you want to generate more than one aggregate, you add another argument to |
| 155 |
the ``aggregate()`` clause. So, if we also wanted to know the maximum and |
| 156 |
minimum price of all books, we would issue the query: |
| 157 |
|
| 158 |
.. code-block:: pycon |
| 159 |
|
| 160 |
>>> from django.db.models import Avg, Max, Min |
| 161 |
>>> Book.objects.aggregate(Avg("price"), Max("price"), Min("price")) |
| 162 |
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')} |
| 163 |
|
| 164 |
Generating aggregates for each item in a ``QuerySet`` |
| 165 |
===================================================== |
| 166 |
|
| 167 |
The second way to generate summary values is to generate an independent |
| 168 |
summary for each object in a :class:`.QuerySet`. For example, if you are |
| 169 |
retrieving a list of books, you may want to know how many authors contributed |
| 170 |
to each book. Each Book has a many-to-many relationship with the Author; we |
| 171 |
want to summarize this relationship for each book in the ``QuerySet``. |
| 172 |
|
| 173 |
Per-object summaries can be generated using the |
| 174 |
:meth:`~.QuerySet.annotate` clause. When an ``annotate()`` clause is |
| 175 |
specified, each object in the ``QuerySet`` will be annotated with the |
| 176 |
specified values. |
| 177 |
|
| 178 |
The syntax for these annotations is identical to that used for the |
| 179 |
:meth:`~.QuerySet.aggregate` clause. Each argument to ``annotate()`` describes |
| 180 |
an aggregate that is to be calculated. For example, to annotate books with the |
| 181 |
number of authors: |
| 182 |
|
| 183 |
.. code-block:: pycon |
| 184 |
|
| 185 |
# Build an annotated queryset |
| 186 |
>>> from django.db.models import Count |
| 187 |
>>> q = Book.objects.annotate(Count("authors")) |
| 188 |
# Interrogate the first object in the queryset |
| 189 |
>>> q[0] |
| 190 |
<Book: The Definitive Guide to Django> |
| 191 |
>>> q[0].authors__count |
| 192 |
2 |
| 193 |
# Interrogate the second object in the queryset |
| 194 |
>>> q[1] |
| 195 |
<Book: Practical Django Projects> |
| 196 |
>>> q[1].authors__count |
| 197 |
1 |
| 198 |
|
| 199 |
As with ``aggregate()``, the name for the annotation is automatically derived |
| 200 |
from the name of the aggregate function and the name of the field being |
| 201 |
aggregated. You can override this default name by providing an alias when you |
| 202 |
specify the annotation: |
| 203 |
|
| 204 |
.. code-block:: pycon |
| 205 |
|
| 206 |
>>> q = Book.objects.annotate(num_authors=Count("authors")) |
| 207 |
>>> q[0].num_authors |
| 208 |
2 |
| 209 |
>>> q[1].num_authors |
| 210 |
1 |
| 211 |
|
| 212 |
Unlike ``aggregate()``, ``annotate()`` is *not* a terminal clause. The output |
| 213 |
of the ``annotate()`` clause is a ``QuerySet``; this ``QuerySet`` can be |
| 214 |
modified using any other ``QuerySet`` operation, including ``filter()``, |
| 215 |
``order_by()``, or even additional calls to ``annotate()``. |
| 216 |
|
| 217 |
.. _combining-multiple-aggregations: |
| 218 |
|
| 219 |
Combining multiple aggregations |
| 220 |
------------------------------- |
| 221 |
|
| 222 |
Combining multiple aggregations with ``annotate()`` will :ticket:`yield the |
| 223 |
wrong results <10060>` because joins are used instead of subqueries: |
| 224 |
|
| 225 |
.. code-block:: pycon |
| 226 |
|
| 227 |
>>> book = Book.objects.first() |
| 228 |
>>> book.authors.count() |
| 229 |
2 |
| 230 |
>>> book.store_set.count() |
| 231 |
3 |
| 232 |
>>> q = Book.objects.annotate(Count("authors"), Count("store")) |
| 233 |
>>> q[0].authors__count |
| 234 |
6 |
| 235 |
>>> q[0].store__count |
| 236 |
6 |
| 237 |
|
| 238 |
For most aggregates, there is no way to avoid this problem, however, the |
| 239 |
:class:`~django.db.models.Count` aggregate has a ``distinct`` parameter that |
| 240 |
may help: |
| 241 |
|
| 242 |
.. code-block:: pycon |
| 243 |
|
| 244 |
>>> q = Book.objects.annotate( |
| 245 |
... Count("authors", distinct=True), Count("store", distinct=True) |
| 246 |
... ) |
| 247 |
>>> q[0].authors__count |
| 248 |
2 |
| 249 |
>>> q[0].store__count |
| 250 |
3 |
| 251 |
|
| 252 |
.. admonition:: If in doubt, inspect the SQL query! |
| 253 |
|
| 254 |
In order to understand what happens in your query, consider inspecting the |
| 255 |
``query`` property of your ``QuerySet``. |
| 256 |
|
| 257 |
Joins and aggregates |
| 258 |
==================== |
| 259 |
|
| 260 |
So far, we have dealt with aggregates over fields that belong to the |
| 261 |
model being queried. However, sometimes the value you want to aggregate |
| 262 |
will belong to a model that is related to the model you are querying. |
| 263 |
|
| 264 |
When specifying the field to be aggregated in an aggregate function, Django |
| 265 |
will allow you to use the same :ref:`double underscore notation |
| 266 |
<field-lookups-intro>` that is used when referring to related fields in |
| 267 |
filters. Django will then handle any table joins that are required to retrieve |
| 268 |
and aggregate the related value. |
| 269 |
|
| 270 |
For example, to find the price range of books offered in each store, |
| 271 |
you could use the annotation: |
| 272 |
|
| 273 |
.. code-block:: pycon |
| 274 |
|
| 275 |
>>> from django.db.models import Max, Min |
| 276 |
>>> Store.objects.annotate(min_price=Min("books__price"), max_price=Max("books__price")) |
| 277 |
|
| 278 |
This tells Django to retrieve the ``Store`` model, join (through the |
| 279 |
many-to-many relationship) with the ``Book`` model, and aggregate on the |
| 280 |
price field of the book model to produce a minimum and maximum value. |
| 281 |
|
| 282 |
The same rules apply to the ``aggregate()`` clause. If you wanted to |
| 283 |
know the lowest and highest price of any book that is available for sale |
| 284 |
in any of the stores, you could use the aggregate: |
| 285 |
|
| 286 |
.. code-block:: pycon |
| 287 |
|
| 288 |
>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price")) |
| 289 |
|
| 290 |
Join chains can be as deep as you require. For example, to extract the |
| 291 |
age of the youngest author of any book available for sale, you could |
| 292 |
issue the query: |
| 293 |
|
| 294 |
.. code-block:: pycon |
| 295 |
|
| 296 |
>>> Store.objects.aggregate(youngest_age=Min("books__authors__age")) |
| 297 |
|
| 298 |
Following relationships backwards |
| 299 |
--------------------------------- |
| 300 |
|
| 301 |
In a way similar to :ref:`lookups-that-span-relationships`, aggregations and |
| 302 |
annotations on fields of models or models that are related to the one you are |
| 303 |
querying can include traversing "reverse" relationships. The lowercase name |
| 304 |
of related models and double-underscores are used here too. |
| 305 |
|
| 306 |
For example, we can ask for all publishers, annotated with their respective |
| 307 |
total book stock counters (note how we use ``'book'`` to specify the |
| 308 |
``Publisher`` -> ``Book`` reverse foreign key hop): |
| 309 |
|
| 310 |
.. code-block:: pycon |
| 311 |
|
| 312 |
>>> from django.db.models import Avg, Count, Min, Sum |
| 313 |
>>> Publisher.objects.annotate(Count("book")) |
| 314 |
|
| 315 |
(Every ``Publisher`` in the resulting ``QuerySet`` will have an extra attribute |
| 316 |
called ``book__count``.) |
| 317 |
|
| 318 |
We can also ask for the oldest book of any of those managed by every publisher: |
| 319 |
|
| 320 |
.. code-block:: pycon |
| 321 |
|
| 322 |
>>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate")) |
| 323 |
|
| 324 |
(The resulting dictionary will have a key called ``'oldest_pubdate'``. If no |
| 325 |
such alias were specified, it would be the rather long ``'book__pubdate__min'``.) |
| 326 |
|
| 327 |
This doesn't apply just to foreign keys. It also works with many-to-many |
| 328 |
relations. For example, we can ask for every author, annotated with the total |
| 329 |
number of pages considering all the books the author has (co-)authored (note how we |
| 330 |
use ``'book'`` to specify the ``Author`` -> ``Book`` reverse many-to-many hop): |
| 331 |
|
| 332 |
.. code-block:: pycon |
| 333 |
|
| 334 |
>>> Author.objects.annotate(total_pages=Sum("book__pages")) |
| 335 |
|
| 336 |
(Every ``Author`` in the resulting ``QuerySet`` will have an extra attribute |
| 337 |
called ``total_pages``. If no such alias were specified, it would be the rather |
| 338 |
long ``book__pages__sum``.) |
| 339 |
|
| 340 |
Or ask for the average rating of all the books written by author(s) we have on |
| 341 |
file: |
| 342 |
|
| 343 |
.. code-block:: pycon |
| 344 |
|
| 345 |
>>> Author.objects.aggregate(average_rating=Avg("book__rating")) |
| 346 |
|
| 347 |
(The resulting dictionary will have a key called ``'average_rating'``. If no |
| 348 |
such alias were specified, it would be the rather long ``'book__rating__avg'``.) |
| 349 |
|
| 350 |
Aggregations and other ``QuerySet`` clauses |
| 351 |
=========================================== |
| 352 |
|
| 353 |
``filter()`` and ``exclude()`` |
| 354 |
------------------------------ |
| 355 |
|
| 356 |
Aggregates can also participate in filters. Any ``filter()`` (or |
| 357 |
``exclude()``) applied to normal model fields will have the effect of |
| 358 |
constraining the objects that are considered for aggregation. |
| 359 |
|
| 360 |
When used with an ``annotate()`` clause, a filter has the effect of |
| 361 |
constraining the objects for which an annotation is calculated. For example, |
| 362 |
you can generate an annotated list of all books that have a title starting |
| 363 |
with "Django" using the query: |
| 364 |
|
| 365 |
.. code-block:: pycon |
| 366 |
|
| 367 |
>>> from django.db.models import Avg, Count |
| 368 |
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count("authors")) |
| 369 |
|
| 370 |
When used with an ``aggregate()`` clause, a filter has the effect of |
| 371 |
constraining the objects over which the aggregate is calculated. |
| 372 |
For example, you can generate the average price of all books with a |
| 373 |
title that starts with "Django" using the query: |
| 374 |
|
| 375 |
.. code-block:: pycon |
| 376 |
|
| 377 |
>>> Book.objects.filter(name__startswith="Django").aggregate(Avg("price")) |
| 378 |
|
| 379 |
.. _filtering-on-annotations: |
| 380 |
|
| 381 |
Filtering on annotations |
| 382 |
~~~~~~~~~~~~~~~~~~~~~~~~ |
| 383 |
|
| 384 |
Annotated values can also be filtered. The alias for the annotation can be |
| 385 |
used in ``filter()`` and ``exclude()`` clauses in the same way as any other |
| 386 |
model field. |
| 387 |
|
| 388 |
For example, to generate a list of books that have more than one author, |
| 389 |
you can issue the query: |
| 390 |
|
| 391 |
.. code-block:: pycon |
| 392 |
|
| 393 |
>>> Book.objects.annotate(num_authors=Count("authors")).filter(num_authors__gt=1) |
| 394 |
|
| 395 |
This query generates an annotated result set, and then generates a filter |
| 396 |
based upon that annotation. |
| 397 |
|
| 398 |
If you need two annotations with two separate filters you can use the |
| 399 |
``filter`` argument with any aggregate. For example, to generate a list of |
| 400 |
authors with a count of highly rated books: |
| 401 |
|
| 402 |
.. code-block:: pycon |
| 403 |
|
| 404 |
>>> highly_rated = Count("book", filter=Q(book__rating__gte=7)) |
| 405 |
>>> Author.objects.annotate(num_books=Count("book"), highly_rated_books=highly_rated) |
| 406 |
|
| 407 |
Each ``Author`` in the result set will have the ``num_books`` and |
| 408 |
``highly_rated_books`` attributes. See also :ref:`conditional-aggregation`. |
| 409 |
|
| 410 |
.. admonition:: Choosing between ``filter`` and ``QuerySet.filter()`` |
| 411 |
|
| 412 |
Avoid using the ``filter`` argument with a single annotation or |
| 413 |
aggregation. It's more efficient to use ``QuerySet.filter()`` to exclude |
| 414 |
rows. The aggregation ``filter`` argument is only useful when using two or |
| 415 |
more aggregations over the same relations with different conditionals. |
| 416 |
|
| 417 |
Order of ``annotate()`` and ``filter()`` clauses |
| 418 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 419 |
|
| 420 |
When developing a complex query that involves both ``annotate()`` and |
| 421 |
``filter()`` clauses, pay particular attention to the order in which the |
| 422 |
clauses are applied to the ``QuerySet``. |
| 423 |
|
| 424 |
When an ``annotate()`` clause is applied to a query, the annotation is computed |
| 425 |
over the state of the query up to the point where the annotation is requested. |
| 426 |
The practical implication of this is that ``filter()`` and ``annotate()`` are |
| 427 |
not commutative operations. |
| 428 |
|
| 429 |
Given: |
| 430 |
|
| 431 |
* Publisher A has two books with ratings 4 and 5. |
| 432 |
* Publisher B has two books with ratings 1 and 4. |
| 433 |
* Publisher C has one book with rating 1. |
| 434 |
|
| 435 |
Here's an example with the ``Count`` aggregate: |
| 436 |
|
| 437 |
.. code-block:: pycon |
| 438 |
|
| 439 |
>>> a, b = Publisher.objects.annotate(num_books=Count("book", distinct=True)).filter( |
| 440 |
... book__rating__gt=3.0 |
| 441 |
... ) |
| 442 |
>>> a, a.num_books |
| 443 |
(<Publisher: A>, 2) |
| 444 |
>>> b, b.num_books |
| 445 |
(<Publisher: B>, 2) |
| 446 |
|
| 447 |
>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count("book")) |
| 448 |
>>> a, a.num_books |
| 449 |
(<Publisher: A>, 2) |
| 450 |
>>> b, b.num_books |
| 451 |
(<Publisher: B>, 1) |
| 452 |
|
| 453 |
Both queries return a list of publishers that have at least one book with a |
| 454 |
rating exceeding 3.0, hence publisher C is excluded. |
| 455 |
|
| 456 |
In the first query, the annotation precedes the filter, so the filter has no |
| 457 |
effect on the annotation. ``distinct=True`` is required to avoid a :ref:`query |
| 458 |
bug <combining-multiple-aggregations>`. |
| 459 |
|
| 460 |
The second query counts the number of books that have a rating exceeding 3.0 |
| 461 |
for each publisher. The filter precedes the annotation, so the filter |
| 462 |
constrains the objects considered when calculating the annotation. |
| 463 |
|
| 464 |
Here's another example with the ``Avg`` aggregate: |
| 465 |
|
| 466 |
.. code-block:: pycon |
| 467 |
|
| 468 |
>>> a, b = Publisher.objects.annotate(avg_rating=Avg("book__rating")).filter( |
| 469 |
... book__rating__gt=3.0 |
| 470 |
... ) |
| 471 |
>>> a, a.avg_rating |
| 472 |
(<Publisher: A>, 4.5) # (5+4)/2 |
| 473 |
>>> b, b.avg_rating |
| 474 |
(<Publisher: B>, 2.5) # (1+4)/2 |
| 475 |
|
| 476 |
>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate( |
| 477 |
... avg_rating=Avg("book__rating") |
| 478 |
... ) |
| 479 |
>>> a, a.avg_rating |
| 480 |
(<Publisher: A>, 4.5) # (5+4)/2 |
| 481 |
>>> b, b.avg_rating |
| 482 |
(<Publisher: B>, 4.0) # 4/1 (book with rating 1 excluded) |
| 483 |
|
| 484 |
The first query asks for the average rating of all a publisher's books for |
| 485 |
publisher's that have at least one book with a rating exceeding 3.0. The second |
| 486 |
query asks for the average of a publisher's book's ratings for only those |
| 487 |
ratings exceeding 3.0. |
| 488 |
|
| 489 |
It's difficult to intuit how the ORM will translate complex querysets into SQL |
| 490 |
queries so when in doubt, inspect the SQL with ``str(queryset.query)`` and |
| 491 |
write plenty of tests. |
| 492 |
|
| 493 |
``order_by()`` |
| 494 |
-------------- |
| 495 |
|
| 496 |
Annotations can be used as a basis for ordering. When you |
| 497 |
define an ``order_by()`` clause, the aggregates you provide can reference |
| 498 |
any alias defined as part of an ``annotate()`` clause in the query. |
| 499 |
|
| 500 |
For example, to order a ``QuerySet`` of books by the number of authors |
| 501 |
that have contributed to the book, you could use the following query: |
| 502 |
|
| 503 |
.. code-block:: pycon |
| 504 |
|
| 505 |
>>> Book.objects.annotate(num_authors=Count("authors")).order_by("num_authors") |
| 506 |
|
| 507 |
``values()`` |
| 508 |
------------ |
| 509 |
|
| 510 |
Ordinarily, annotations are generated on a per-object basis - an annotated |
| 511 |
``QuerySet`` will return one result for each object in the original |
| 512 |
``QuerySet``. However, when a ``values()`` clause is used to constrain the |
| 513 |
columns that are returned in the result set, the method for evaluating |
| 514 |
annotations is slightly different. Instead of returning an annotated result |
| 515 |
for each result in the original ``QuerySet``, the original results are |
| 516 |
grouped according to the unique combinations of the fields specified in the |
| 517 |
``values()`` clause. An annotation is then provided for each unique group; |
| 518 |
the annotation is computed over all members of the group. |
| 519 |
|
| 520 |
For example, consider an author query that attempts to find out the average |
| 521 |
rating of books written by each author: |
| 522 |
|
| 523 |
.. code-block:: pycon |
| 524 |
|
| 525 |
>>> Author.objects.annotate(average_rating=Avg("book__rating")) |
| 526 |
|
| 527 |
This will return one result for each author in the database, annotated with |
| 528 |
their average book rating. |
| 529 |
|
| 530 |
However, the result will be slightly different if you use a ``values()`` clause: |
| 531 |
|
| 532 |
.. code-block:: pycon |
| 533 |
|
| 534 |
>>> Author.objects.values("name").annotate(average_rating=Avg("book__rating")) |
| 535 |
|
| 536 |
In this example, the authors will be grouped by name, so you will only get |
| 537 |
an annotated result for each *unique* author name. This means if you have |
| 538 |
two authors with the same name, their results will be merged into a single |
| 539 |
result in the output of the query; the average will be computed as the |
| 540 |
average over the books written by both authors. |
| 541 |
|
| 542 |
Order of ``annotate()`` and ``values()`` clauses |
| 543 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 544 |
|
| 545 |
As with the ``filter()`` clause, the order in which ``annotate()`` and |
| 546 |
``values()`` clauses are applied to a query is significant. If the |
| 547 |
``values()`` clause precedes the ``annotate()``, the annotation will be |
| 548 |
computed using the grouping described by the ``values()`` clause. |
| 549 |
|
| 550 |
However, if the ``annotate()`` clause precedes the ``values()`` clause, |
| 551 |
the annotations will be generated over the entire query set. In this case, |
| 552 |
the ``values()`` clause only constrains the fields that are generated on |
| 553 |
output. |
| 554 |
|
| 555 |
For example, if we reverse the order of the ``values()`` and ``annotate()`` |
| 556 |
clause from our previous example: |
| 557 |
|
| 558 |
.. code-block:: pycon |
| 559 |
|
| 560 |
>>> Author.objects.annotate(average_rating=Avg("book__rating")).values( |
| 561 |
... "name", "average_rating" |
| 562 |
... ) |
| 563 |
|
| 564 |
This will now yield one unique result for each author; however, only |
| 565 |
the author's name and the ``average_rating`` annotation will be returned |
| 566 |
in the output data. |
| 567 |
|
| 568 |
You should also note that ``average_rating`` has been explicitly included |
| 569 |
in the list of values to be returned. This is required because of the |
| 570 |
ordering of the ``values()`` and ``annotate()`` clause. |
| 571 |
|
| 572 |
If the ``values()`` clause precedes the ``annotate()`` clause, any annotations |
| 573 |
will be automatically added to the result set. However, if the ``values()`` |
| 574 |
clause is applied after the ``annotate()`` clause, you need to explicitly |
| 575 |
include the aggregate column. |
| 576 |
|
| 577 |
.. _aggregation-ordering-interaction: |
| 578 |
|
| 579 |
Interaction with ``order_by()`` |
| 580 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 581 |
|
| 582 |
Fields that are mentioned in the ``order_by()`` part of a queryset are used |
| 583 |
when selecting the output data, even if they are not otherwise specified in the |
| 584 |
``values()`` call. These extra fields are used to group "like" results together |
| 585 |
and they can make otherwise identical result rows appear to be separate. This |
| 586 |
shows up, particularly, when counting things. |
| 587 |
|
| 588 |
By way of example, suppose you have a model like this:: |
| 589 |
|
| 590 |
from django.db import models |
| 591 |
|
| 592 |
|
| 593 |
class Item(models.Model): |
| 594 |
name = models.CharField(max_length=10) |
| 595 |
data = models.IntegerField() |
| 596 |
|
| 597 |
If you want to count how many times each distinct ``data`` value appears in an |
| 598 |
ordered queryset, you might try this:: |
| 599 |
|
| 600 |
items = Item.objects.order_by("name") |
| 601 |
# Warning: not quite correct! |
| 602 |
items.values("data").annotate(Count("id")) |
| 603 |
|
| 604 |
...which will group the ``Item`` objects by their common ``data`` values and |
| 605 |
then count the number of ``id`` values in each group. Except that it won't |
| 606 |
quite work. The ordering by ``name`` will also play a part in the grouping, so |
| 607 |
this query will group by distinct ``(data, name)`` pairs, which isn't what you |
| 608 |
want. Instead, you should construct this queryset:: |
| 609 |
|
| 610 |
items.values("data").annotate(Count("id")).order_by() |
| 611 |
|
| 612 |
...clearing any ordering in the query. You could also order by, say, ``data`` |
| 613 |
without any harmful effects, since that is already playing a role in the |
| 614 |
query. |
| 615 |
|
| 616 |
This behavior is the same as that noted in the queryset documentation for |
| 617 |
:meth:`~django.db.models.query.QuerySet.distinct` and the general rule is the |
| 618 |
same: normally you won't want extra columns playing a part in the result, so |
| 619 |
clear out the ordering, or at least make sure it's restricted only to those |
| 620 |
fields you also select in a ``values()`` call. |
| 621 |
|
| 622 |
.. note:: |
| 623 |
You might reasonably ask why Django doesn't remove the extraneous columns |
| 624 |
for you. The main reason is consistency with ``distinct()`` and other |
| 625 |
places: Django **never** removes ordering constraints that you have |
| 626 |
specified *explicitly with* ``order_by()`` (and we can't change those |
| 627 |
other methods' behavior, as that would violate our |
| 628 |
:doc:`/misc/api-stability` policy). |
| 629 |
|
| 630 |
.. admonition:: Default ordering not applied to GROUP BY |
| 631 |
|
| 632 |
``GROUP BY`` queries (for example, those using ``.values()`` and |
| 633 |
``.annotate()``) don't use the model's default ordering. |
| 634 |
Use ``order_by()`` explicitly when a given order is needed. |
| 635 |
|
| 636 |
Aggregating annotations |
| 637 |
----------------------- |
| 638 |
|
| 639 |
You can also generate an aggregate on the result of an annotation. When you |
| 640 |
define an ``aggregate()`` clause, the aggregates you provide can reference |
| 641 |
any alias defined as part of an ``annotate()`` clause in the query. |
| 642 |
|
| 643 |
For example, if you wanted to calculate the average number of authors per |
| 644 |
book you first annotate the set of books with the author count, then |
| 645 |
aggregate that author count, referencing the annotation field: |
| 646 |
|
| 647 |
.. code-block:: pycon |
| 648 |
|
| 649 |
>>> from django.db.models import Avg, Count |
| 650 |
>>> Book.objects.annotate(num_authors=Count("authors")).aggregate(Avg("num_authors")) |
| 651 |
{'num_authors__avg': 1.66} |
| 652 |
|
| 653 |
Aggregating on empty querysets or groups |
| 654 |
---------------------------------------- |
| 655 |
|
| 656 |
When an aggregation is applied to an empty queryset or grouping, the result |
| 657 |
defaults to its :ref:`default <aggregate-default>` parameter, typically |
| 658 |
``None``. This behavior occurs because aggregate functions return ``NULL`` when |
| 659 |
the executed query returns no rows. |
| 660 |
|
| 661 |
You can specify a return value by providing the :ref:`default |
| 662 |
<aggregate-default>` argument for most aggregations. However, since |
| 663 |
:class:`~django.db.models.Count` does not support the :ref:`default |
| 664 |
<aggregate-default>` argument, it will always return ``0`` for empty querysets |
| 665 |
or groups. |
| 666 |
|
| 667 |
For example, assuming that no book contains *web* in its name, calculating the |
| 668 |
total price for this book set would return ``None`` since there are no matching |
| 669 |
rows to compute the :class:`~django.db.models.Sum` aggregation on: |
| 670 |
|
| 671 |
.. code-block:: pycon |
| 672 |
|
| 673 |
>>> from django.db.models import Sum |
| 674 |
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price")) |
| 675 |
{"price__sum": None} |
| 676 |
|
| 677 |
However, the :ref:`default <aggregate-default>` argument can be set when |
| 678 |
calling :class:`~django.db.models.Sum` to return a different default value if |
| 679 |
no books can be found: |
| 680 |
|
| 681 |
.. code-block:: pycon |
| 682 |
|
| 683 |
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0)) |
| 684 |
{"price__sum": Decimal("0")} |
| 685 |
|
| 686 |
Under the hood, the :ref:`default <aggregate-default>` argument is implemented |
| 687 |
by wrapping the aggregate function with |
| 688 |
:class:`~django.db.models.functions.Coalesce`. |