Debian Codegraph @

tests/aggregation/tests.py

raw
1 import datetime
2 import math
3 import re
4 from decimal import Decimal
5 from itertools import chain
6
7 from django.core.exceptions import FieldError
8 from django.db import connection
9 from django.db.models import (
10 Avg,
11 Case,
12 Count,
13 DateField,
14 DateTimeField,
15 DecimalField,
16 DurationField,
17 Exists,
18 F,
19 FloatField,
20 IntegerField,
21 Max,
22 Min,
23 OuterRef,
24 Q,
25 StdDev,
26 Subquery,
27 Sum,
28 TimeField,
29 Transform,
30 Value,
31 Variance,
32 When,
33 Window,
34 )
35 from django.db.models.expressions import Func, RawSQL
36 from django.db.models.functions import (
37 Cast,
38 Coalesce,
39 Greatest,
40 Least,
41 Lower,
42 Mod,
43 Now,
44 Pi,
45 TruncDate,
46 TruncHour,
47 )
48 from django.test import TestCase
49 from django.test.testcases import skipUnlessDBFeature
50 from django.test.utils import Approximate, CaptureQueriesContext
51 from django.utils import timezone
52
53 from .models import Author, Book, Publisher, Store
54
55
56 class NowUTC(Now):
57 template = "CURRENT_TIMESTAMP"
58 output_field = DateTimeField()
59
60 def as_sql(self, compiler, connection, **extra_context):
61 if connection.features.test_now_utc_template:
62 extra_context["template"] = connection.features.test_now_utc_template
63 return super().as_sql(compiler, connection, **extra_context)
64
65
66 class AggregateTestCase(TestCase):
67 @classmethod
68 def setUpTestData(cls):
69 cls.a1 = Author.objects.create(name="Adrian Holovaty", age=34)
70 cls.a2 = Author.objects.create(name="Jacob Kaplan-Moss", age=35)
71 cls.a3 = Author.objects.create(name="Brad Dayley", age=45)
72 cls.a4 = Author.objects.create(name="James Bennett", age=29)
73 cls.a5 = Author.objects.create(name="Jeffrey Forcier", age=37)
74 cls.a6 = Author.objects.create(name="Paul Bissex", age=29)
75 cls.a7 = Author.objects.create(name="Wesley J. Chun", age=25)
76 cls.a8 = Author.objects.create(name="Peter Norvig", age=57)
77 cls.a9 = Author.objects.create(name="Stuart Russell", age=46)
78 cls.a1.friends.add(cls.a2, cls.a4)
79 cls.a2.friends.add(cls.a1, cls.a7)
80 cls.a4.friends.add(cls.a1)
81 cls.a5.friends.add(cls.a6, cls.a7)
82 cls.a6.friends.add(cls.a5, cls.a7)
83 cls.a7.friends.add(cls.a2, cls.a5, cls.a6)
84 cls.a8.friends.add(cls.a9)
85 cls.a9.friends.add(cls.a8)
86
87 cls.p1 = Publisher.objects.create(
88 name="Apress", num_awards=3, duration=datetime.timedelta(days=1)
89 )
90 cls.p2 = Publisher.objects.create(
91 name="Sams", num_awards=1, duration=datetime.timedelta(days=2)
92 )
93 cls.p3 = Publisher.objects.create(name="Prentice Hall", num_awards=7)
94 cls.p4 = Publisher.objects.create(name="Morgan Kaufmann", num_awards=9)
95 cls.p5 = Publisher.objects.create(name="Jonno's House of Books", num_awards=0)
96
97 cls.b1 = Book.objects.create(
98 isbn="159059725",
99 name="The Definitive Guide to Django: Web Development Done Right",
100 pages=447,
101 rating=4.5,
102 price=Decimal("30.00"),
103 contact=cls.a1,
104 publisher=cls.p1,
105 pubdate=datetime.date(2007, 12, 6),
106 )
107 cls.b2 = Book.objects.create(
108 isbn="067232959",
109 name="Sams Teach Yourself Django in 24 Hours",
110 pages=528,
111 rating=3.0,
112 price=Decimal("23.09"),
113 contact=cls.a3,
114 publisher=cls.p2,
115 pubdate=datetime.date(2008, 3, 3),
116 )
117 cls.b3 = Book.objects.create(
118 isbn="159059996",
119 name="Practical Django Projects",
120 pages=300,
121 rating=4.0,
122 price=Decimal("29.69"),
123 contact=cls.a4,
124 publisher=cls.p1,
125 pubdate=datetime.date(2008, 6, 23),
126 )
127 cls.b4 = Book.objects.create(
128 isbn="013235613",
129 name="Python Web Development with Django",
130 pages=350,
131 rating=4.0,
132 price=Decimal("29.69"),
133 contact=cls.a5,
134 publisher=cls.p3,
135 pubdate=datetime.date(2008, 11, 3),
136 )
137 cls.b5 = Book.objects.create(
138 isbn="013790395",
139 name="Artificial Intelligence: A Modern Approach",
140 pages=1132,
141 rating=4.0,
142 price=Decimal("82.80"),
143 contact=cls.a8,
144 publisher=cls.p3,
145 pubdate=datetime.date(1995, 1, 15),
146 )
147 cls.b6 = Book.objects.create(
148 isbn="155860191",
149 name=(
150 "Paradigms of Artificial Intelligence Programming: Case Studies in "
151 "Common Lisp"
152 ),
153 pages=946,
154 rating=5.0,
155 price=Decimal("75.00"),
156 contact=cls.a8,
157 publisher=cls.p4,
158 pubdate=datetime.date(1991, 10, 15),
159 )
160 cls.b1.authors.add(cls.a1, cls.a2)
161 cls.b2.authors.add(cls.a3)
162 cls.b3.authors.add(cls.a4)
163 cls.b4.authors.add(cls.a5, cls.a6, cls.a7)
164 cls.b5.authors.add(cls.a8, cls.a9)
165 cls.b6.authors.add(cls.a8)
166
167 s1 = Store.objects.create(
168 name="Amazon.com",
169 original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
170 friday_night_closing=datetime.time(23, 59, 59),
171 )
172 s2 = Store.objects.create(
173 name="Books.com",
174 original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
175 friday_night_closing=datetime.time(23, 59, 59),
176 )
177 s3 = Store.objects.create(
178 name="Mamma and Pappa's Books",
179 original_opening=datetime.datetime(1945, 4, 25, 16, 24, 14),
180 friday_night_closing=datetime.time(21, 30),
181 )
182 s1.books.add(cls.b1, cls.b2, cls.b3, cls.b4, cls.b5, cls.b6)
183 s2.books.add(cls.b1, cls.b3, cls.b5, cls.b6)
184 s3.books.add(cls.b3, cls.b4, cls.b6)
185
186 def test_empty_aggregate(self):
187 self.assertEqual(Author.objects.aggregate(), {})
188
189 def test_aggregate_in_order_by(self):
190 msg = (
191 "Using an aggregate in order_by() without also including it in "
192 "annotate() is not allowed: Avg(F(book__rating)"
193 )
194 with self.assertRaisesMessage(FieldError, msg):
195 Author.objects.values("age").order_by(Avg("book__rating"))
196
197 def test_single_aggregate(self):
198 vals = Author.objects.aggregate(Avg("age"))
199 self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)})
200
201 def test_multiple_aggregates(self):
202 vals = Author.objects.aggregate(Sum("age"), Avg("age"))
203 self.assertEqual(
204 vals, {"age__sum": 337, "age__avg": Approximate(37.4, places=1)}
205 )
206
207 def test_filter_aggregate(self):
208 vals = Author.objects.filter(age__gt=29).aggregate(Sum("age"))
209 self.assertEqual(vals, {"age__sum": 254})
210
211 def test_related_aggregate(self):
212 vals = Author.objects.aggregate(Avg("friends__age"))
213 self.assertEqual(vals, {"friends__age__avg": Approximate(34.07, places=2)})
214
215 vals = Book.objects.filter(rating__lt=4.5).aggregate(Avg("authors__age"))
216 self.assertEqual(vals, {"authors__age__avg": Approximate(38.2857, places=2)})
217
218 vals = Author.objects.filter(name__contains="a").aggregate(Avg("book__rating"))
219 self.assertEqual(vals, {"book__rating__avg": 4.0})
220
221 vals = Book.objects.aggregate(Sum("publisher__num_awards"))
222 self.assertEqual(vals, {"publisher__num_awards__sum": 30})
223
224 vals = Publisher.objects.aggregate(Sum("book__price"))
225 self.assertEqual(vals, {"book__price__sum": Decimal("270.27")})
226
227 def test_aggregate_multi_join(self):
228 vals = Store.objects.aggregate(Max("books__authors__age"))
229 self.assertEqual(vals, {"books__authors__age__max": 57})
230
231 vals = Author.objects.aggregate(Min("book__publisher__num_awards"))
232 self.assertEqual(vals, {"book__publisher__num_awards__min": 1})
233
234 def test_aggregate_alias(self):
235 vals = Store.objects.filter(name="Amazon.com").aggregate(
236 amazon_mean=Avg("books__rating")
237 )
238 self.assertEqual(vals, {"amazon_mean": Approximate(4.08, places=2)})
239
240 def test_aggregate_transform(self):
241 vals = Store.objects.aggregate(min_month=Min("original_opening__month"))
242 self.assertEqual(vals, {"min_month": 3})
243
244 def test_aggregate_join_transform(self):
245 vals = Publisher.objects.aggregate(min_year=Min("book__pubdate__year"))
246 self.assertEqual(vals, {"min_year": 1991})
247
248 def test_annotate_basic(self):
249 self.assertQuerySetEqual(
250 Book.objects.annotate().order_by("pk"),
251 [
252 "The Definitive Guide to Django: Web Development Done Right",
253 "Sams Teach Yourself Django in 24 Hours",
254 "Practical Django Projects",
255 "Python Web Development with Django",
256 "Artificial Intelligence: A Modern Approach",
257 "Paradigms of Artificial Intelligence Programming: Case Studies in "
258 "Common Lisp",
259 ],
260 lambda b: b.name,
261 )
262
263 books = Book.objects.annotate(mean_age=Avg("authors__age"))
264 b = books.get(pk=self.b1.pk)
265 self.assertEqual(
266 b.name, "The Definitive Guide to Django: Web Development Done Right"
267 )
268 self.assertEqual(b.mean_age, 34.5)
269
270 def test_annotate_defer(self):
271 qs = (
272 Book.objects.annotate(page_sum=Sum("pages"))
273 .defer("name")
274 .filter(pk=self.b1.pk)
275 )
276
277 rows = [
278 (
279 self.b1.id,
280 "159059725",
281 447,
282 "The Definitive Guide to Django: Web Development Done Right",
283 )
284 ]
285 self.assertQuerySetEqual(
286 qs.order_by("pk"), rows, lambda r: (r.id, r.isbn, r.page_sum, r.name)
287 )
288
289 def test_annotate_defer_select_related(self):
290 qs = (
291 Book.objects.select_related("contact")
292 .annotate(page_sum=Sum("pages"))
293 .defer("name")
294 .filter(pk=self.b1.pk)
295 )
296
297 rows = [
298 (
299 self.b1.id,
300 "159059725",
301 447,
302 "Adrian Holovaty",
303 "The Definitive Guide to Django: Web Development Done Right",
304 )
305 ]
306 self.assertQuerySetEqual(
307 qs.order_by("pk"),
308 rows,
309 lambda r: (r.id, r.isbn, r.page_sum, r.contact.name, r.name),
310 )
311
312 def test_annotate_m2m(self):
313 books = (
314 Book.objects.filter(rating__lt=4.5)
315 .annotate(Avg("authors__age"))
316 .order_by("name")
317 )
318 self.assertQuerySetEqual(
319 books,
320 [
321 ("Artificial Intelligence: A Modern Approach", 51.5),
322 ("Practical Django Projects", 29.0),
323 ("Python Web Development with Django", Approximate(30.3, places=1)),
324 ("Sams Teach Yourself Django in 24 Hours", 45.0),
325 ],
326 lambda b: (b.name, b.authors__age__avg),
327 )
328
329 books = Book.objects.annotate(num_authors=Count("authors")).order_by("name")
330 self.assertQuerySetEqual(
331 books,
332 [
333 ("Artificial Intelligence: A Modern Approach", 2),
334 (
335 "Paradigms of Artificial Intelligence Programming: Case Studies in "
336 "Common Lisp",
337 1,
338 ),
339 ("Practical Django Projects", 1),
340 ("Python Web Development with Django", 3),
341 ("Sams Teach Yourself Django in 24 Hours", 1),
342 ("The Definitive Guide to Django: Web Development Done Right", 2),
343 ],
344 lambda b: (b.name, b.num_authors),
345 )
346
347 def test_backwards_m2m_annotate(self):
348 authors = (
349 Author.objects.filter(name__contains="a")
350 .annotate(Avg("book__rating"))
351 .order_by("name")
352 )
353 self.assertQuerySetEqual(
354 authors,
355 [
356 ("Adrian Holovaty", 4.5),
357 ("Brad Dayley", 3.0),
358 ("Jacob Kaplan-Moss", 4.5),
359 ("James Bennett", 4.0),
360 ("Paul Bissex", 4.0),
361 ("Stuart Russell", 4.0),
362 ],
363 lambda a: (a.name, a.book__rating__avg),
364 )
365
366 authors = Author.objects.annotate(num_books=Count("book")).order_by("name")
367 self.assertQuerySetEqual(
368 authors,
369 [
370 ("Adrian Holovaty", 1),
371 ("Brad Dayley", 1),
372 ("Jacob Kaplan-Moss", 1),
373 ("James Bennett", 1),
374 ("Jeffrey Forcier", 1),
375 ("Paul Bissex", 1),
376 ("Peter Norvig", 2),
377 ("Stuart Russell", 1),
378 ("Wesley J. Chun", 1),
379 ],
380 lambda a: (a.name, a.num_books),
381 )
382
383 def test_reverse_fkey_annotate(self):
384 books = Book.objects.annotate(Sum("publisher__num_awards")).order_by("name")
385 self.assertQuerySetEqual(
386 books,
387 [
388 ("Artificial Intelligence: A Modern Approach", 7),
389 (
390 "Paradigms of Artificial Intelligence Programming: Case Studies in "
391 "Common Lisp",
392 9,
393 ),
394 ("Practical Django Projects", 3),
395 ("Python Web Development with Django", 7),
396 ("Sams Teach Yourself Django in 24 Hours", 1),
397 ("The Definitive Guide to Django: Web Development Done Right", 3),
398 ],
399 lambda b: (b.name, b.publisher__num_awards__sum),
400 )
401
402 publishers = Publisher.objects.annotate(Sum("book__price")).order_by("name")
403 self.assertQuerySetEqual(
404 publishers,
405 [
406 ("Apress", Decimal("59.69")),
407 ("Jonno's House of Books", None),
408 ("Morgan Kaufmann", Decimal("75.00")),
409 ("Prentice Hall", Decimal("112.49")),
410 ("Sams", Decimal("23.09")),
411 ],
412 lambda p: (p.name, p.book__price__sum),
413 )
414
415 def test_annotate_values(self):
416 books = list(
417 Book.objects.filter(pk=self.b1.pk)
418 .annotate(mean_age=Avg("authors__age"))
419 .values()
420 )
421 self.assertEqual(
422 books,
423 [
424 {
425 "contact_id": self.a1.id,
426 "id": self.b1.id,
427 "isbn": "159059725",
428 "mean_age": 34.5,
429 "name": (
430 "The Definitive Guide to Django: Web Development Done Right"
431 ),
432 "pages": 447,
433 "price": Approximate(Decimal("30")),
434 "pubdate": datetime.date(2007, 12, 6),
435 "publisher_id": self.p1.id,
436 "rating": 4.5,
437 }
438 ],
439 )
440
441 books = (
442 Book.objects.filter(pk=self.b1.pk)
443 .annotate(mean_age=Avg("authors__age"))
444 .values("pk", "isbn", "mean_age")
445 )
446 self.assertEqual(
447 list(books),
448 [
449 {
450 "pk": self.b1.pk,
451 "isbn": "159059725",
452 "mean_age": 34.5,
453 }
454 ],
455 )
456
457 books = (
458 Book.objects.filter(pk=self.b1.pk)
459 .annotate(mean_age=Avg("authors__age"))
460 .values("name")
461 )
462 self.assertEqual(
463 list(books),
464 [{"name": "The Definitive Guide to Django: Web Development Done Right"}],
465 )
466
467 books = (
468 Book.objects.filter(pk=self.b1.pk)
469 .values()
470 .annotate(mean_age=Avg("authors__age"))
471 )
472 self.assertEqual(
473 list(books),
474 [
475 {
476 "contact_id": self.a1.id,
477 "id": self.b1.id,
478 "isbn": "159059725",
479 "mean_age": 34.5,
480 "name": (
481 "The Definitive Guide to Django: Web Development Done Right"
482 ),
483 "pages": 447,
484 "price": Approximate(Decimal("30")),
485 "pubdate": datetime.date(2007, 12, 6),
486 "publisher_id": self.p1.id,
487 "rating": 4.5,
488 }
489 ],
490 )
491
492 books = (
493 Book.objects.values("rating")
494 .annotate(n_authors=Count("authors__id"), mean_age=Avg("authors__age"))
495 .order_by("rating")
496 )
497 self.assertEqual(
498 list(books),
499 [
500 {
501 "rating": 3.0,
502 "n_authors": 1,
503 "mean_age": 45.0,
504 },
505 {
506 "rating": 4.0,
507 "n_authors": 6,
508 "mean_age": Approximate(37.16, places=1),
509 },
510 {
511 "rating": 4.5,
512 "n_authors": 2,
513 "mean_age": 34.5,
514 },
515 {
516 "rating": 5.0,
517 "n_authors": 1,
518 "mean_age": 57.0,
519 },
520 ],
521 )
522
523 authors = Author.objects.annotate(Avg("friends__age")).order_by("name")
524 self.assertQuerySetEqual(
525 authors,
526 [
527 ("Adrian Holovaty", 32.0),
528 ("Brad Dayley", None),
529 ("Jacob Kaplan-Moss", 29.5),
530 ("James Bennett", 34.0),
531 ("Jeffrey Forcier", 27.0),
532 ("Paul Bissex", 31.0),
533 ("Peter Norvig", 46.0),
534 ("Stuart Russell", 57.0),
535 ("Wesley J. Chun", Approximate(33.66, places=1)),
536 ],
537 lambda a: (a.name, a.friends__age__avg),
538 )
539
540 def test_count(self):
541 vals = Book.objects.aggregate(Count("rating"))
542 self.assertEqual(vals, {"rating__count": 6})
543
544 def test_count_star(self):
545 with self.assertNumQueries(1) as ctx:
546 Book.objects.aggregate(n=Count("*"))
547 sql = ctx.captured_queries[0]["sql"]
548 self.assertIn("SELECT COUNT(*) ", sql)
549
550 def test_count_distinct_expression(self):
551 aggs = Book.objects.aggregate(
552 distinct_ratings=Count(
553 Case(When(pages__gt=300, then="rating")), distinct=True
554 ),
555 )
556 self.assertEqual(aggs["distinct_ratings"], 4)
557
558 def test_distinct_on_aggregate(self):
559 for aggregate, expected_result in (
560 (Avg, 4.125),
561 (Count, 4),
562 (Sum, 16.5),
563 ):
564 with self.subTest(aggregate=aggregate.__name__):
565 books = Book.objects.aggregate(
566 ratings=aggregate("rating", distinct=True)
567 )
568 self.assertEqual(books["ratings"], expected_result)
569
570 def test_non_grouped_annotation_not_in_group_by(self):
571 """
572 An annotation not included in values() before an aggregate should be
573 excluded from the group by clause.
574 """
575 qs = (
576 Book.objects.annotate(xprice=F("price"))
577 .filter(rating=4.0)
578 .values("rating")
579 .annotate(count=Count("publisher_id", distinct=True))
580 .values("count", "rating")
581 .order_by("count")
582 )
583 self.assertEqual(list(qs), [{"rating": 4.0, "count": 2}])
584
585 def test_grouped_annotation_in_group_by(self):
586 """
587 An annotation included in values() before an aggregate should be
588 included in the group by clause.
589 """
590 qs = (
591 Book.objects.annotate(xprice=F("price"))
592 .filter(rating=4.0)
593 .values("rating", "xprice")
594 .annotate(count=Count("publisher_id", distinct=True))
595 .values("count", "rating")
596 .order_by("count")
597 )
598 self.assertEqual(
599 list(qs),
600 [
601 {"rating": 4.0, "count": 1},
602 {"rating": 4.0, "count": 2},
603 ],
604 )
605
606 def test_fkey_aggregate(self):
607 explicit = list(Author.objects.annotate(Count("book__id")))
608 implicit = list(Author.objects.annotate(Count("book")))
609 self.assertCountEqual(explicit, implicit)
610
611 def test_annotate_ordering(self):
612 books = (
613 Book.objects.values("rating")
614 .annotate(oldest=Max("authors__age"))
615 .order_by("oldest", "rating")
616 )
617 self.assertEqual(
618 list(books),
619 [
620 {"rating": 4.5, "oldest": 35},
621 {"rating": 3.0, "oldest": 45},
622 {"rating": 4.0, "oldest": 57},
623 {"rating": 5.0, "oldest": 57},
624 ],
625 )
626
627 books = (
628 Book.objects.values("rating")
629 .annotate(oldest=Max("authors__age"))
630 .order_by("-oldest", "-rating")
631 )
632 self.assertEqual(
633 list(books),
634 [
635 {"rating": 5.0, "oldest": 57},
636 {"rating": 4.0, "oldest": 57},
637 {"rating": 3.0, "oldest": 45},
638 {"rating": 4.5, "oldest": 35},
639 ],
640 )
641
642 def test_aggregate_annotation(self):
643 vals = Book.objects.annotate(num_authors=Count("authors__id")).aggregate(
644 Avg("num_authors")
645 )
646 self.assertEqual(vals, {"num_authors__avg": Approximate(1.66, places=1)})
647
648 def test_avg_duration_field(self):
649 # Explicit `output_field`.
650 self.assertEqual(
651 Publisher.objects.aggregate(Avg("duration", output_field=DurationField())),
652 {"duration__avg": datetime.timedelta(days=1, hours=12)},
653 )
654 # Implicit `output_field`.
655 self.assertEqual(
656 Publisher.objects.aggregate(Avg("duration")),
657 {"duration__avg": datetime.timedelta(days=1, hours=12)},
658 )
659
660 def test_sum_duration_field(self):
661 self.assertEqual(
662 Publisher.objects.aggregate(Sum("duration", output_field=DurationField())),
663 {"duration__sum": datetime.timedelta(days=3)},
664 )
665
666 def test_sum_distinct_aggregate(self):
667 """
668 Sum on a distinct() QuerySet should aggregate only the distinct items.
669 """
670 authors = Author.objects.filter(book__in=[self.b5, self.b6])
671 self.assertEqual(authors.count(), 3)
672
673 distinct_authors = authors.distinct()
674 self.assertEqual(distinct_authors.count(), 2)
675
676 # Selected author ages are 57 and 46
677 age_sum = distinct_authors.aggregate(Sum("age"))
678 self.assertEqual(age_sum["age__sum"], 103)
679
680 def test_filtering(self):
681 p = Publisher.objects.create(name="Expensive Publisher", num_awards=0)
682 Book.objects.create(
683 name="ExpensiveBook1",
684 pages=1,
685 isbn="111",
686 rating=3.5,
687 price=Decimal("1000"),
688 publisher=p,
689 contact_id=self.a1.id,
690 pubdate=datetime.date(2008, 12, 1),
691 )
692 Book.objects.create(
693 name="ExpensiveBook2",
694 pages=1,
695 isbn="222",
696 rating=4.0,
697 price=Decimal("1000"),
698 publisher=p,
699 contact_id=self.a1.id,
700 pubdate=datetime.date(2008, 12, 2),
701 )
702 Book.objects.create(
703 name="ExpensiveBook3",
704 pages=1,
705 isbn="333",
706 rating=4.5,
707 price=Decimal("35"),
708 publisher=p,
709 contact_id=self.a1.id,
710 pubdate=datetime.date(2008, 12, 3),
711 )
712
713 publishers = (
714 Publisher.objects.annotate(num_books=Count("book__id"))
715 .filter(num_books__gt=1)
716 .order_by("pk")
717 )
718 self.assertQuerySetEqual(
719 publishers,
720 ["Apress", "Prentice Hall", "Expensive Publisher"],
721 lambda p: p.name,
722 )
723
724 publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).order_by(
725 "pk"
726 )
727 self.assertQuerySetEqual(
728 publishers,
729 [
730 "Apress",
731 "Apress",
732 "Sams",
733 "Prentice Hall",
734 "Expensive Publisher",
735 ],
736 lambda p: p.name,
737 )
738
739 publishers = (
740 Publisher.objects.annotate(num_books=Count("book__id"))
741 .filter(num_books__gt=1, book__price__lt=Decimal("40.0"))
742 .order_by("pk")
743 )
744 self.assertQuerySetEqual(
745 publishers,
746 ["Apress", "Prentice Hall", "Expensive Publisher"],
747 lambda p: p.name,
748 )
749
750 publishers = (
751 Publisher.objects.filter(book__price__lt=Decimal("40.0"))
752 .annotate(num_books=Count("book__id"))
753 .filter(num_books__gt=1)
754 .order_by("pk")
755 )
756 self.assertQuerySetEqual(publishers, ["Apress"], lambda p: p.name)
757
758 publishers = (
759 Publisher.objects.annotate(num_books=Count("book"))
760 .filter(num_books__range=[1, 3])
761 .order_by("pk")
762 )
763 self.assertQuerySetEqual(
764 publishers,
765 [
766 "Apress",
767 "Sams",
768 "Prentice Hall",
769 "Morgan Kaufmann",
770 "Expensive Publisher",
771 ],
772 lambda p: p.name,
773 )
774
775 publishers = (
776 Publisher.objects.annotate(num_books=Count("book"))
777 .filter(num_books__range=[1, 2])
778 .order_by("pk")
779 )
780 self.assertQuerySetEqual(
781 publishers,
782 ["Apress", "Sams", "Prentice Hall", "Morgan Kaufmann"],
783 lambda p: p.name,
784 )
785
786 publishers = (
787 Publisher.objects.annotate(num_books=Count("book"))
788 .filter(num_books__in=[1, 3])
789 .order_by("pk")
790 )
791 self.assertQuerySetEqual(
792 publishers,
793 ["Sams", "Morgan Kaufmann", "Expensive Publisher"],
794 lambda p: p.name,
795 )
796
797 publishers = Publisher.objects.annotate(num_books=Count("book")).filter(
798 num_books__isnull=True
799 )
800 self.assertEqual(len(publishers), 0)
801
802 def test_annotation(self):
803 vals = Author.objects.filter(pk=self.a1.pk).aggregate(Count("friends__id"))
804 self.assertEqual(vals, {"friends__id__count": 2})
805
806 books = (
807 Book.objects.annotate(num_authors=Count("authors__name"))
808 .filter(num_authors__exact=2)
809 .order_by("pk")
810 )
811 self.assertQuerySetEqual(
812 books,
813 [
814 "The Definitive Guide to Django: Web Development Done Right",
815 "Artificial Intelligence: A Modern Approach",
816 ],
817 lambda b: b.name,
818 )
819
820 authors = (
821 Author.objects.annotate(num_friends=Count("friends__id", distinct=True))
822 .filter(num_friends=0)
823 .order_by("pk")
824 )
825 self.assertQuerySetEqual(authors, ["Brad Dayley"], lambda a: a.name)
826
827 publishers = (
828 Publisher.objects.annotate(num_books=Count("book__id"))
829 .filter(num_books__gt=1)
830 .order_by("pk")
831 )
832 self.assertQuerySetEqual(
833 publishers, ["Apress", "Prentice Hall"], lambda p: p.name
834 )
835
836 publishers = (
837 Publisher.objects.filter(book__price__lt=Decimal("40.0"))
838 .annotate(num_books=Count("book__id"))
839 .filter(num_books__gt=1)
840 )
841 self.assertQuerySetEqual(publishers, ["Apress"], lambda p: p.name)
842
843 books = Book.objects.annotate(num_authors=Count("authors__id")).filter(
844 authors__name__contains="Norvig", num_authors__gt=1
845 )
846 self.assertQuerySetEqual(
847 books, ["Artificial Intelligence: A Modern Approach"], lambda b: b.name
848 )
849
850 def test_more_aggregation(self):
851 a = Author.objects.get(name__contains="Norvig")
852 b = Book.objects.get(name__contains="Done Right")
853 b.authors.add(a)
854 b.save()
855
856 vals = (
857 Book.objects.annotate(num_authors=Count("authors__id"))
858 .filter(authors__name__contains="Norvig", num_authors__gt=1)
859 .aggregate(Avg("rating"))
860 )
861 self.assertEqual(vals, {"rating__avg": 4.25})
862
863 def test_even_more_aggregate(self):
864 publishers = (
865 Publisher.objects.annotate(
866 earliest_book=Min("book__pubdate"),
867 )
868 .exclude(earliest_book=None)
869 .order_by("earliest_book")
870 .values(
871 "earliest_book",
872 "num_awards",
873 "id",
874 "name",
875 )
876 )
877 self.assertEqual(
878 list(publishers),
879 [
880 {
881 "earliest_book": datetime.date(1991, 10, 15),
882 "num_awards": 9,
883 "id": self.p4.id,
884 "name": "Morgan Kaufmann",
885 },
886 {
887 "earliest_book": datetime.date(1995, 1, 15),
888 "num_awards": 7,
889 "id": self.p3.id,
890 "name": "Prentice Hall",
891 },
892 {
893 "earliest_book": datetime.date(2007, 12, 6),
894 "num_awards": 3,
895 "id": self.p1.id,
896 "name": "Apress",
897 },
898 {
899 "earliest_book": datetime.date(2008, 3, 3),
900 "num_awards": 1,
901 "id": self.p2.id,
902 "name": "Sams",
903 },
904 ],
905 )
906
907 vals = Store.objects.aggregate(
908 Max("friday_night_closing"), Min("original_opening")
909 )
910 self.assertEqual(
911 vals,
912 {
913 "friday_night_closing__max": datetime.time(23, 59, 59),
914 "original_opening__min": datetime.datetime(1945, 4, 25, 16, 24, 14),
915 },
916 )
917
918 def test_annotate_values_list(self):
919 books = (
920 Book.objects.filter(pk=self.b1.pk)
921 .annotate(mean_age=Avg("authors__age"))
922 .values_list("pk", "isbn", "mean_age")
923 )
924 self.assertEqual(list(books), [(self.b1.id, "159059725", 34.5)])
925
926 books = (
927 Book.objects.filter(pk=self.b1.pk)
928 .annotate(mean_age=Avg("authors__age"))
929 .values_list("isbn")
930 )
931 self.assertEqual(list(books), [("159059725",)])
932
933 books = (
934 Book.objects.filter(pk=self.b1.pk)
935 .annotate(mean_age=Avg("authors__age"))
936 .values_list("mean_age")
937 )
938 self.assertEqual(list(books), [(34.5,)])
939
940 books = (
941 Book.objects.filter(pk=self.b1.pk)
942 .annotate(mean_age=Avg("authors__age"))
943 .values_list("mean_age", flat=True)
944 )
945 self.assertEqual(list(books), [34.5])
946
947 books = (
948 Book.objects.values_list("price")
949 .annotate(count=Count("price"))
950 .order_by("-count", "price")
951 )
952 self.assertEqual(
953 list(books),
954 [
955 (Decimal("29.69"), 2),
956 (Decimal("23.09"), 1),
957 (Decimal("30"), 1),
958 (Decimal("75"), 1),
959 (Decimal("82.8"), 1),
960 ],
961 )
962
963 def test_dates_with_aggregation(self):
964 """
965 .dates() returns a distinct set of dates when applied to a
966 QuerySet with aggregation.
967
968 Refs #18056. Previously, .dates() would return distinct (date_kind,
969 aggregation) sets, in this case (year, num_authors), so 2008 would be
970 returned twice because there are books from 2008 with a different
971 number of authors.
972 """
973 dates = Book.objects.annotate(num_authors=Count("authors")).dates(
974 "pubdate", "year"
975 )
976 self.assertSequenceEqual(
977 dates,
978 [
979 datetime.date(1991, 1, 1),
980 datetime.date(1995, 1, 1),
981 datetime.date(2007, 1, 1),
982 datetime.date(2008, 1, 1),
983 ],
984 )
985
986 def test_values_aggregation(self):
987 # Refs #20782
988 max_rating = Book.objects.values("rating").aggregate(max_rating=Max("rating"))
989 self.assertEqual(max_rating["max_rating"], 5)
990 max_books_per_rating = (
991 Book.objects.values("rating")
992 .annotate(books_per_rating=Count("id"))
993 .aggregate(Max("books_per_rating"))
994 )
995 self.assertEqual(max_books_per_rating, {"books_per_rating__max": 3})
996
997 def test_ticket17424(self):
998 """
999 Doing exclude() on a foreign model after annotate() doesn't crash.
1000 """
1001 all_books = list(Book.objects.values_list("pk", flat=True).order_by("pk"))
1002 annotated_books = Book.objects.order_by("pk").annotate(one=Count("id"))
1003
1004 # The value doesn't matter, we just need any negative
1005 # constraint on a related model that's a noop.
1006 excluded_books = annotated_books.exclude(publisher__name="__UNLIKELY_VALUE__")
1007
1008 # Try to generate query tree
1009 str(excluded_books.query)
1010
1011 self.assertQuerySetEqual(excluded_books, all_books, lambda x: x.pk)
1012
1013 # Check internal state
1014 self.assertIsNone(annotated_books.query.alias_map["aggregation_book"].join_type)
1015 self.assertIsNone(excluded_books.query.alias_map["aggregation_book"].join_type)
1016
1017 def test_ticket12886(self):
1018 """
1019 Aggregation over sliced queryset works correctly.
1020 """
1021 qs = Book.objects.order_by("-rating")[0:3]
1022 vals = qs.aggregate(average_top3_rating=Avg("rating"))["average_top3_rating"]
1023 self.assertAlmostEqual(vals, 4.5, places=2)
1024
1025 def test_ticket11881(self):
1026 """
1027 Subqueries do not needlessly contain ORDER BY, SELECT FOR UPDATE or
1028 select_related() stuff.
1029 """
1030 qs = (
1031 Book.objects.select_for_update()
1032 .order_by("pk")
1033 .select_related("publisher")
1034 .annotate(max_pk=Max("pk"))
1035 )
1036 with CaptureQueriesContext(connection) as captured_queries:
1037 qs.aggregate(avg_pk=Avg("max_pk"))
1038 self.assertEqual(len(captured_queries), 1)
1039 qstr = captured_queries[0]["sql"].lower()
1040 self.assertNotIn("for update", qstr)
1041 forced_ordering = connection.ops.force_no_ordering()
1042 if forced_ordering:
1043 # If the backend needs to force an ordering we make sure it's
1044 # the only "ORDER BY" clause present in the query.
1045 self.assertEqual(
1046 re.findall(r"order by (\w+)", qstr),
1047 [", ".join(f[1][0] for f in forced_ordering).lower()],
1048 )
1049 else:
1050 self.assertNotIn("order by", qstr)
1051 self.assertEqual(qstr.count(" join "), 0)
1052
1053 def test_decimal_max_digits_has_no_effect(self):
1054 Book.objects.all().delete()
1055 a1 = Author.objects.first()
1056 p1 = Publisher.objects.first()
1057 thedate = timezone.now()
1058 for i in range(10):
1059 Book.objects.create(
1060 isbn="abcde{}".format(i),
1061 name="none",
1062 pages=10,
1063 rating=4.0,
1064 price=9999.98,
1065 contact=a1,
1066 publisher=p1,
1067 pubdate=thedate,
1068 )
1069
1070 book = Book.objects.aggregate(price_sum=Sum("price"))
1071 self.assertEqual(book["price_sum"], Decimal("99999.80"))
1072
1073 def test_nonaggregate_aggregation_throws(self):
1074 with self.assertRaisesMessage(TypeError, "fail is not an aggregate expression"):
1075 Book.objects.aggregate(fail=F("price"))
1076
1077 def test_nonfield_annotation(self):
1078 book = Book.objects.annotate(val=Max(Value(2))).first()
1079 self.assertEqual(book.val, 2)
1080 book = Book.objects.annotate(
1081 val=Max(Value(2), output_field=IntegerField())
1082 ).first()
1083 self.assertEqual(book.val, 2)
1084 book = Book.objects.annotate(val=Max(2, output_field=IntegerField())).first()
1085 self.assertEqual(book.val, 2)
1086
1087 def test_annotation_expressions(self):
1088 authors = Author.objects.annotate(
1089 combined_ages=Sum(F("age") + F("friends__age"))
1090 ).order_by("name")
1091 authors2 = Author.objects.annotate(
1092 combined_ages=Sum("age") + Sum("friends__age")
1093 ).order_by("name")
1094 for qs in (authors, authors2):
1095 self.assertQuerySetEqual(
1096 qs,
1097 [
1098 ("Adrian Holovaty", 132),
1099 ("Brad Dayley", None),
1100 ("Jacob Kaplan-Moss", 129),
1101 ("James Bennett", 63),
1102 ("Jeffrey Forcier", 128),
1103 ("Paul Bissex", 120),
1104 ("Peter Norvig", 103),
1105 ("Stuart Russell", 103),
1106 ("Wesley J. Chun", 176),
1107 ],
1108 lambda a: (a.name, a.combined_ages),
1109 )
1110
1111 def test_aggregation_expressions(self):
1112 a1 = Author.objects.aggregate(av_age=Sum("age") / Count("*"))
1113 a2 = Author.objects.aggregate(av_age=Sum("age") / Count("age"))
1114 a3 = Author.objects.aggregate(av_age=Avg("age"))
1115 self.assertEqual(a1, {"av_age": 37})
1116 self.assertEqual(a2, {"av_age": 37})
1117 self.assertEqual(a3, {"av_age": Approximate(37.4, places=1)})
1118
1119 def test_avg_decimal_field(self):
1120 v = Book.objects.filter(rating=4).aggregate(avg_price=(Avg("price")))[
1121 "avg_price"
1122 ]
1123 self.assertIsInstance(v, Decimal)
1124 self.assertEqual(v, Approximate(Decimal("47.39"), places=2))
1125
1126 def test_order_of_precedence(self):
1127 p1 = Book.objects.filter(rating=4).aggregate(avg_price=(Avg("price") + 2) * 3)
1128 self.assertEqual(p1, {"avg_price": Approximate(Decimal("148.18"), places=2)})
1129
1130 p2 = Book.objects.filter(rating=4).aggregate(avg_price=Avg("price") + 2 * 3)
1131 self.assertEqual(p2, {"avg_price": Approximate(Decimal("53.39"), places=2)})
1132
1133 def test_combine_different_types(self):
1134 msg = (
1135 "Cannot infer type of '+' expression involving these types: FloatField, "
1136 "DecimalField. You must set output_field."
1137 )
1138 qs = Book.objects.annotate(sums=Sum("rating") + Sum("pages") + Sum("price"))
1139 with self.assertRaisesMessage(FieldError, msg):
1140 qs.first()
1141 with self.assertRaisesMessage(FieldError, msg):
1142 qs.first()
1143
1144 b1 = Book.objects.annotate(
1145 sums=Sum(F("rating") + F("pages") + F("price"), output_field=IntegerField())
1146 ).get(pk=self.b4.pk)
1147 self.assertEqual(b1.sums, 383)
1148
1149 b2 = Book.objects.annotate(
1150 sums=Sum(F("rating") + F("pages") + F("price"), output_field=FloatField())
1151 ).get(pk=self.b4.pk)
1152 self.assertEqual(b2.sums, 383.69)
1153
1154 b3 = Book.objects.annotate(
1155 sums=Sum(F("rating") + F("pages") + F("price"), output_field=DecimalField())
1156 ).get(pk=self.b4.pk)
1157 self.assertEqual(b3.sums, Approximate(Decimal("383.69"), places=2))
1158
1159 def test_complex_aggregations_require_kwarg(self):
1160 with self.assertRaisesMessage(
1161 TypeError, "Complex annotations require an alias"
1162 ):
1163 Author.objects.annotate(Sum(F("age") + F("friends__age")))
1164 with self.assertRaisesMessage(TypeError, "Complex aggregates require an alias"):
1165 Author.objects.aggregate(Sum("age") / Count("age"))
1166 with self.assertRaisesMessage(TypeError, "Complex aggregates require an alias"):
1167 Author.objects.aggregate(Sum(1))
1168
1169 def test_aggregate_over_complex_annotation(self):
1170 qs = Author.objects.annotate(combined_ages=Sum(F("age") + F("friends__age")))
1171
1172 age = qs.aggregate(max_combined_age=Max("combined_ages"))
1173 self.assertEqual(age["max_combined_age"], 176)
1174
1175 age = qs.aggregate(max_combined_age_doubled=Max("combined_ages") * 2)
1176 self.assertEqual(age["max_combined_age_doubled"], 176 * 2)
1177
1178 age = qs.aggregate(
1179 max_combined_age_doubled=Max("combined_ages") + Max("combined_ages")
1180 )
1181 self.assertEqual(age["max_combined_age_doubled"], 176 * 2)
1182
1183 age = qs.aggregate(
1184 max_combined_age_doubled=Max("combined_ages") + Max("combined_ages"),
1185 sum_combined_age=Sum("combined_ages"),
1186 )
1187 self.assertEqual(age["max_combined_age_doubled"], 176 * 2)
1188 self.assertEqual(age["sum_combined_age"], 954)
1189
1190 age = qs.aggregate(
1191 max_combined_age_doubled=Max("combined_ages") + Max("combined_ages"),
1192 sum_combined_age_doubled=Sum("combined_ages") + Sum("combined_ages"),
1193 )
1194 self.assertEqual(age["max_combined_age_doubled"], 176 * 2)
1195 self.assertEqual(age["sum_combined_age_doubled"], 954 * 2)
1196
1197 def test_values_annotation_with_expression(self):
1198 # ensure the F() is promoted to the group by clause
1199 qs = Author.objects.values("name").annotate(another_age=Sum("age") + F("age"))
1200 a = qs.get(name="Adrian Holovaty")
1201 self.assertEqual(a["another_age"], 68)
1202
1203 qs = qs.annotate(friend_count=Count("friends"))
1204 a = qs.get(name="Adrian Holovaty")
1205 self.assertEqual(a["friend_count"], 2)
1206
1207 qs = (
1208 qs.annotate(combined_age=Sum("age") + F("friends__age"))
1209 .filter(name="Adrian Holovaty")
1210 .order_by("-combined_age")
1211 )
1212 self.assertEqual(
1213 list(qs),
1214 [
1215 {
1216 "name": "Adrian Holovaty",
1217 "another_age": 68,
1218 "friend_count": 1,
1219 "combined_age": 69,
1220 },
1221 {
1222 "name": "Adrian Holovaty",
1223 "another_age": 68,
1224 "friend_count": 1,
1225 "combined_age": 63,
1226 },
1227 ],
1228 )
1229
1230 vals = qs.values("name", "combined_age")
1231 self.assertEqual(
1232 list(vals),
1233 [
1234 {"name": "Adrian Holovaty", "combined_age": 69},
1235 {"name": "Adrian Holovaty", "combined_age": 63},
1236 ],
1237 )
1238
1239 def test_annotate_values_aggregate(self):
1240 alias_age = (
1241 Author.objects.annotate(age_alias=F("age"))
1242 .values(
1243 "age_alias",
1244 )
1245 .aggregate(sum_age=Sum("age_alias"))
1246 )
1247
1248 age = Author.objects.values("age").aggregate(sum_age=Sum("age"))
1249
1250 self.assertEqual(alias_age["sum_age"], age["sum_age"])
1251
1252 def test_annotate_over_annotate(self):
1253 author = (
1254 Author.objects.annotate(age_alias=F("age"))
1255 .annotate(sum_age=Sum("age_alias"))
1256 .get(name="Adrian Holovaty")
1257 )
1258
1259 other_author = Author.objects.annotate(sum_age=Sum("age")).get(
1260 name="Adrian Holovaty"
1261 )
1262
1263 self.assertEqual(author.sum_age, other_author.sum_age)
1264
1265 def test_aggregate_over_aggregate(self):
1266 msg = "Cannot compute Avg('age_agg'): 'age_agg' is an aggregate"
1267 with self.assertRaisesMessage(FieldError, msg):
1268 Author.objects.aggregate(
1269 age_agg=Sum(F("age")),
1270 avg_age=Avg(F("age_agg")),
1271 )
1272
1273 def test_annotated_aggregate_over_annotated_aggregate(self):
1274 with self.assertRaisesMessage(
1275 FieldError, "Cannot compute Sum('id__max'): 'id__max' is an aggregate"
1276 ):
1277 Book.objects.annotate(Max("id")).annotate(Sum("id__max"))
1278
1279 class MyMax(Max):
1280 arity = None
1281
1282 def as_sql(self, compiler, connection):
1283 self.set_source_expressions(self.get_source_expressions()[0:1])
1284 return super().as_sql(compiler, connection)
1285
1286 with self.assertRaisesMessage(
1287 FieldError, "Cannot compute Max('id__max'): 'id__max' is an aggregate"
1288 ):
1289 Book.objects.annotate(Max("id")).annotate(my_max=MyMax("id__max", "price"))
1290
1291 def test_multi_arg_aggregate(self):
1292 class MyMax(Max):
1293 output_field = DecimalField()
1294 arity = None
1295
1296 def as_sql(self, compiler, connection):
1297 copy = self.copy()
1298 copy.set_source_expressions(copy.get_source_expressions()[0:1] + [None])
1299 return super(MyMax, copy).as_sql(compiler, connection)
1300
1301 with self.assertRaisesMessage(TypeError, "Complex aggregates require an alias"):
1302 Book.objects.aggregate(MyMax("pages", "price"))
1303
1304 with self.assertRaisesMessage(
1305 TypeError, "Complex annotations require an alias"
1306 ):
1307 Book.objects.annotate(MyMax("pages", "price"))
1308
1309 Book.objects.aggregate(max_field=MyMax("pages", "price"))
1310
1311 def test_add_implementation(self):
1312 class MySum(Sum):
1313 pass
1314
1315 # test completely changing how the output is rendered
1316 def lower_case_function_override(self, compiler, connection):
1317 sql, params = compiler.compile(self.source_expressions[0])
1318 substitutions = {
1319 "function": self.function.lower(),
1320 "expressions": sql,
1321 "distinct": "",
1322 }
1323 substitutions.update(self.extra)
1324 return self.template % substitutions, params
1325
1326 setattr(MySum, "as_" + connection.vendor, lower_case_function_override)
1327
1328 qs = Book.objects.annotate(
1329 sums=MySum(
1330 F("rating") + F("pages") + F("price"), output_field=IntegerField()
1331 )
1332 )
1333 self.assertEqual(str(qs.query).count("sum("), 1)
1334 b1 = qs.get(pk=self.b4.pk)
1335 self.assertEqual(b1.sums, 383)
1336
1337 # test changing the dict and delegating
1338 def lower_case_function_super(self, compiler, connection):
1339 self.extra["function"] = self.function.lower()
1340 return super(MySum, self).as_sql(compiler, connection)
1341
1342 setattr(MySum, "as_" + connection.vendor, lower_case_function_super)
1343
1344 qs = Book.objects.annotate(
1345 sums=MySum(
1346 F("rating") + F("pages") + F("price"), output_field=IntegerField()
1347 )
1348 )
1349 self.assertEqual(str(qs.query).count("sum("), 1)
1350 b1 = qs.get(pk=self.b4.pk)
1351 self.assertEqual(b1.sums, 383)
1352
1353 # test overriding all parts of the template
1354 def be_evil(self, compiler, connection):
1355 substitutions = {"function": "MAX", "expressions": "2", "distinct": ""}
1356 substitutions.update(self.extra)
1357 return self.template % substitutions, ()
1358
1359 setattr(MySum, "as_" + connection.vendor, be_evil)
1360
1361 qs = Book.objects.annotate(
1362 sums=MySum(
1363 F("rating") + F("pages") + F("price"), output_field=IntegerField()
1364 )
1365 )
1366 self.assertEqual(str(qs.query).count("MAX("), 1)
1367 b1 = qs.get(pk=self.b4.pk)
1368 self.assertEqual(b1.sums, 2)
1369
1370 def test_complex_values_aggregation(self):
1371 max_rating = Book.objects.values("rating").aggregate(
1372 double_max_rating=Max("rating") + Max("rating")
1373 )
1374 self.assertEqual(max_rating["double_max_rating"], 5 * 2)
1375
1376 max_books_per_rating = (
1377 Book.objects.values("rating")
1378 .annotate(books_per_rating=Count("id") + 5)
1379 .aggregate(Max("books_per_rating"))
1380 )
1381 self.assertEqual(max_books_per_rating, {"books_per_rating__max": 3 + 5})
1382
1383 def test_expression_on_aggregation(self):
1384 qs = (
1385 Publisher.objects.annotate(
1386 price_or_median=Greatest(
1387 Avg("book__rating", output_field=DecimalField()), Avg("book__price")
1388 )
1389 )
1390 .filter(price_or_median__gte=F("num_awards"))
1391 .order_by("num_awards")
1392 )
1393 self.assertQuerySetEqual(qs, [1, 3, 7, 9], lambda v: v.num_awards)
1394
1395 qs2 = (
1396 Publisher.objects.annotate(
1397 rating_or_num_awards=Greatest(
1398 Avg("book__rating"), F("num_awards"), output_field=FloatField()
1399 )
1400 )
1401 .filter(rating_or_num_awards__gt=F("num_awards"))
1402 .order_by("num_awards")
1403 )
1404 self.assertQuerySetEqual(qs2, [1, 3], lambda v: v.num_awards)
1405
1406 def test_arguments_must_be_expressions(self):
1407 msg = "QuerySet.aggregate() received non-expression(s): %s."
1408 with self.assertRaisesMessage(TypeError, msg % FloatField()):
1409 Book.objects.aggregate(FloatField())
1410 with self.assertRaisesMessage(TypeError, msg % True):
1411 Book.objects.aggregate(is_book=True)
1412 with self.assertRaisesMessage(
1413 TypeError, msg % ", ".join([str(FloatField()), "True"])
1414 ):
1415 Book.objects.aggregate(FloatField(), Avg("price"), is_book=True)
1416
1417 def test_aggregation_subquery_annotation(self):
1418 """Subquery annotations are excluded from the GROUP BY if they are
1419 not explicitly grouped against."""
1420 latest_book_pubdate_qs = (
1421 Book.objects.filter(publisher=OuterRef("pk"))
1422 .order_by("-pubdate")
1423 .values("pubdate")[:1]
1424 )
1425 publisher_qs = Publisher.objects.annotate(
1426 latest_book_pubdate=Subquery(latest_book_pubdate_qs),
1427 ).annotate(count=Count("book"))
1428 with self.assertNumQueries(1) as ctx:
1429 list(publisher_qs)
1430 self.assertEqual(ctx[0]["sql"].count("SELECT"), 2)
1431 # The GROUP BY should not be by alias either.
1432 self.assertEqual(ctx[0]["sql"].lower().count("latest_book_pubdate"), 1)
1433
1434 def test_aggregation_subquery_annotation_exists(self):
1435 latest_book_pubdate_qs = (
1436 Book.objects.filter(publisher=OuterRef("pk"))
1437 .order_by("-pubdate")
1438 .values("pubdate")[:1]
1439 )
1440 publisher_qs = Publisher.objects.annotate(
1441 latest_book_pubdate=Subquery(latest_book_pubdate_qs),
1442 count=Count("book"),
1443 )
1444 self.assertTrue(publisher_qs.exists())
1445
1446 def test_aggregation_filter_exists(self):
1447 publishers_having_more_than_one_book_qs = (
1448 Book.objects.values("publisher")
1449 .annotate(cnt=Count("isbn"))
1450 .filter(cnt__gt=1)
1451 )
1452 query = publishers_having_more_than_one_book_qs.query.exists()
1453 _, _, group_by = query.get_compiler(connection=connection).pre_sql_setup()
1454 self.assertEqual(len(group_by), 1)
1455
1456 def test_aggregation_exists_annotation(self):
1457 published_books = Book.objects.filter(publisher=OuterRef("pk"))
1458 publisher_qs = Publisher.objects.annotate(
1459 published_book=Exists(published_books),
1460 count=Count("book"),
1461 ).values_list("name", flat=True)
1462 self.assertCountEqual(
1463 list(publisher_qs),
1464 [
1465 "Apress",
1466 "Morgan Kaufmann",
1467 "Jonno's House of Books",
1468 "Prentice Hall",
1469 "Sams",
1470 ],
1471 )
1472
1473 def test_aggregation_subquery_annotation_values(self):
1474 """
1475 Subquery annotations and external aliases are excluded from the GROUP
1476 BY if they are not selected.
1477 """
1478 books_qs = (
1479 Book.objects.annotate(
1480 first_author_the_same_age=Subquery(
1481 Author.objects.filter(
1482 age=OuterRef("contact__friends__age"),
1483 )
1484 .order_by("age")
1485 .values("id")[:1],
1486 )
1487 )
1488 .filter(
1489 publisher=self.p1,
1490 first_author_the_same_age__isnull=False,
1491 )
1492 .annotate(
1493 min_age=Min("contact__friends__age"),
1494 )
1495 .values("name", "min_age")
1496 .order_by("name")
1497 )
1498 self.assertEqual(
1499 list(books_qs),
1500 [
1501 {"name": "Practical Django Projects", "min_age": 34},
1502 {
1503 "name": (
1504 "The Definitive Guide to Django: Web Development Done Right"
1505 ),
1506 "min_age": 29,
1507 },
1508 ],
1509 )
1510
1511 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1512 def test_aggregation_subquery_annotation_values_collision(self):
1513 books_rating_qs = Book.objects.filter(
1514 pk=OuterRef("book"),
1515 ).values("rating")
1516 publisher_qs = (
1517 Publisher.objects.filter(
1518 book__contact__age__gt=20,
1519 )
1520 .annotate(
1521 rating=Subquery(books_rating_qs),
1522 )
1523 .values("rating")
1524 .annotate(total_count=Count("*"))
1525 .order_by("rating")
1526 )
1527 self.assertEqual(
1528 list(publisher_qs),
1529 [
1530 {"rating": 3.0, "total_count": 1},
1531 {"rating": 4.0, "total_count": 3},
1532 {"rating": 4.5, "total_count": 1},
1533 {"rating": 5.0, "total_count": 1},
1534 ],
1535 )
1536
1537 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1538 def test_aggregation_subquery_annotation_multivalued(self):
1539 """
1540 Subquery annotations must be included in the GROUP BY if they use
1541 potentially multivalued relations (contain the LOOKUP_SEP).
1542 """
1543 subquery_qs = Author.objects.filter(
1544 pk=OuterRef("pk"),
1545 book__name=OuterRef("book__name"),
1546 ).values("pk")
1547 author_qs = Author.objects.annotate(
1548 subquery_id=Subquery(subquery_qs),
1549 ).annotate(count=Count("book"))
1550 self.assertEqual(author_qs.count(), Author.objects.count())
1551
1552 def test_aggregation_order_by_not_selected_annotation_values(self):
1553 result_asc = [
1554 self.b4.pk,
1555 self.b3.pk,
1556 self.b1.pk,
1557 self.b2.pk,
1558 self.b5.pk,
1559 self.b6.pk,
1560 ]
1561 result_desc = result_asc[::-1]
1562 tests = [
1563 ("min_related_age", result_asc),
1564 ("-min_related_age", result_desc),
1565 (F("min_related_age"), result_asc),
1566 (F("min_related_age").asc(), result_asc),
1567 (F("min_related_age").desc(), result_desc),
1568 ]
1569 for ordering, expected_result in tests:
1570 with self.subTest(ordering=ordering):
1571 books_qs = (
1572 Book.objects.annotate(
1573 min_age=Min("authors__age"),
1574 )
1575 .annotate(
1576 min_related_age=Coalesce("min_age", "contact__age"),
1577 )
1578 .order_by(ordering)
1579 .values_list("pk", flat=True)
1580 )
1581 self.assertEqual(list(books_qs), expected_result)
1582
1583 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1584 def test_group_by_subquery_annotation(self):
1585 """
1586 Subquery annotations are included in the GROUP BY if they are
1587 grouped against.
1588 """
1589 long_books_count_qs = (
1590 Book.objects.filter(
1591 publisher=OuterRef("pk"),
1592 pages__gt=400,
1593 )
1594 .values("publisher")
1595 .annotate(count=Count("pk"))
1596 .values("count")
1597 )
1598 groups = [
1599 Subquery(long_books_count_qs),
1600 long_books_count_qs,
1601 long_books_count_qs.query,
1602 ]
1603 for group in groups:
1604 with self.subTest(group=group.__class__.__name__):
1605 long_books_count_breakdown = Publisher.objects.values_list(
1606 group,
1607 ).annotate(total=Count("*"))
1608 self.assertEqual(dict(long_books_count_breakdown), {None: 1, 1: 4})
1609
1610 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1611 def test_group_by_exists_annotation(self):
1612 """
1613 Exists annotations are included in the GROUP BY if they are
1614 grouped against.
1615 """
1616 long_books_qs = Book.objects.filter(
1617 publisher=OuterRef("pk"),
1618 pages__gt=800,
1619 )
1620 has_long_books_breakdown = Publisher.objects.values_list(
1621 Exists(long_books_qs),
1622 ).annotate(total=Count("*"))
1623 self.assertEqual(dict(has_long_books_breakdown), {True: 2, False: 3})
1624
1625 def test_group_by_nested_expression_with_params(self):
1626 books_qs = (
1627 Book.objects.annotate(greatest_pages=Greatest("pages", Value(600)))
1628 .values(
1629 "greatest_pages",
1630 )
1631 .annotate(
1632 min_pages=Min("pages"),
1633 least=Least("min_pages", "greatest_pages"),
1634 )
1635 .values_list("least", flat=True)
1636 )
1637 self.assertCountEqual(books_qs, [300, 946, 1132])
1638
1639 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1640 def test_aggregation_subquery_annotation_related_field(self):
1641 publisher = Publisher.objects.create(name=self.a9.name, num_awards=2)
1642 book = Book.objects.create(
1643 isbn="159059999",
1644 name="Test book.",
1645 pages=819,
1646 rating=2.5,
1647 price=Decimal("14.44"),
1648 contact=self.a9,
1649 publisher=publisher,
1650 pubdate=datetime.date(2019, 12, 6),
1651 )
1652 book.authors.add(self.a5, self.a6, self.a7)
1653 books_qs = (
1654 Book.objects.annotate(
1655 contact_publisher=Subquery(
1656 Publisher.objects.filter(
1657 pk=OuterRef("publisher"),
1658 name=OuterRef("contact__name"),
1659 ).values("name")[:1],
1660 )
1661 )
1662 .filter(
1663 contact_publisher__isnull=False,
1664 )
1665 .annotate(count=Count("authors"))
1666 )
1667 with self.assertNumQueries(1) as ctx:
1668 self.assertSequenceEqual(books_qs, [book])
1669 if connection.features.allows_group_by_select_index:
1670 self.assertEqual(ctx[0]["sql"].count("SELECT"), 3)
1671
1672 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1673 def test_aggregation_nested_subquery_outerref(self):
1674 publisher_with_same_name = Publisher.objects.filter(
1675 id__in=Subquery(
1676 Publisher.objects.filter(
1677 name=OuterRef(OuterRef("publisher__name")),
1678 ).values("id"),
1679 ),
1680 ).values(publisher_count=Count("id"))[:1]
1681 books_breakdown = Book.objects.annotate(
1682 publisher_count=Subquery(publisher_with_same_name),
1683 authors_count=Count("authors"),
1684 ).values_list("publisher_count", flat=True)
1685 self.assertSequenceEqual(books_breakdown, [1] * 6)
1686
1687 def test_aggregation_exists_multivalued_outeref(self):
1688 self.assertCountEqual(
1689 Publisher.objects.annotate(
1690 books_exists=Exists(
1691 Book.objects.filter(publisher=OuterRef("book__publisher"))
1692 ),
1693 books_count=Count("book"),
1694 ),
1695 Publisher.objects.all(),
1696 )
1697
1698 def test_filter_in_subquery_or_aggregation(self):
1699 """
1700 Filtering against an aggregate requires the usage of the HAVING clause.
1701
1702 If such a filter is unionized to a non-aggregate one the latter will
1703 also need to be moved to the HAVING clause and have its grouping
1704 columns used in the GROUP BY.
1705
1706 When this is done with a subquery the specialized logic in charge of
1707 using outer reference columns to group should be used instead of the
1708 subquery itself as the latter might return multiple rows.
1709 """
1710 authors = Author.objects.annotate(
1711 Count("book"),
1712 ).filter(Q(book__count__gt=0) | Q(pk__in=Book.objects.values("authors")))
1713 self.assertCountEqual(authors, Author.objects.all())
1714
1715 def test_aggregation_random_ordering(self):
1716 """Random() is not included in the GROUP BY when used for ordering."""
1717 authors = Author.objects.annotate(contact_count=Count("book")).order_by("?")
1718 self.assertQuerySetEqual(
1719 authors,
1720 [
1721 ("Adrian Holovaty", 1),
1722 ("Jacob Kaplan-Moss", 1),
1723 ("Brad Dayley", 1),
1724 ("James Bennett", 1),
1725 ("Jeffrey Forcier", 1),
1726 ("Paul Bissex", 1),
1727 ("Wesley J. Chun", 1),
1728 ("Stuart Russell", 1),
1729 ("Peter Norvig", 2),
1730 ],
1731 lambda a: (a.name, a.contact_count),
1732 ordered=False,
1733 )
1734
1735 def test_order_by_aggregate_transform(self):
1736 class Mod100(Mod, Transform):
1737 def __init__(self, expr):
1738 super().__init__(expr, 100)
1739
1740 sum_field = IntegerField()
1741 sum_field.register_lookup(Mod100, "mod100")
1742 publisher_pages = (
1743 Book.objects.values("publisher")
1744 .annotate(sum_pages=Sum("pages", output_field=sum_field))
1745 .order_by("sum_pages__mod100")
1746 )
1747 self.assertQuerySetEqual(
1748 publisher_pages,
1749 [
1750 {"publisher": self.p2.id, "sum_pages": 528},
1751 {"publisher": self.p4.id, "sum_pages": 946},
1752 {"publisher": self.p1.id, "sum_pages": 747},
1753 {"publisher": self.p3.id, "sum_pages": 1482},
1754 ],
1755 )
1756
1757 def test_order_by_aggregate_default_alias(self):
1758 publisher_books = (
1759 Publisher.objects.values("book")
1760 .annotate(Count("book"))
1761 .order_by("book__count", "book__id")
1762 .values_list("book", flat=True)
1763 )
1764 self.assertQuerySetEqual(
1765 publisher_books,
1766 [
1767 None,
1768 self.b1.id,
1769 self.b2.id,
1770 self.b3.id,
1771 self.b4.id,
1772 self.b5.id,
1773 self.b6.id,
1774 ],
1775 )
1776
1777 def test_empty_result_optimization(self):
1778 with self.assertNumQueries(0):
1779 self.assertEqual(
1780 Publisher.objects.none().aggregate(
1781 sum_awards=Sum("num_awards"),
1782 books_count=Count("book"),
1783 ),
1784 {
1785 "sum_awards": None,
1786 "books_count": 0,
1787 },
1788 )
1789 # Expression without empty_result_set_value forces queries to be
1790 # executed even if they would return an empty result set.
1791 raw_books_count = Func("book", function="COUNT")
1792 raw_books_count.contains_aggregate = True
1793 with self.assertNumQueries(1):
1794 self.assertEqual(
1795 Publisher.objects.none().aggregate(
1796 sum_awards=Sum("num_awards"),
1797 books_count=raw_books_count,
1798 ),
1799 {
1800 "sum_awards": None,
1801 "books_count": 0,
1802 },
1803 )
1804
1805 def test_coalesced_empty_result_set(self):
1806 with self.assertNumQueries(0):
1807 self.assertEqual(
1808 Publisher.objects.none().aggregate(
1809 sum_awards=Coalesce(Sum("num_awards"), 0),
1810 )["sum_awards"],
1811 0,
1812 )
1813 # Multiple expressions.
1814 with self.assertNumQueries(0):
1815 self.assertEqual(
1816 Publisher.objects.none().aggregate(
1817 sum_awards=Coalesce(Sum("num_awards"), None, 0),
1818 )["sum_awards"],
1819 0,
1820 )
1821 # Nested coalesce.
1822 with self.assertNumQueries(0):
1823 self.assertEqual(
1824 Publisher.objects.none().aggregate(
1825 sum_awards=Coalesce(Coalesce(Sum("num_awards"), None), 0),
1826 )["sum_awards"],
1827 0,
1828 )
1829 # Expression coalesce.
1830 with self.assertNumQueries(1):
1831 self.assertIsInstance(
1832 Store.objects.none().aggregate(
1833 latest_opening=Coalesce(
1834 Max("original_opening"),
1835 RawSQL("CURRENT_TIMESTAMP", []),
1836 ),
1837 )["latest_opening"],
1838 datetime.datetime,
1839 )
1840
1841 def test_aggregation_default_unsupported_by_count(self):
1842 msg = "Count does not allow default."
1843 with self.assertRaisesMessage(TypeError, msg):
1844 Count("age", default=0)
1845
1846 def test_aggregation_default_unset(self):
1847 for Aggregate in [Avg, Max, Min, StdDev, Sum, Variance]:
1848 with self.subTest(Aggregate):
1849 result = Author.objects.filter(age__gt=100).aggregate(
1850 value=Aggregate("age"),
1851 )
1852 self.assertIsNone(result["value"])
1853
1854 def test_aggregation_default_zero(self):
1855 for Aggregate in [Avg, Max, Min, StdDev, Sum, Variance]:
1856 with self.subTest(Aggregate):
1857 result = Author.objects.filter(age__gt=100).aggregate(
1858 value=Aggregate("age", default=0),
1859 )
1860 self.assertEqual(result["value"], 0)
1861
1862 def test_aggregation_default_integer(self):
1863 for Aggregate in [Avg, Max, Min, StdDev, Sum, Variance]:
1864 with self.subTest(Aggregate):
1865 result = Author.objects.filter(age__gt=100).aggregate(
1866 value=Aggregate("age", default=21),
1867 )
1868 self.assertEqual(result["value"], 21)
1869
1870 def test_aggregation_default_expression(self):
1871 for Aggregate in [Avg, Max, Min, StdDev, Sum, Variance]:
1872 with self.subTest(Aggregate):
1873 result = Author.objects.filter(age__gt=100).aggregate(
1874 value=Aggregate("age", default=Value(5) * Value(7)),
1875 )
1876 self.assertEqual(result["value"], 35)
1877
1878 def test_aggregation_default_group_by(self):
1879 qs = (
1880 Publisher.objects.values("name")
1881 .annotate(
1882 books=Count("book"),
1883 pages=Sum("book__pages", default=0),
1884 )
1885 .filter(books=0)
1886 )
1887 self.assertSequenceEqual(
1888 qs,
1889 [{"name": "Jonno's House of Books", "books": 0, "pages": 0}],
1890 )
1891
1892 def test_aggregation_default_compound_expression(self):
1893 # Scale rating to a percentage; default to 50% if no books published.
1894 formula = Avg("book__rating", default=2.5) * 20.0
1895 queryset = Publisher.objects.annotate(rating=formula).order_by("name")
1896 self.assertSequenceEqual(
1897 queryset.values("name", "rating"),
1898 [
1899 {"name": "Apress", "rating": 85.0},
1900 {"name": "Jonno's House of Books", "rating": 50.0},
1901 {"name": "Morgan Kaufmann", "rating": 100.0},
1902 {"name": "Prentice Hall", "rating": 80.0},
1903 {"name": "Sams", "rating": 60.0},
1904 ],
1905 )
1906
1907 def test_aggregation_default_using_time_from_python(self):
1908 expr = Min(
1909 "store__friday_night_closing",
1910 filter=~Q(store__name="Amazon.com"),
1911 default=datetime.time(17),
1912 )
1913 if connection.vendor == "mysql":
1914 # Workaround for #30224 for MySQL & MariaDB.
1915 expr.default = Cast(expr.default, TimeField())
1916 queryset = Book.objects.annotate(oldest_store_opening=expr).order_by("isbn")
1917 self.assertSequenceEqual(
1918 queryset.values("isbn", "oldest_store_opening"),
1919 [
1920 {"isbn": "013235613", "oldest_store_opening": datetime.time(21, 30)},
1921 {
1922 "isbn": "013790395",
1923 "oldest_store_opening": datetime.time(23, 59, 59),
1924 },
1925 {"isbn": "067232959", "oldest_store_opening": datetime.time(17)},
1926 {"isbn": "155860191", "oldest_store_opening": datetime.time(21, 30)},
1927 {
1928 "isbn": "159059725",
1929 "oldest_store_opening": datetime.time(23, 59, 59),
1930 },
1931 {"isbn": "159059996", "oldest_store_opening": datetime.time(21, 30)},
1932 ],
1933 )
1934
1935 def test_aggregation_default_using_time_from_database(self):
1936 now = timezone.now().astimezone(datetime.timezone.utc)
1937 expr = Min(
1938 "store__friday_night_closing",
1939 filter=~Q(store__name="Amazon.com"),
1940 default=TruncHour(NowUTC(), output_field=TimeField()),
1941 )
1942 queryset = Book.objects.annotate(oldest_store_opening=expr).order_by("isbn")
1943 self.assertSequenceEqual(
1944 queryset.values("isbn", "oldest_store_opening"),
1945 [
1946 {"isbn": "013235613", "oldest_store_opening": datetime.time(21, 30)},
1947 {
1948 "isbn": "013790395",
1949 "oldest_store_opening": datetime.time(23, 59, 59),
1950 },
1951 {"isbn": "067232959", "oldest_store_opening": datetime.time(now.hour)},
1952 {"isbn": "155860191", "oldest_store_opening": datetime.time(21, 30)},
1953 {
1954 "isbn": "159059725",
1955 "oldest_store_opening": datetime.time(23, 59, 59),
1956 },
1957 {"isbn": "159059996", "oldest_store_opening": datetime.time(21, 30)},
1958 ],
1959 )
1960
1961 def test_aggregation_default_using_date_from_python(self):
1962 expr = Min("book__pubdate", default=datetime.date(1970, 1, 1))
1963 if connection.vendor == "mysql":
1964 # Workaround for #30224 for MySQL & MariaDB.
1965 expr.default = Cast(expr.default, DateField())
1966 queryset = Publisher.objects.annotate(earliest_pubdate=expr).order_by("name")
1967 self.assertSequenceEqual(
1968 queryset.values("name", "earliest_pubdate"),
1969 [
1970 {"name": "Apress", "earliest_pubdate": datetime.date(2007, 12, 6)},
1971 {
1972 "name": "Jonno's House of Books",
1973 "earliest_pubdate": datetime.date(1970, 1, 1),
1974 },
1975 {
1976 "name": "Morgan Kaufmann",
1977 "earliest_pubdate": datetime.date(1991, 10, 15),
1978 },
1979 {
1980 "name": "Prentice Hall",
1981 "earliest_pubdate": datetime.date(1995, 1, 15),
1982 },
1983 {"name": "Sams", "earliest_pubdate": datetime.date(2008, 3, 3)},
1984 ],
1985 )
1986
1987 def test_aggregation_default_using_date_from_database(self):
1988 now = timezone.now().astimezone(datetime.timezone.utc)
1989 expr = Min("book__pubdate", default=TruncDate(NowUTC()))
1990 queryset = Publisher.objects.annotate(earliest_pubdate=expr).order_by("name")
1991 self.assertSequenceEqual(
1992 queryset.values("name", "earliest_pubdate"),
1993 [
1994 {"name": "Apress", "earliest_pubdate": datetime.date(2007, 12, 6)},
1995 {"name": "Jonno's House of Books", "earliest_pubdate": now.date()},
1996 {
1997 "name": "Morgan Kaufmann",
1998 "earliest_pubdate": datetime.date(1991, 10, 15),
1999 },
2000 {
2001 "name": "Prentice Hall",
2002 "earliest_pubdate": datetime.date(1995, 1, 15),
2003 },
2004 {"name": "Sams", "earliest_pubdate": datetime.date(2008, 3, 3)},
2005 ],
2006 )
2007
2008 def test_aggregation_default_using_datetime_from_python(self):
2009 expr = Min(
2010 "store__original_opening",
2011 filter=~Q(store__name="Amazon.com"),
2012 default=datetime.datetime(1970, 1, 1),
2013 )
2014 if connection.vendor == "mysql":
2015 # Workaround for #30224 for MySQL & MariaDB.
2016 expr.default = Cast(expr.default, DateTimeField())
2017 queryset = Book.objects.annotate(oldest_store_opening=expr).order_by("isbn")
2018 self.assertSequenceEqual(
2019 queryset.values("isbn", "oldest_store_opening"),
2020 [
2021 {
2022 "isbn": "013235613",
2023 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2024 },
2025 {
2026 "isbn": "013790395",
2027 "oldest_store_opening": datetime.datetime(2001, 3, 15, 11, 23, 37),
2028 },
2029 {
2030 "isbn": "067232959",
2031 "oldest_store_opening": datetime.datetime(1970, 1, 1),
2032 },
2033 {
2034 "isbn": "155860191",
2035 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2036 },
2037 {
2038 "isbn": "159059725",
2039 "oldest_store_opening": datetime.datetime(2001, 3, 15, 11, 23, 37),
2040 },
2041 {
2042 "isbn": "159059996",
2043 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2044 },
2045 ],
2046 )
2047
2048 def test_aggregation_default_using_datetime_from_database(self):
2049 now = timezone.now().astimezone(datetime.timezone.utc)
2050 expr = Min(
2051 "store__original_opening",
2052 filter=~Q(store__name="Amazon.com"),
2053 default=TruncHour(NowUTC(), output_field=DateTimeField()),
2054 )
2055 queryset = Book.objects.annotate(oldest_store_opening=expr).order_by("isbn")
2056 self.assertSequenceEqual(
2057 queryset.values("isbn", "oldest_store_opening"),
2058 [
2059 {
2060 "isbn": "013235613",
2061 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2062 },
2063 {
2064 "isbn": "013790395",
2065 "oldest_store_opening": datetime.datetime(2001, 3, 15, 11, 23, 37),
2066 },
2067 {
2068 "isbn": "067232959",
2069 "oldest_store_opening": now.replace(
2070 minute=0, second=0, microsecond=0, tzinfo=None
2071 ),
2072 },
2073 {
2074 "isbn": "155860191",
2075 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2076 },
2077 {
2078 "isbn": "159059725",
2079 "oldest_store_opening": datetime.datetime(2001, 3, 15, 11, 23, 37),
2080 },
2081 {
2082 "isbn": "159059996",
2083 "oldest_store_opening": datetime.datetime(1945, 4, 25, 16, 24, 14),
2084 },
2085 ],
2086 )
2087
2088 def test_aggregation_default_using_duration_from_python(self):
2089 result = Publisher.objects.filter(num_awards__gt=3).aggregate(
2090 value=Sum("duration", default=datetime.timedelta(0)),
2091 )
2092 self.assertEqual(result["value"], datetime.timedelta(0))
2093
2094 def test_aggregation_default_using_duration_from_database(self):
2095 result = Publisher.objects.filter(num_awards__gt=3).aggregate(
2096 value=Sum("duration", default=Now() - Now()),
2097 )
2098 self.assertEqual(result["value"], datetime.timedelta(0))
2099
2100 def test_aggregation_default_using_decimal_from_python(self):
2101 result = Book.objects.filter(rating__lt=3.0).aggregate(
2102 value=Sum("price", default=Decimal("0.00")),
2103 )
2104 self.assertEqual(result["value"], Decimal("0.00"))
2105
2106 def test_aggregation_default_using_decimal_from_database(self):
2107 result = Book.objects.filter(rating__lt=3.0).aggregate(
2108 value=Sum("price", default=Pi()),
2109 )
2110 self.assertAlmostEqual(result["value"], Decimal.from_float(math.pi), places=6)
2111
2112 def test_aggregation_default_passed_another_aggregate(self):
2113 result = Book.objects.aggregate(
2114 value=Sum("price", filter=Q(rating__lt=3.0), default=Avg("pages") / 10.0),
2115 )
2116 self.assertAlmostEqual(result["value"], Decimal("61.72"), places=2)
2117
2118 def test_aggregation_default_after_annotation(self):
2119 result = Publisher.objects.annotate(
2120 double_num_awards=F("num_awards") * 2,
2121 ).aggregate(value=Sum("double_num_awards", default=0))
2122 self.assertEqual(result["value"], 40)
2123
2124 def test_aggregation_default_not_in_aggregate(self):
2125 result = Publisher.objects.annotate(
2126 avg_rating=Avg("book__rating", default=2.5),
2127 ).aggregate(Sum("num_awards"))
2128 self.assertEqual(result["num_awards__sum"], 20)
2129
2130 def test_exists_none_with_aggregate(self):
2131 qs = Book.objects.annotate(
2132 count=Count("id"),
2133 exists=Exists(Author.objects.none()),
2134 )
2135 self.assertEqual(len(qs), 6)
2136
2137 def test_alias_sql_injection(self):
2138 msg = (
2139 "Column aliases cannot contain whitespace characters, hashes, "
2140 "control characters, quotation marks, semicolons, or SQL comments."
2141 )
2142 for crafted_alias in [
2143 """injected_name" from "aggregation_author"; --""",
2144 # Control characters.
2145 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
2146 ]:
2147 with self.subTest(crafted_alias):
2148 with self.assertRaisesMessage(ValueError, msg):
2149 Author.objects.aggregate(**{crafted_alias: Avg("age")})
2150
2151 def test_exists_extra_where_with_aggregate(self):
2152 qs = Book.objects.annotate(
2153 count=Count("id"),
2154 exists=Exists(Author.objects.extra(where=["1=0"])),
2155 )
2156 self.assertEqual(len(qs), 6)
2157
2158 def test_multiple_aggregate_references(self):
2159 aggregates = Author.objects.aggregate(
2160 total_books=Count("book"),
2161 coalesced_total_books=Coalesce("total_books", 0),
2162 )
2163 self.assertEqual(
2164 aggregates,
2165 {
2166 "total_books": 10,
2167 "coalesced_total_books": 10,
2168 },
2169 )
2170
2171 def test_group_by_transform_column(self):
2172 self.assertSequenceEqual(
2173 Store.objects.values(
2174 "original_opening__date",
2175 "name",
2176 )
2177 .annotate(Count("books"))
2178 .order_by("name"),
2179 [
2180 {
2181 "original_opening__date": datetime.date(1994, 4, 23),
2182 "name": "Amazon.com",
2183 "books__count": 6,
2184 },
2185 {
2186 "original_opening__date": datetime.date(2001, 3, 15),
2187 "name": "Books.com",
2188 "books__count": 4,
2189 },
2190 {
2191 "original_opening__date": datetime.date(1945, 4, 25),
2192 "name": "Mamma and Pappa's Books",
2193 "books__count": 3,
2194 },
2195 ],
2196 )
2197
2198 def test_group_by_reference_subquery(self):
2199 author_qs = (
2200 Author.objects.annotate(publisher_id=F("book__publisher"))
2201 .values("publisher_id")
2202 .annotate(cnt=Count("*"))
2203 .values("publisher_id")
2204 )
2205 qs = Publisher.objects.filter(pk__in=author_qs)
2206 self.assertCountEqual(qs, [self.p1, self.p2, self.p3, self.p4])
2207
2208 def test_having_with_no_group_by(self):
2209 author_qs = (
2210 Author.objects.values(static_value=Value("static-value"))
2211 .annotate(sum=Sum("age"))
2212 .filter(sum__gte=0)
2213 .values_list("sum", flat=True)
2214 )
2215 self.assertEqual(list(author_qs), [337])
2216
2217 def test_aggregate_arity(self):
2218 funcs_with_inherited_constructors = [Avg, Max, Min, Sum]
2219 msg = "takes exactly 1 argument (2 given)"
2220 for function in funcs_with_inherited_constructors:
2221 with (
2222 self.subTest(function=function),
2223 self.assertRaisesMessage(TypeError, msg),
2224 ):
2225 function(Value(1), Value(2))
2226
2227 funcs_with_custom_constructors = [Count, StdDev, Variance]
2228 for function in funcs_with_custom_constructors:
2229 with self.subTest(function=function):
2230 # Extra arguments are rejected via the constructor.
2231 with self.assertRaises(TypeError):
2232 function(Value(1), True, Value(2))
2233 # If the constructor is skipped, the arity check runs.
2234 func_instance = function(Value(1), True)
2235 with self.assertRaisesMessage(TypeError, msg):
2236 super(function, func_instance).__init__(Value(1), Value(2))
2237
2238
2239 class AggregateAnnotationPruningTests(TestCase):
2240 @classmethod
2241 def setUpTestData(cls):
2242 cls.a1 = Author.objects.create(age=1)
2243 cls.a2 = Author.objects.create(age=2)
2244 cls.p1 = Publisher.objects.create(num_awards=1)
2245 cls.p2 = Publisher.objects.create(num_awards=0)
2246 cls.b1 = Book.objects.create(
2247 name="b1",
2248 publisher=cls.p1,
2249 pages=100,
2250 rating=4.5,
2251 price=10,
2252 contact=cls.a1,
2253 pubdate=datetime.date.today(),
2254 )
2255 cls.b1.authors.add(cls.a1)
2256 cls.b2 = Book.objects.create(
2257 name="b2",
2258 publisher=cls.p2,
2259 pages=1000,
2260 rating=3.2,
2261 price=50,
2262 contact=cls.a2,
2263 pubdate=datetime.date.today(),
2264 )
2265 cls.b2.authors.add(cls.a1, cls.a2)
2266
2267 def test_unused_aliased_aggregate_pruned(self):
2268 with CaptureQueriesContext(connection) as ctx:
2269 cnt = Book.objects.alias(
2270 authors_count=Count("authors"),
2271 ).count()
2272 self.assertEqual(cnt, 2)
2273 sql = ctx.captured_queries[0]["sql"].lower()
2274 self.assertEqual(sql.count("select"), 2, "Subquery wrapping required")
2275 self.assertNotIn("authors_count", sql)
2276
2277 def test_unused_aliased_aggregate_and_annotation_reverse_fk(self):
2278 Book.objects.create(
2279 name="b3",
2280 publisher=self.p2,
2281 pages=1000,
2282 rating=4.2,
2283 price=50,
2284 contact=self.a2,
2285 pubdate=datetime.date.today(),
2286 )
2287 qs = Publisher.objects.annotate(
2288 total_pages=Sum("book__pages"),
2289 good_book=Case(
2290 When(book__rating__gt=4.0, then=Value(True)),
2291 default=Value(False),
2292 ),
2293 )
2294 self.assertEqual(qs.count(), 3)
2295
2296 def test_unused_aliased_aggregate_and_annotation_reverse_fk_grouped(self):
2297 Book.objects.create(
2298 name="b3",
2299 publisher=self.p2,
2300 pages=1000,
2301 rating=4.2,
2302 price=50,
2303 contact=self.a2,
2304 pubdate=datetime.date.today(),
2305 )
2306 qs = (
2307 Publisher.objects.values("id", "name")
2308 .annotate(total_pages=Sum("book__pages"))
2309 .annotate(
2310 good_book=Case(
2311 When(book__rating__gt=4.0, then=Value(True)),
2312 default=Value(False),
2313 )
2314 )
2315 )
2316 self.assertEqual(qs.count(), 3)
2317
2318 def test_non_aggregate_annotation_pruned(self):
2319 with CaptureQueriesContext(connection) as ctx:
2320 Book.objects.annotate(
2321 name_lower=Lower("name"),
2322 ).count()
2323 sql = ctx.captured_queries[0]["sql"].lower()
2324 self.assertEqual(sql.count("select"), 1, "No subquery wrapping required")
2325 self.assertNotIn("name_lower", sql)
2326
2327 def test_unreferenced_aggregate_annotation_pruned(self):
2328 with CaptureQueriesContext(connection) as ctx:
2329 cnt = Book.objects.annotate(
2330 authors_count=Count("authors"),
2331 ).count()
2332 self.assertEqual(cnt, 2)
2333 sql = ctx.captured_queries[0]["sql"].lower()
2334 self.assertEqual(sql.count("select"), 2, "Subquery wrapping required")
2335 self.assertNotIn("authors_count", sql)
2336
2337 def test_referenced_aggregate_annotation_kept(self):
2338 with CaptureQueriesContext(connection) as ctx:
2339 Book.objects.annotate(
2340 authors_count=Count("authors"),
2341 ).aggregate(Avg("authors_count"))
2342 sql = ctx.captured_queries[0]["sql"].lower()
2343 self.assertEqual(sql.count("select"), 2, "Subquery wrapping required")
2344 self.assertEqual(sql.count("authors_count"), 2)
2345
2346 def test_referenced_group_by_annotation_kept(self):
2347 queryset = Book.objects.values(pages_mod=Mod("pages", 10)).annotate(
2348 mod_count=Count("*")
2349 )
2350 self.assertEqual(queryset.count(), 1)
2351
2352 def test_referenced_subquery_requires_wrapping(self):
2353 total_books_qs = (
2354 Author.book_set.through.objects.values("author")
2355 .filter(author=OuterRef("pk"))
2356 .annotate(total=Count("book"))
2357 )
2358 with self.assertNumQueries(1) as ctx:
2359 aggregate = (
2360 Author.objects.annotate(
2361 total_books=Subquery(total_books_qs.values("total"))
2362 )
2363 .values("pk", "total_books")
2364 .aggregate(
2365 sum_total_books=Sum("total_books"),
2366 )
2367 )
2368 sql = ctx.captured_queries[0]["sql"].lower()
2369 self.assertEqual(sql.count("select"), 3, "Subquery wrapping required")
2370 self.assertEqual(aggregate, {"sum_total_books": 3})
2371
2372 def test_referenced_composed_subquery_requires_wrapping(self):
2373 total_books_qs = (
2374 Author.book_set.through.objects.values("author")
2375 .filter(author=OuterRef("pk"))
2376 .annotate(total=Count("book"))
2377 )
2378 with self.assertNumQueries(1) as ctx:
2379 aggregate = (
2380 Author.objects.annotate(
2381 total_books=Subquery(total_books_qs.values("total")),
2382 total_books_ref=F("total_books") / 1,
2383 )
2384 .values("pk", "total_books_ref")
2385 .aggregate(
2386 sum_total_books=Sum("total_books_ref"),
2387 )
2388 )
2389 sql = ctx.captured_queries[0]["sql"].lower()
2390 self.assertEqual(sql.count("select"), 3, "Subquery wrapping required")
2391 self.assertEqual(aggregate, {"sum_total_books": 3})
2392
2393 @skipUnlessDBFeature("supports_over_clause")
2394 def test_referenced_window_requires_wrapping(self):
2395 total_books_qs = Book.objects.annotate(
2396 avg_publisher_pages=Coalesce(
2397 Window(Avg("pages"), partition_by=F("publisher")),
2398 0.0,
2399 )
2400 )
2401 with self.assertNumQueries(1) as ctx:
2402 aggregate = total_books_qs.aggregate(
2403 sum_avg_publisher_pages=Sum("avg_publisher_pages"),
2404 books_count=Count("id"),
2405 )
2406 sql = ctx.captured_queries[0]["sql"].lower()
2407 self.assertEqual(sql.count("select"), 2, "Subquery wrapping required")
2408 self.assertEqual(
2409 aggregate,
2410 {"sum_avg_publisher_pages": 1100.0, "books_count": 2},
2411 )
2412
2413 def test_aggregate_reference_lookup_rhs(self):
2414 aggregates = Author.objects.annotate(
2415 max_book_author=Max("book__authors"),
2416 ).aggregate(count=Count("id", filter=Q(id=F("max_book_author"))))
2417 self.assertEqual(aggregates, {"count": 1})
2418
2419 def test_aggregate_reference_lookup_rhs_iter(self):
2420 aggregates = Author.objects.annotate(
2421 max_book_author=Max("book__authors"),
2422 ).aggregate(count=Count("id", filter=Q(id__in=[F("max_book_author"), 0])))
2423 self.assertEqual(aggregates, {"count": 1})
2424
2425 @skipUnlessDBFeature("supports_select_union")
2426 def test_aggregate_combined_queries(self):
2427 # Combined queries could have members in their values select mask while
2428 # others have them in their annotation mask which makes annotation
2429 # pruning complex to implement hence why it's not implemented.
2430 qs = Author.objects.values(
2431 "age",
2432 other=Value(0),
2433 ).union(
2434 Book.objects.values(
2435 age=Value(0),
2436 other=Value(0),
2437 )
2438 )
2439 self.assertEqual(qs.count(), 3)