Debian Codegraph @

tests/aggregation_regress/tests.py

raw
1 import datetime
2 import pickle
3 from decimal import Decimal
4 from operator import attrgetter
5 from unittest import mock
6
7 from django.contrib.contenttypes.models import ContentType
8 from django.core.exceptions import FieldError
9 from django.db import connection
10 from django.db.models import (
11 Aggregate,
12 Avg,
13 Case,
14 CharField,
15 Count,
16 DecimalField,
17 F,
18 IntegerField,
19 Max,
20 Q,
21 StdDev,
22 Sum,
23 Value,
24 Variance,
25 When,
26 )
27 from django.db.models.functions import Cast, Concat
28 from django.test import TestCase, skipUnlessDBFeature
29 from django.test.utils import Approximate
30
31 from .models import (
32 Alfa,
33 Author,
34 AuthorProxy,
35 AuthorUnmanaged,
36 Book,
37 Bravo,
38 Charlie,
39 Clues,
40 Entries,
41 HardbackBook,
42 ItemTag,
43 Publisher,
44 RecipeProxy,
45 RecipeUnmanaged,
46 SelfRefFK,
47 Store,
48 WithManualPK,
49 )
50
51
52 class AggregationTests(TestCase):
53 @classmethod
54 def setUpTestData(cls):
55 cls.a1 = Author.objects.create(name="Adrian Holovaty", age=34)
56 cls.a2 = Author.objects.create(name="Jacob Kaplan-Moss", age=35)
57 cls.a3 = Author.objects.create(name="Brad Dayley", age=45)
58 cls.a4 = Author.objects.create(name="James Bennett", age=29)
59 cls.a5 = Author.objects.create(name="Jeffrey Forcier", age=37)
60 cls.a6 = Author.objects.create(name="Paul Bissex", age=29)
61 cls.a7 = Author.objects.create(name="Wesley J. Chun", age=25)
62 cls.a8 = Author.objects.create(name="Peter Norvig", age=57)
63 cls.a9 = Author.objects.create(name="Stuart Russell", age=46)
64 cls.a1.friends.add(cls.a2, cls.a4)
65 cls.a2.friends.add(cls.a1, cls.a7)
66 cls.a4.friends.add(cls.a1)
67 cls.a5.friends.add(cls.a6, cls.a7)
68 cls.a6.friends.add(cls.a5, cls.a7)
69 cls.a7.friends.add(cls.a2, cls.a5, cls.a6)
70 cls.a8.friends.add(cls.a9)
71 cls.a9.friends.add(cls.a8)
72
73 cls.p1 = Publisher.objects.create(name="Apress", num_awards=3)
74 cls.p2 = Publisher.objects.create(name="Sams", num_awards=1)
75 cls.p3 = Publisher.objects.create(name="Prentice Hall", num_awards=7)
76 cls.p4 = Publisher.objects.create(name="Morgan Kaufmann", num_awards=9)
77 cls.p5 = Publisher.objects.create(name="Jonno's House of Books", num_awards=0)
78
79 cls.b1 = Book.objects.create(
80 isbn="159059725",
81 name="The Definitive Guide to Django: Web Development Done Right",
82 pages=447,
83 rating=4.5,
84 price=Decimal("30.00"),
85 contact=cls.a1,
86 publisher=cls.p1,
87 pubdate=datetime.date(2007, 12, 6),
88 )
89 cls.b2 = Book.objects.create(
90 isbn="067232959",
91 name="Sams Teach Yourself Django in 24 Hours",
92 pages=528,
93 rating=3.0,
94 price=Decimal("23.09"),
95 contact=cls.a3,
96 publisher=cls.p2,
97 pubdate=datetime.date(2008, 3, 3),
98 )
99 cls.b3 = Book.objects.create(
100 isbn="159059996",
101 name="Practical Django Projects",
102 pages=300,
103 rating=4.0,
104 price=Decimal("29.69"),
105 contact=cls.a4,
106 publisher=cls.p1,
107 pubdate=datetime.date(2008, 6, 23),
108 )
109 cls.b4 = Book.objects.create(
110 isbn="013235613",
111 name="Python Web Development with Django",
112 pages=350,
113 rating=4.0,
114 price=Decimal("29.69"),
115 contact=cls.a5,
116 publisher=cls.p3,
117 pubdate=datetime.date(2008, 11, 3),
118 )
119 cls.b5 = HardbackBook.objects.create(
120 isbn="013790395",
121 name="Artificial Intelligence: A Modern Approach",
122 pages=1132,
123 rating=4.0,
124 price=Decimal("82.80"),
125 contact=cls.a8,
126 publisher=cls.p3,
127 pubdate=datetime.date(1995, 1, 15),
128 weight=4.5,
129 )
130 cls.b6 = HardbackBook.objects.create(
131 isbn="155860191",
132 name=(
133 "Paradigms of Artificial Intelligence Programming: Case Studies in "
134 "Common Lisp"
135 ),
136 pages=946,
137 rating=5.0,
138 price=Decimal("75.00"),
139 contact=cls.a8,
140 publisher=cls.p4,
141 pubdate=datetime.date(1991, 10, 15),
142 weight=3.7,
143 )
144 cls.b1.authors.add(cls.a1, cls.a2)
145 cls.b2.authors.add(cls.a3)
146 cls.b3.authors.add(cls.a4)
147 cls.b4.authors.add(cls.a5, cls.a6, cls.a7)
148 cls.b5.authors.add(cls.a8, cls.a9)
149 cls.b6.authors.add(cls.a8)
150
151 s1 = Store.objects.create(
152 name="Amazon.com",
153 original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
154 friday_night_closing=datetime.time(23, 59, 59),
155 )
156 s2 = Store.objects.create(
157 name="Books.com",
158 original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
159 friday_night_closing=datetime.time(23, 59, 59),
160 )
161 s3 = Store.objects.create(
162 name="Mamma and Pappa's Books",
163 original_opening=datetime.datetime(1945, 4, 25, 16, 24, 14),
164 friday_night_closing=datetime.time(21, 30),
165 )
166 s1.books.add(cls.b1, cls.b2, cls.b3, cls.b4, cls.b5, cls.b6)
167 s2.books.add(cls.b1, cls.b3, cls.b5, cls.b6)
168 s3.books.add(cls.b3, cls.b4, cls.b6)
169
170 def assertObjectAttrs(self, obj, **kwargs):
171 for attr, value in kwargs.items():
172 self.assertEqual(getattr(obj, attr), value)
173
174 def test_annotation_with_value(self):
175 values = (
176 Book.objects.filter(
177 name="Practical Django Projects",
178 )
179 .annotate(
180 discount_price=F("price") * 2,
181 )
182 .values(
183 "discount_price",
184 )
185 .annotate(sum_discount=Sum("discount_price"))
186 )
187 with self.assertNumQueries(1) as ctx:
188 self.assertSequenceEqual(
189 values,
190 [
191 {
192 "discount_price": Decimal("59.38"),
193 "sum_discount": Decimal("59.38"),
194 }
195 ],
196 )
197 if connection.features.allows_group_by_select_index:
198 self.assertIn("GROUP BY 1", ctx[0]["sql"])
199
200 def test_aggregates_in_where_clause(self):
201 """
202 Regression test for #12822: DatabaseError: aggregates not allowed in
203 WHERE clause
204
205 The subselect works and returns results equivalent to a
206 query with the IDs listed.
207
208 Before the corresponding fix for this bug, this test passed in 1.1 and
209 failed in 1.2-beta (trunk).
210 """
211 qs = Book.objects.values("contact").annotate(Max("id"))
212 qs = qs.order_by("contact").values_list("id__max", flat=True)
213 # don't do anything with the queryset (qs) before including it as a
214 # subquery
215 books = Book.objects.order_by("id")
216 qs1 = books.filter(id__in=qs)
217 qs2 = books.filter(id__in=list(qs))
218 self.assertEqual(list(qs1), list(qs2))
219
220 def test_aggregates_in_where_clause_pre_eval(self):
221 """
222 Regression test for #12822: DatabaseError: aggregates not allowed in
223 WHERE clause
224
225 Same as the above test, but evaluates the queryset for the subquery
226 before it's used as a subquery.
227
228 Before the corresponding fix for this bug, this test failed in both
229 1.1 and 1.2-beta (trunk).
230 """
231 qs = Book.objects.values("contact").annotate(Max("id"))
232 qs = qs.order_by("contact").values_list("id__max", flat=True)
233 # force the queryset (qs) for the subquery to be evaluated in its
234 # current state
235 list(qs)
236 books = Book.objects.order_by("id")
237 qs1 = books.filter(id__in=qs)
238 qs2 = books.filter(id__in=list(qs))
239 self.assertEqual(list(qs1), list(qs2))
240
241 @skipUnlessDBFeature("supports_subqueries_in_group_by")
242 def test_annotate_with_extra(self):
243 """
244 Regression test for #11916: Extra params + aggregation creates
245 incorrect SQL.
246 """
247 # Oracle doesn't support subqueries in group by clause
248 shortest_book_sql = """
249 SELECT name
250 FROM aggregation_regress_book b
251 WHERE b.publisher_id = aggregation_regress_publisher.id
252 ORDER BY b.pages
253 LIMIT 1
254 """
255 # tests that this query does not raise a DatabaseError due to the full
256 # subselect being (erroneously) added to the GROUP BY parameters
257 qs = Publisher.objects.extra(
258 select={
259 "name_of_shortest_book": shortest_book_sql,
260 }
261 ).annotate(total_books=Count("book"))
262 # force execution of the query
263 list(qs)
264
265 def test_aggregate(self):
266 # Ordering requests are ignored
267 self.assertEqual(
268 Author.objects.order_by("name").aggregate(Avg("age")),
269 {"age__avg": Approximate(37.444, places=1)},
270 )
271
272 # Implicit ordering is also ignored
273 self.assertEqual(
274 Book.objects.aggregate(Sum("pages")),
275 {"pages__sum": 3703},
276 )
277
278 # Baseline results
279 self.assertEqual(
280 Book.objects.aggregate(Sum("pages"), Avg("pages")),
281 {"pages__sum": 3703, "pages__avg": Approximate(617.166, places=2)},
282 )
283
284 # Empty values query doesn't affect grouping or results
285 self.assertEqual(
286 Book.objects.values().aggregate(Sum("pages"), Avg("pages")),
287 {"pages__sum": 3703, "pages__avg": Approximate(617.166, places=2)},
288 )
289
290 # Aggregate overrides extra selected column
291 self.assertEqual(
292 Book.objects.extra(select={"price_per_page": "price / pages"}).aggregate(
293 Sum("pages")
294 ),
295 {"pages__sum": 3703},
296 )
297
298 def test_annotation(self):
299 # Annotations get combined with extra select clauses
300 obj = (
301 Book.objects.annotate(mean_auth_age=Avg("authors__age"))
302 .extra(select={"manufacture_cost": "price * .5"})
303 .get(pk=self.b2.pk)
304 )
305 self.assertObjectAttrs(
306 obj,
307 contact_id=self.a3.id,
308 isbn="067232959",
309 mean_auth_age=45.0,
310 name="Sams Teach Yourself Django in 24 Hours",
311 pages=528,
312 price=Decimal("23.09"),
313 pubdate=datetime.date(2008, 3, 3),
314 publisher_id=self.p2.id,
315 rating=3.0,
316 )
317 # Different DB backends return different types for the extra select computation
318 self.assertIn(obj.manufacture_cost, (11.545, Decimal("11.545")))
319
320 # Order of the annotate/extra in the query doesn't matter
321 obj = (
322 Book.objects.extra(select={"manufacture_cost": "price * .5"})
323 .annotate(mean_auth_age=Avg("authors__age"))
324 .get(pk=self.b2.pk)
325 )
326 self.assertObjectAttrs(
327 obj,
328 contact_id=self.a3.id,
329 isbn="067232959",
330 mean_auth_age=45.0,
331 name="Sams Teach Yourself Django in 24 Hours",
332 pages=528,
333 price=Decimal("23.09"),
334 pubdate=datetime.date(2008, 3, 3),
335 publisher_id=self.p2.id,
336 rating=3.0,
337 )
338 # Different DB backends return different types for the extra select computation
339 self.assertIn(obj.manufacture_cost, (11.545, Decimal("11.545")))
340
341 # Values queries can be combined with annotate and extra
342 obj = (
343 Book.objects.annotate(mean_auth_age=Avg("authors__age"))
344 .extra(select={"manufacture_cost": "price * .5"})
345 .values()
346 .get(pk=self.b2.pk)
347 )
348 manufacture_cost = obj["manufacture_cost"]
349 self.assertIn(manufacture_cost, (11.545, Decimal("11.545")))
350 del obj["manufacture_cost"]
351 self.assertEqual(
352 obj,
353 {
354 "id": self.b2.id,
355 "contact_id": self.a3.id,
356 "isbn": "067232959",
357 "mean_auth_age": 45.0,
358 "name": "Sams Teach Yourself Django in 24 Hours",
359 "pages": 528,
360 "price": Decimal("23.09"),
361 "pubdate": datetime.date(2008, 3, 3),
362 "publisher_id": self.p2.id,
363 "rating": 3.0,
364 },
365 )
366
367 # The order of the (empty) values, annotate and extra clauses doesn't
368 # matter
369 obj = (
370 Book.objects.values()
371 .annotate(mean_auth_age=Avg("authors__age"))
372 .extra(select={"manufacture_cost": "price * .5"})
373 .get(pk=self.b2.pk)
374 )
375 manufacture_cost = obj["manufacture_cost"]
376 self.assertIn(manufacture_cost, (11.545, Decimal("11.545")))
377 del obj["manufacture_cost"]
378 self.assertEqual(
379 obj,
380 {
381 "id": self.b2.id,
382 "contact_id": self.a3.id,
383 "isbn": "067232959",
384 "mean_auth_age": 45.0,
385 "name": "Sams Teach Yourself Django in 24 Hours",
386 "pages": 528,
387 "price": Decimal("23.09"),
388 "pubdate": datetime.date(2008, 3, 3),
389 "publisher_id": self.p2.id,
390 "rating": 3.0,
391 },
392 )
393
394 # If the annotation precedes the values clause, it won't be included
395 # unless it is explicitly named
396 obj = (
397 Book.objects.annotate(mean_auth_age=Avg("authors__age"))
398 .extra(select={"price_per_page": "price / pages"})
399 .values("name")
400 .get(pk=self.b1.pk)
401 )
402 self.assertEqual(
403 obj,
404 {
405 "name": "The Definitive Guide to Django: Web Development Done Right",
406 },
407 )
408
409 obj = (
410 Book.objects.annotate(mean_auth_age=Avg("authors__age"))
411 .extra(select={"price_per_page": "price / pages"})
412 .values("name", "mean_auth_age")
413 .get(pk=self.b1.pk)
414 )
415 self.assertEqual(
416 obj,
417 {
418 "mean_auth_age": 34.5,
419 "name": "The Definitive Guide to Django: Web Development Done Right",
420 },
421 )
422
423 # If an annotation isn't included in the values, it can still be used
424 # in a filter
425 qs = (
426 Book.objects.annotate(n_authors=Count("authors"))
427 .values("name")
428 .filter(n_authors__gt=2)
429 )
430 self.assertSequenceEqual(
431 qs,
432 [{"name": "Python Web Development with Django"}],
433 )
434
435 # The annotations are added to values output if values() precedes
436 # annotate()
437 obj = (
438 Book.objects.values("name")
439 .annotate(mean_auth_age=Avg("authors__age"))
440 .extra(select={"price_per_page": "price / pages"})
441 .get(pk=self.b1.pk)
442 )
443 self.assertEqual(
444 obj,
445 {
446 "mean_auth_age": 34.5,
447 "name": "The Definitive Guide to Django: Web Development Done Right",
448 },
449 )
450
451 # All of the objects are getting counted (allow_nulls) and that values
452 # respects the amount of objects
453 self.assertEqual(len(Author.objects.annotate(Avg("friends__age")).values()), 9)
454
455 # Consecutive calls to annotate accumulate in the query
456 qs = (
457 Book.objects.values("price")
458 .annotate(oldest=Max("authors__age"))
459 .order_by("oldest", "price")
460 .annotate(Max("publisher__num_awards"))
461 )
462 self.assertSequenceEqual(
463 qs,
464 [
465 {"price": Decimal("30"), "oldest": 35, "publisher__num_awards__max": 3},
466 {
467 "price": Decimal("29.69"),
468 "oldest": 37,
469 "publisher__num_awards__max": 7,
470 },
471 {
472 "price": Decimal("23.09"),
473 "oldest": 45,
474 "publisher__num_awards__max": 1,
475 },
476 {"price": Decimal("75"), "oldest": 57, "publisher__num_awards__max": 9},
477 {
478 "price": Decimal("82.8"),
479 "oldest": 57,
480 "publisher__num_awards__max": 7,
481 },
482 ],
483 )
484
485 def test_aggregate_annotation(self):
486 # Aggregates can be composed over annotations.
487 # The return type is derived from the composed aggregate
488 vals = Book.objects.annotate(num_authors=Count("authors__id")).aggregate(
489 Max("pages"), Max("price"), Sum("num_authors"), Avg("num_authors")
490 )
491 self.assertEqual(
492 vals,
493 {
494 "num_authors__sum": 10,
495 "num_authors__avg": Approximate(1.666, places=2),
496 "pages__max": 1132,
497 "price__max": Decimal("82.80"),
498 },
499 )
500
501 # Regression for #15624 - Missing SELECT columns when using values, annotate
502 # and aggregate in a single query
503 self.assertEqual(
504 Book.objects.annotate(c=Count("authors")).values("c").aggregate(Max("c")),
505 {"c__max": 3},
506 )
507
508 def test_conditional_aggregate(self):
509 # Conditional aggregation of a grouped queryset.
510 self.assertEqual(
511 Book.objects.annotate(c=Count("authors"))
512 .values("pk")
513 .aggregate(test=Sum(Case(When(c__gt=1, then=1))))["test"],
514 3,
515 )
516
517 def test_sliced_conditional_aggregate(self):
518 self.assertEqual(
519 Author.objects.order_by("pk")[:5].aggregate(
520 test=Sum(Case(When(age__lte=35, then=1)))
521 )["test"],
522 3,
523 )
524
525 def test_annotated_conditional_aggregate(self):
526 annotated_qs = Book.objects.annotate(
527 discount_price=F("price") * Decimal("0.75")
528 )
529 self.assertAlmostEqual(
530 annotated_qs.aggregate(
531 test=Avg(
532 Case(
533 When(pages__lt=400, then="discount_price"),
534 output_field=DecimalField(),
535 )
536 )
537 )["test"],
538 Decimal("22.27"),
539 places=2,
540 )
541
542 def test_distinct_conditional_aggregate(self):
543 self.assertEqual(
544 Book.objects.distinct().aggregate(
545 test=Avg(
546 Case(
547 When(price=Decimal("29.69"), then="pages"),
548 output_field=IntegerField(),
549 )
550 )
551 )["test"],
552 325,
553 )
554
555 def test_conditional_aggregate_on_complex_condition(self):
556 self.assertEqual(
557 Book.objects.distinct().aggregate(
558 test=Avg(
559 Case(
560 When(
561 Q(price__gte=Decimal("29")) & Q(price__lt=Decimal("30")),
562 then="pages",
563 ),
564 output_field=IntegerField(),
565 )
566 )
567 )["test"],
568 325,
569 )
570
571 def test_q_annotation_aggregate(self):
572 self.assertEqual(Book.objects.annotate(has_pk=Q(pk__isnull=False)).count(), 6)
573
574 def test_decimal_aggregate_annotation_filter(self):
575 """
576 Filtering on an aggregate annotation with Decimal values should work.
577 Requires special handling on SQLite (#18247).
578 """
579 self.assertEqual(
580 len(
581 Author.objects.annotate(sum=Sum("book_contact_set__price")).filter(
582 sum__gt=Decimal(40)
583 )
584 ),
585 1,
586 )
587 self.assertEqual(
588 len(
589 Author.objects.annotate(sum=Sum("book_contact_set__price")).filter(
590 sum__lte=Decimal(40)
591 )
592 ),
593 4,
594 )
595
596 def test_field_error(self):
597 # Bad field requests in aggregates are caught and reported
598 msg = (
599 "Cannot resolve keyword 'foo' into field. Choices are: authors, "
600 "contact, contact_id, hardbackbook, id, isbn, name, pages, price, "
601 "pubdate, publisher, publisher_id, rating, store, tags"
602 )
603 with self.assertRaisesMessage(FieldError, msg):
604 Book.objects.aggregate(num_authors=Count("foo"))
605
606 with self.assertRaisesMessage(FieldError, msg):
607 Book.objects.annotate(num_authors=Count("foo"))
608
609 msg = (
610 "Cannot resolve keyword 'foo' into field. Choices are: authors, "
611 "contact, contact_id, hardbackbook, id, isbn, name, num_authors, "
612 "pages, price, pubdate, publisher, publisher_id, rating, store, tags"
613 )
614 with self.assertRaisesMessage(FieldError, msg):
615 Book.objects.annotate(num_authors=Count("authors__id")).aggregate(
616 Max("foo")
617 )
618
619 def test_more(self):
620 # Old-style count aggregations can be mixed with new-style
621 self.assertEqual(Book.objects.annotate(num_authors=Count("authors")).count(), 6)
622
623 # Non-ordinal, non-computed Aggregates over annotations correctly
624 # inherit the annotation's internal type if the annotation is ordinal
625 # or computed
626 vals = Book.objects.annotate(num_authors=Count("authors")).aggregate(
627 Max("num_authors")
628 )
629 self.assertEqual(vals, {"num_authors__max": 3})
630
631 vals = Publisher.objects.annotate(avg_price=Avg("book__price")).aggregate(
632 Max("avg_price")
633 )
634 self.assertEqual(vals, {"avg_price__max": 75.0})
635
636 # Aliases are quoted to protected aliases that might be reserved names
637 vals = Book.objects.aggregate(number=Max("pages"), select=Max("pages"))
638 self.assertEqual(vals, {"number": 1132, "select": 1132})
639
640 # Regression for #10064: select_related() plays nice with aggregates
641 obj = (
642 Book.objects.select_related("publisher")
643 .annotate(num_authors=Count("authors"))
644 .values()
645 .get(isbn="013790395")
646 )
647 self.assertEqual(
648 obj,
649 {
650 "contact_id": self.a8.id,
651 "id": self.b5.id,
652 "isbn": "013790395",
653 "name": "Artificial Intelligence: A Modern Approach",
654 "num_authors": 2,
655 "pages": 1132,
656 "price": Decimal("82.8"),
657 "pubdate": datetime.date(1995, 1, 15),
658 "publisher_id": self.p3.id,
659 "rating": 4.0,
660 },
661 )
662
663 # Regression for #10010: exclude on an aggregate field is correctly
664 # negated
665 self.assertEqual(len(Book.objects.annotate(num_authors=Count("authors"))), 6)
666 self.assertEqual(
667 len(
668 Book.objects.annotate(num_authors=Count("authors")).filter(
669 num_authors__gt=2
670 )
671 ),
672 1,
673 )
674 self.assertEqual(
675 len(
676 Book.objects.annotate(num_authors=Count("authors")).exclude(
677 num_authors__gt=2
678 )
679 ),
680 5,
681 )
682
683 self.assertEqual(
684 len(
685 Book.objects.annotate(num_authors=Count("authors"))
686 .filter(num_authors__lt=3)
687 .exclude(num_authors__lt=2)
688 ),
689 2,
690 )
691 self.assertEqual(
692 len(
693 Book.objects.annotate(num_authors=Count("authors"))
694 .exclude(num_authors__lt=2)
695 .filter(num_authors__lt=3)
696 ),
697 2,
698 )
699
700 def test_aggregate_fexpr(self):
701 # Aggregates can be used with F() expressions
702 # ... where the F() is pushed into the HAVING clause
703 qs = (
704 Publisher.objects.annotate(num_books=Count("book"))
705 .filter(num_books__lt=F("num_awards") / 2)
706 .order_by("name")
707 .values("name", "num_books", "num_awards")
708 )
709 self.assertSequenceEqual(
710 qs,
711 [
712 {"num_books": 1, "name": "Morgan Kaufmann", "num_awards": 9},
713 {"num_books": 2, "name": "Prentice Hall", "num_awards": 7},
714 ],
715 )
716
717 qs = (
718 Publisher.objects.annotate(num_books=Count("book"))
719 .exclude(num_books__lt=F("num_awards") / 2)
720 .order_by("name")
721 .values("name", "num_books", "num_awards")
722 )
723 self.assertSequenceEqual(
724 qs,
725 [
726 {"num_books": 2, "name": "Apress", "num_awards": 3},
727 {"num_books": 0, "name": "Jonno's House of Books", "num_awards": 0},
728 {"num_books": 1, "name": "Sams", "num_awards": 1},
729 ],
730 )
731
732 # ... and where the F() references an aggregate
733 qs = (
734 Publisher.objects.annotate(num_books=Count("book"))
735 .filter(num_awards__gt=2 * F("num_books"))
736 .order_by("name")
737 .values("name", "num_books", "num_awards")
738 )
739 self.assertSequenceEqual(
740 qs,
741 [
742 {"num_books": 1, "name": "Morgan Kaufmann", "num_awards": 9},
743 {"num_books": 2, "name": "Prentice Hall", "num_awards": 7},
744 ],
745 )
746
747 qs = (
748 Publisher.objects.annotate(num_books=Count("book"))
749 .exclude(num_books__lt=F("num_awards") / 2)
750 .order_by("name")
751 .values("name", "num_books", "num_awards")
752 )
753 self.assertSequenceEqual(
754 qs,
755 [
756 {"num_books": 2, "name": "Apress", "num_awards": 3},
757 {"num_books": 0, "name": "Jonno's House of Books", "num_awards": 0},
758 {"num_books": 1, "name": "Sams", "num_awards": 1},
759 ],
760 )
761
762 def test_db_col_table(self):
763 # Tests on fields with non-default table and column names.
764 qs = Clues.objects.values("EntryID__Entry").annotate(
765 Appearances=Count("EntryID"), Distinct_Clues=Count("Clue", distinct=True)
766 )
767 self.assertSequenceEqual(qs, [])
768
769 qs = Entries.objects.annotate(clue_count=Count("clues__ID"))
770 self.assertSequenceEqual(qs, [])
771
772 def test_boolean_conversion(self):
773 # Aggregates mixed up ordering of columns for backend's convert_values
774 # method. Refs #21126.
775 e = Entries.objects.create(Entry="foo")
776 c = Clues.objects.create(EntryID=e, Clue="bar")
777 qs = Clues.objects.select_related("EntryID").annotate(Count("ID"))
778 self.assertSequenceEqual(qs, [c])
779 self.assertEqual(qs[0].EntryID, e)
780 self.assertIs(qs[0].EntryID.Exclude, False)
781
782 def test_empty(self):
783 # Regression for #10089: Check handling of empty result sets with
784 # aggregates
785 self.assertEqual(Book.objects.filter(id__in=[]).count(), 0)
786
787 vals = Book.objects.filter(id__in=[]).aggregate(
788 num_authors=Count("authors"),
789 avg_authors=Avg("authors"),
790 max_authors=Max("authors"),
791 max_price=Max("price"),
792 max_rating=Max("rating"),
793 )
794 self.assertEqual(
795 vals,
796 {
797 "max_authors": None,
798 "max_rating": None,
799 "num_authors": 0,
800 "avg_authors": None,
801 "max_price": None,
802 },
803 )
804
805 qs = (
806 Publisher.objects.filter(name="Jonno's House of Books")
807 .annotate(
808 num_authors=Count("book__authors"),
809 avg_authors=Avg("book__authors"),
810 max_authors=Max("book__authors"),
811 max_price=Max("book__price"),
812 max_rating=Max("book__rating"),
813 )
814 .values()
815 )
816 self.assertSequenceEqual(
817 qs,
818 [
819 {
820 "max_authors": None,
821 "name": "Jonno's House of Books",
822 "num_awards": 0,
823 "max_price": None,
824 "num_authors": 0,
825 "max_rating": None,
826 "id": self.p5.id,
827 "avg_authors": None,
828 }
829 ],
830 )
831
832 def test_more_more(self):
833 # Regression for #10113 - Fields mentioned in order_by() must be
834 # included in the GROUP BY. This only becomes a problem when the
835 # order_by introduces a new join.
836 self.assertQuerySetEqual(
837 Book.objects.annotate(num_authors=Count("authors")).order_by(
838 "publisher__name", "name"
839 ),
840 [
841 "Practical Django Projects",
842 "The Definitive Guide to Django: Web Development Done Right",
843 "Paradigms of Artificial Intelligence Programming: Case Studies in "
844 "Common Lisp",
845 "Artificial Intelligence: A Modern Approach",
846 "Python Web Development with Django",
847 "Sams Teach Yourself Django in 24 Hours",
848 ],
849 lambda b: b.name,
850 )
851
852 # Regression for #10127 - Empty select_related() works with annotate
853 qs = (
854 Book.objects.filter(rating__lt=4.5)
855 .select_related()
856 .annotate(Avg("authors__age"))
857 .order_by("name")
858 )
859 self.assertQuerySetEqual(
860 qs,
861 [
862 (
863 "Artificial Intelligence: A Modern Approach",
864 51.5,
865 "Prentice Hall",
866 "Peter Norvig",
867 ),
868 ("Practical Django Projects", 29.0, "Apress", "James Bennett"),
869 (
870 "Python Web Development with Django",
871 Approximate(30.333, places=2),
872 "Prentice Hall",
873 "Jeffrey Forcier",
874 ),
875 ("Sams Teach Yourself Django in 24 Hours", 45.0, "Sams", "Brad Dayley"),
876 ],
877 lambda b: (b.name, b.authors__age__avg, b.publisher.name, b.contact.name),
878 )
879
880 # Regression for #10132 - If the values() clause only mentioned extra
881 # (select=) columns, those columns are used for grouping
882 qs = (
883 Book.objects.extra(select={"pub": "publisher_id"})
884 .values("pub")
885 .annotate(Count("id"))
886 .order_by("pub")
887 )
888 self.assertSequenceEqual(
889 qs,
890 [
891 {"pub": self.p1.id, "id__count": 2},
892 {"pub": self.p2.id, "id__count": 1},
893 {"pub": self.p3.id, "id__count": 2},
894 {"pub": self.p4.id, "id__count": 1},
895 ],
896 )
897
898 qs = (
899 Book.objects.extra(select={"pub": "publisher_id", "foo": "pages"})
900 .values("pub")
901 .annotate(Count("id"))
902 .order_by("pub")
903 )
904 self.assertSequenceEqual(
905 qs,
906 [
907 {"pub": self.p1.id, "id__count": 2},
908 {"pub": self.p2.id, "id__count": 1},
909 {"pub": self.p3.id, "id__count": 2},
910 {"pub": self.p4.id, "id__count": 1},
911 ],
912 )
913
914 # Regression for #10182 - Queries with aggregate calls are correctly
915 # realiased when used in a subquery
916 ids = (
917 Book.objects.filter(pages__gt=100)
918 .annotate(n_authors=Count("authors"))
919 .filter(n_authors__gt=2)
920 .order_by("n_authors")
921 )
922 self.assertQuerySetEqual(
923 Book.objects.filter(id__in=ids),
924 [
925 "Python Web Development with Django",
926 ],
927 lambda b: b.name,
928 )
929
930 # Regression for #15709 - Ensure each group_by field only exists once
931 # per query
932 qstr = str(
933 Book.objects.values("publisher")
934 .annotate(max_pages=Max("pages"))
935 .order_by()
936 .query
937 )
938 # There is just one GROUP BY clause (zero commas means at most one clause).
939 self.assertEqual(qstr[qstr.index("GROUP BY") :].count(", "), 0)
940
941 def test_duplicate_alias(self):
942 # Regression for #11256 - duplicating a default alias raises ValueError.
943 msg = (
944 "The named annotation 'authors__age__avg' conflicts with "
945 "the default name for another annotation."
946 )
947 with self.assertRaisesMessage(ValueError, msg):
948 Book.objects.annotate(
949 Avg("authors__age"), authors__age__avg=Avg("authors__age")
950 )
951
952 def test_field_name_conflict(self):
953 # Regression for #11256 - providing an aggregate name
954 # that conflicts with a field name on the model raises ValueError
955 msg = "The annotation 'age' conflicts with a field on the model."
956 with self.assertRaisesMessage(ValueError, msg):
957 Author.objects.annotate(age=Avg("friends__age"))
958
959 def test_m2m_name_conflict(self):
960 # Regression for #11256 - providing an aggregate name
961 # that conflicts with an m2m name on the model raises ValueError
962 msg = "The annotation 'friends' conflicts with a field on the model."
963 with self.assertRaisesMessage(ValueError, msg):
964 Author.objects.annotate(friends=Count("friends"))
965
966 def test_fk_attname_conflict(self):
967 msg = "The annotation 'contact_id' conflicts with a field on the model."
968 with self.assertRaisesMessage(ValueError, msg):
969 Book.objects.annotate(contact_id=F("publisher_id"))
970
971 def test_values_queryset_non_conflict(self):
972 # If you're using a values query set, some potential conflicts are
973 # avoided.
974 # age is a field on Author, so it shouldn't be allowed as an aggregate.
975 # But age isn't included in values(), so it is.
976 results = (
977 Author.objects.values("name")
978 .annotate(age=Count("book_contact_set"))
979 .order_by("name")
980 )
981 self.assertEqual(len(results), 9)
982 self.assertEqual(results[0]["name"], "Adrian Holovaty")
983 self.assertEqual(results[0]["age"], 1)
984
985 # Same problem, but aggregating over m2m fields
986 results = (
987 Author.objects.values("name")
988 .annotate(age=Avg("friends__age"))
989 .order_by("name")
990 )
991 self.assertEqual(len(results), 9)
992 self.assertEqual(results[0]["name"], "Adrian Holovaty")
993 self.assertEqual(results[0]["age"], 32.0)
994
995 # Same problem, but colliding with an m2m field
996 results = (
997 Author.objects.values("name")
998 .annotate(friends=Count("friends"))
999 .order_by("name")
1000 )
1001 self.assertEqual(len(results), 9)
1002 self.assertEqual(results[0]["name"], "Adrian Holovaty")
1003 self.assertEqual(results[0]["friends"], 2)
1004
1005 def test_reverse_relation_name_conflict(self):
1006 # Regression for #11256 - providing an aggregate name
1007 # that conflicts with a reverse-related name on the model raises ValueError
1008 msg = "The annotation 'book_contact_set' conflicts with a field on the model."
1009 with self.assertRaisesMessage(ValueError, msg):
1010 Author.objects.annotate(book_contact_set=Avg("friends__age"))
1011
1012 def test_pickle(self):
1013 # Regression for #10197 -- Queries with aggregates can be pickled.
1014 # First check that pickling is possible at all. No crash = success
1015 qs = Book.objects.annotate(num_authors=Count("authors"))
1016 pickle.dumps(qs)
1017
1018 # Then check that the round trip works.
1019 query = qs.query.get_compiler(qs.db).as_sql()[0]
1020 qs2 = pickle.loads(pickle.dumps(qs))
1021 self.assertEqual(
1022 qs2.query.get_compiler(qs2.db).as_sql()[0],
1023 query,
1024 )
1025
1026 def test_more_more_more(self):
1027 # Regression for #10199 - Aggregate calls clone the original query so
1028 # the original query can still be used
1029 books = Book.objects.all()
1030 books.aggregate(Avg("authors__age"))
1031 self.assertQuerySetEqual(
1032 books.all(),
1033 [
1034 "Artificial Intelligence: A Modern Approach",
1035 "Paradigms of Artificial Intelligence Programming: Case Studies in "
1036 "Common Lisp",
1037 "Practical Django Projects",
1038 "Python Web Development with Django",
1039 "Sams Teach Yourself Django in 24 Hours",
1040 "The Definitive Guide to Django: Web Development Done Right",
1041 ],
1042 lambda b: b.name,
1043 )
1044
1045 # Regression for #10248 - Annotations work with dates()
1046 qs = (
1047 Book.objects.annotate(num_authors=Count("authors"))
1048 .filter(num_authors=2)
1049 .dates("pubdate", "day")
1050 )
1051 self.assertSequenceEqual(
1052 qs,
1053 [
1054 datetime.date(1995, 1, 15),
1055 datetime.date(2007, 12, 6),
1056 ],
1057 )
1058
1059 # Regression for #10290 - extra selects with parameters can be used for
1060 # grouping.
1061 qs = (
1062 Book.objects.annotate(mean_auth_age=Avg("authors__age"))
1063 .extra(select={"sheets": "(pages + %s) / %s"}, select_params=[1, 2])
1064 .order_by("sheets")
1065 .values("sheets")
1066 )
1067 self.assertQuerySetEqual(
1068 qs, [150, 175, 224, 264, 473, 566], lambda b: int(b["sheets"])
1069 )
1070
1071 # Regression for 10425 - annotations don't get in the way of a count()
1072 # clause
1073 self.assertEqual(
1074 Book.objects.values("publisher").annotate(Count("publisher")).count(), 4
1075 )
1076 self.assertEqual(
1077 Book.objects.annotate(Count("publisher")).values("publisher").count(), 6
1078 )
1079
1080 # Note: intentionally no order_by(), that case needs tests, too.
1081 publishers = Publisher.objects.filter(id__in=[self.p1.id, self.p2.id])
1082 self.assertEqual(sorted(p.name for p in publishers), ["Apress", "Sams"])
1083
1084 publishers = publishers.annotate(n_books=Count("book"))
1085 sorted_publishers = sorted(publishers, key=lambda x: x.name)
1086 self.assertEqual(sorted_publishers[0].n_books, 2)
1087 self.assertEqual(sorted_publishers[1].n_books, 1)
1088
1089 self.assertEqual(sorted(p.name for p in publishers), ["Apress", "Sams"])
1090
1091 books = Book.objects.filter(publisher__in=publishers)
1092 self.assertQuerySetEqual(
1093 books,
1094 [
1095 "Practical Django Projects",
1096 "Sams Teach Yourself Django in 24 Hours",
1097 "The Definitive Guide to Django: Web Development Done Right",
1098 ],
1099 lambda b: b.name,
1100 )
1101 self.assertEqual(sorted(p.name for p in publishers), ["Apress", "Sams"])
1102
1103 # Regression for 10666 - inherited fields work with annotations and
1104 # aggregations
1105 self.assertEqual(
1106 HardbackBook.objects.aggregate(n_pages=Sum("book_ptr__pages")),
1107 {"n_pages": 2078},
1108 )
1109
1110 self.assertEqual(
1111 HardbackBook.objects.aggregate(n_pages=Sum("pages")),
1112 {"n_pages": 2078},
1113 )
1114
1115 qs = (
1116 HardbackBook.objects.annotate(
1117 n_authors=Count("book_ptr__authors"),
1118 )
1119 .values("name", "n_authors")
1120 .order_by("name")
1121 )
1122 self.assertSequenceEqual(
1123 qs,
1124 [
1125 {"n_authors": 2, "name": "Artificial Intelligence: A Modern Approach"},
1126 {
1127 "n_authors": 1,
1128 "name": (
1129 "Paradigms of Artificial Intelligence Programming: Case "
1130 "Studies in Common Lisp"
1131 ),
1132 },
1133 ],
1134 )
1135
1136 qs = (
1137 HardbackBook.objects.annotate(n_authors=Count("authors"))
1138 .values("name", "n_authors")
1139 .order_by("name")
1140 )
1141 self.assertSequenceEqual(
1142 qs,
1143 [
1144 {"n_authors": 2, "name": "Artificial Intelligence: A Modern Approach"},
1145 {
1146 "n_authors": 1,
1147 "name": (
1148 "Paradigms of Artificial Intelligence Programming: Case "
1149 "Studies in Common Lisp"
1150 ),
1151 },
1152 ],
1153 )
1154
1155 # Regression for #10766 - Shouldn't be able to reference an aggregate
1156 # fields in an aggregate() call.
1157 msg = "Cannot compute Avg('mean_age'): 'mean_age' is an aggregate"
1158 with self.assertRaisesMessage(FieldError, msg):
1159 Book.objects.annotate(mean_age=Avg("authors__age")).annotate(
1160 Avg("mean_age")
1161 )
1162
1163 def test_empty_filter_count(self):
1164 self.assertEqual(
1165 Author.objects.filter(id__in=[]).annotate(Count("friends")).count(), 0
1166 )
1167
1168 def test_empty_filter_aggregate(self):
1169 self.assertEqual(
1170 Author.objects.filter(id__in=[])
1171 .annotate(Count("friends"))
1172 .aggregate(Count("pk")),
1173 {"pk__count": 0},
1174 )
1175
1176 def test_none_call_before_aggregate(self):
1177 # Regression for #11789
1178 self.assertEqual(
1179 Author.objects.none().aggregate(Avg("age")), {"age__avg": None}
1180 )
1181
1182 def test_annotate_and_join(self):
1183 self.assertEqual(
1184 Author.objects.annotate(c=Count("friends__name"))
1185 .exclude(friends__name="Joe")
1186 .count(),
1187 Author.objects.count(),
1188 )
1189
1190 def test_f_expression_annotation(self):
1191 # Books with less than 200 pages per author.
1192 qs = (
1193 Book.objects.values("name")
1194 .annotate(n_authors=Count("authors"))
1195 .filter(pages__lt=F("n_authors") * 200)
1196 .values_list("pk")
1197 )
1198 self.assertQuerySetEqual(
1199 Book.objects.filter(pk__in=qs),
1200 ["Python Web Development with Django"],
1201 attrgetter("name"),
1202 )
1203
1204 def test_values_annotate_values(self):
1205 qs = (
1206 Book.objects.values("name")
1207 .annotate(n_authors=Count("authors"))
1208 .values_list("pk", flat=True)
1209 .order_by("name")
1210 )
1211 self.assertEqual(list(qs), list(Book.objects.values_list("pk", flat=True)))
1212
1213 def test_having_group_by(self):
1214 # When a field occurs on the LHS of a HAVING clause that it
1215 # appears correctly in the GROUP BY clause
1216 qs = (
1217 Book.objects.values_list("name")
1218 .annotate(n_authors=Count("authors"))
1219 .filter(pages__gt=F("n_authors"))
1220 .values_list("name", flat=True)
1221 .order_by("name")
1222 )
1223 # Results should be the same, all Books have more pages than authors
1224 self.assertEqual(list(qs), list(Book.objects.values_list("name", flat=True)))
1225
1226 def test_values_list_annotation_args_ordering(self):
1227 """
1228 Annotate *args ordering should be preserved in values_list results.
1229 **kwargs comes after *args.
1230 Regression test for #23659.
1231 """
1232 books = (
1233 Book.objects.values_list("publisher__name")
1234 .annotate(
1235 Count("id"), Avg("price"), Avg("authors__age"), avg_pgs=Avg("pages")
1236 )
1237 .order_by("-publisher__name")
1238 )
1239 self.assertEqual(books[0], ("Sams", 1, Decimal("23.09"), 45.0, 528.0))
1240
1241 def test_annotation_disjunction(self):
1242 qs = (
1243 Book.objects.annotate(n_authors=Count("authors"))
1244 .filter(Q(n_authors=2) | Q(name="Python Web Development with Django"))
1245 .order_by("name")
1246 )
1247 self.assertQuerySetEqual(
1248 qs,
1249 [
1250 "Artificial Intelligence: A Modern Approach",
1251 "Python Web Development with Django",
1252 "The Definitive Guide to Django: Web Development Done Right",
1253 ],
1254 attrgetter("name"),
1255 )
1256
1257 qs = (
1258 Book.objects.annotate(n_authors=Count("authors")).filter(
1259 Q(name="The Definitive Guide to Django: Web Development Done Right")
1260 | (
1261 Q(name="Artificial Intelligence: A Modern Approach")
1262 & Q(n_authors=3)
1263 )
1264 )
1265 ).order_by("name")
1266 self.assertQuerySetEqual(
1267 qs,
1268 [
1269 "The Definitive Guide to Django: Web Development Done Right",
1270 ],
1271 attrgetter("name"),
1272 )
1273
1274 qs = (
1275 Publisher.objects.annotate(
1276 rating_sum=Sum("book__rating"), book_count=Count("book")
1277 )
1278 .filter(Q(rating_sum__gt=5.5) | Q(rating_sum__isnull=True))
1279 .order_by("pk")
1280 )
1281 self.assertQuerySetEqual(
1282 qs,
1283 [
1284 "Apress",
1285 "Prentice Hall",
1286 "Jonno's House of Books",
1287 ],
1288 attrgetter("name"),
1289 )
1290
1291 qs = (
1292 Publisher.objects.annotate(
1293 rating_sum=Sum("book__rating"), book_count=Count("book")
1294 )
1295 .filter(Q(rating_sum__gt=F("book_count")) | Q(rating_sum=None))
1296 .order_by("num_awards")
1297 )
1298 self.assertQuerySetEqual(
1299 qs,
1300 [
1301 "Jonno's House of Books",
1302 "Sams",
1303 "Apress",
1304 "Prentice Hall",
1305 "Morgan Kaufmann",
1306 ],
1307 attrgetter("name"),
1308 )
1309
1310 def test_quoting_aggregate_order_by(self):
1311 qs = (
1312 Book.objects.filter(name="Python Web Development with Django")
1313 .annotate(authorCount=Count("authors"))
1314 .order_by("authorCount")
1315 )
1316 self.assertQuerySetEqual(
1317 qs,
1318 [
1319 ("Python Web Development with Django", 3),
1320 ],
1321 lambda b: (b.name, b.authorCount),
1322 )
1323
1324 def test_stddev(self):
1325 self.assertEqual(
1326 Book.objects.aggregate(StdDev("pages")),
1327 {"pages__stddev": Approximate(311.46, 1)},
1328 )
1329
1330 self.assertEqual(
1331 Book.objects.aggregate(StdDev("rating")),
1332 {"rating__stddev": Approximate(0.60, 1)},
1333 )
1334
1335 self.assertEqual(
1336 Book.objects.aggregate(StdDev("price")),
1337 {"price__stddev": Approximate(Decimal("24.16"), 2)},
1338 )
1339
1340 self.assertEqual(
1341 Book.objects.aggregate(StdDev("pages", sample=True)),
1342 {"pages__stddev": Approximate(341.19, 2)},
1343 )
1344
1345 self.assertEqual(
1346 Book.objects.aggregate(StdDev("rating", sample=True)),
1347 {"rating__stddev": Approximate(0.66, 2)},
1348 )
1349
1350 self.assertEqual(
1351 Book.objects.aggregate(StdDev("price", sample=True)),
1352 {"price__stddev": Approximate(Decimal("26.46"), 1)},
1353 )
1354
1355 self.assertEqual(
1356 Book.objects.aggregate(Variance("pages")),
1357 {"pages__variance": Approximate(97010.80, 1)},
1358 )
1359
1360 self.assertEqual(
1361 Book.objects.aggregate(Variance("rating")),
1362 {"rating__variance": Approximate(0.36, 1)},
1363 )
1364
1365 self.assertEqual(
1366 Book.objects.aggregate(Variance("price")),
1367 {"price__variance": Approximate(Decimal("583.77"), 1)},
1368 )
1369
1370 self.assertEqual(
1371 Book.objects.aggregate(Variance("pages", sample=True)),
1372 {"pages__variance": Approximate(116412.96, 1)},
1373 )
1374
1375 self.assertEqual(
1376 Book.objects.aggregate(Variance("rating", sample=True)),
1377 {"rating__variance": Approximate(0.44, 2)},
1378 )
1379
1380 self.assertEqual(
1381 Book.objects.aggregate(Variance("price", sample=True)),
1382 {"price__variance": Approximate(Decimal("700.53"), 2)},
1383 )
1384
1385 def test_filtering_by_annotation_name(self):
1386 # Regression test for #14476
1387
1388 # The name of the explicitly provided annotation name in this case
1389 # poses no problem
1390 qs = (
1391 Author.objects.annotate(book_cnt=Count("book"))
1392 .filter(book_cnt=2)
1393 .order_by("name")
1394 )
1395 self.assertQuerySetEqual(qs, ["Peter Norvig"], lambda b: b.name)
1396 # Neither in this case
1397 qs = (
1398 Author.objects.annotate(book_count=Count("book"))
1399 .filter(book_count=2)
1400 .order_by("name")
1401 )
1402 self.assertQuerySetEqual(qs, ["Peter Norvig"], lambda b: b.name)
1403 # This case used to fail because the ORM couldn't resolve the
1404 # automatically generated annotation name `book__count`
1405 qs = (
1406 Author.objects.annotate(Count("book"))
1407 .filter(book__count=2)
1408 .order_by("name")
1409 )
1410 self.assertQuerySetEqual(qs, ["Peter Norvig"], lambda b: b.name)
1411 # Referencing the auto-generated name in an aggregate() also works.
1412 self.assertEqual(
1413 Author.objects.annotate(Count("book")).aggregate(Max("book__count")),
1414 {"book__count__max": 2},
1415 )
1416
1417 def test_annotate_joins(self):
1418 """
1419 The base table's join isn't promoted to LOUTER. This could
1420 cause the query generation to fail if there is an exclude() for fk-field
1421 in the query, too. Refs #19087.
1422 """
1423 qs = Book.objects.annotate(n=Count("pk"))
1424 self.assertIs(qs.query.alias_map["aggregation_regress_book"].join_type, None)
1425 # The query executes without problems.
1426 self.assertEqual(len(qs.exclude(publisher=-1)), 6)
1427
1428 @skipUnlessDBFeature("allows_group_by_selected_pks")
1429 def test_aggregate_duplicate_columns(self):
1430 # Regression test for #17144
1431
1432 results = Author.objects.annotate(num_contacts=Count("book_contact_set"))
1433
1434 # There should only be one GROUP BY clause, for the `id` column.
1435 # `name` and `age` should not be grouped on.
1436 _, _, group_by = results.query.get_compiler(using="default").pre_sql_setup()
1437 self.assertEqual(len(group_by), 1)
1438 self.assertIn("id", group_by[0][0])
1439 self.assertNotIn("name", group_by[0][0])
1440 self.assertNotIn("age", group_by[0][0])
1441 self.assertEqual(
1442 [(a.name, a.num_contacts) for a in results.order_by("name")],
1443 [
1444 ("Adrian Holovaty", 1),
1445 ("Brad Dayley", 1),
1446 ("Jacob Kaplan-Moss", 0),
1447 ("James Bennett", 1),
1448 ("Jeffrey Forcier", 1),
1449 ("Paul Bissex", 0),
1450 ("Peter Norvig", 2),
1451 ("Stuart Russell", 0),
1452 ("Wesley J. Chun", 0),
1453 ],
1454 )
1455
1456 @skipUnlessDBFeature("allows_group_by_selected_pks")
1457 def test_aggregate_duplicate_columns_only(self):
1458 # Works with only() too.
1459 results = Author.objects.only("id", "name").annotate(
1460 num_contacts=Count("book_contact_set")
1461 )
1462 _, _, grouping = results.query.get_compiler(using="default").pre_sql_setup()
1463 self.assertEqual(len(grouping), 1)
1464 self.assertIn("id", grouping[0][0])
1465 self.assertNotIn("name", grouping[0][0])
1466 self.assertNotIn("age", grouping[0][0])
1467 self.assertEqual(
1468 [(a.name, a.num_contacts) for a in results.order_by("name")],
1469 [
1470 ("Adrian Holovaty", 1),
1471 ("Brad Dayley", 1),
1472 ("Jacob Kaplan-Moss", 0),
1473 ("James Bennett", 1),
1474 ("Jeffrey Forcier", 1),
1475 ("Paul Bissex", 0),
1476 ("Peter Norvig", 2),
1477 ("Stuart Russell", 0),
1478 ("Wesley J. Chun", 0),
1479 ],
1480 )
1481
1482 @skipUnlessDBFeature("allows_group_by_selected_pks")
1483 def test_aggregate_duplicate_columns_select_related(self):
1484 # And select_related()
1485 results = Book.objects.select_related("contact").annotate(
1486 num_authors=Count("authors")
1487 )
1488 _, _, grouping = results.query.get_compiler(using="default").pre_sql_setup()
1489 self.assertEqual(len(grouping), 2)
1490 self.assertIn("id", grouping[0][0])
1491 self.assertNotIn("name", grouping[0][0])
1492 self.assertNotIn("contact", grouping[0][0])
1493 self.assertEqual(
1494 [(b.name, b.num_authors) for b in results.order_by("name")],
1495 [
1496 ("Artificial Intelligence: A Modern Approach", 2),
1497 (
1498 "Paradigms of Artificial Intelligence Programming: Case Studies in "
1499 "Common Lisp",
1500 1,
1501 ),
1502 ("Practical Django Projects", 1),
1503 ("Python Web Development with Django", 3),
1504 ("Sams Teach Yourself Django in 24 Hours", 1),
1505 ("The Definitive Guide to Django: Web Development Done Right", 2),
1506 ],
1507 )
1508
1509 @skipUnlessDBFeature("allows_group_by_selected_pks")
1510 def test_aggregate_unmanaged_model_columns(self):
1511 """
1512 Unmanaged models are sometimes used to represent database views which
1513 may not allow grouping by selected primary key.
1514 """
1515
1516 def assertQuerysetResults(queryset):
1517 self.assertEqual(
1518 [(b.name, b.num_authors) for b in queryset.order_by("name")],
1519 [
1520 ("Artificial Intelligence: A Modern Approach", 2),
1521 (
1522 "Paradigms of Artificial Intelligence Programming: Case "
1523 "Studies in Common Lisp",
1524 1,
1525 ),
1526 ("Practical Django Projects", 1),
1527 ("Python Web Development with Django", 3),
1528 ("Sams Teach Yourself Django in 24 Hours", 1),
1529 ("The Definitive Guide to Django: Web Development Done Right", 2),
1530 ],
1531 )
1532
1533 queryset = Book.objects.select_related("contact").annotate(
1534 num_authors=Count("authors")
1535 )
1536 # Unmanaged origin model.
1537 with mock.patch.object(Book._meta, "managed", False):
1538 _, _, grouping = queryset.query.get_compiler(
1539 using="default"
1540 ).pre_sql_setup()
1541 self.assertEqual(len(grouping), len(Book._meta.fields) + 1)
1542 for index, field in enumerate(Book._meta.fields):
1543 self.assertIn(field.name, grouping[index][0])
1544 self.assertIn(Author._meta.pk.name, grouping[-1][0])
1545 assertQuerysetResults(queryset)
1546 # Unmanaged related model.
1547 with mock.patch.object(Author._meta, "managed", False):
1548 _, _, grouping = queryset.query.get_compiler(
1549 using="default"
1550 ).pre_sql_setup()
1551 self.assertEqual(len(grouping), len(Author._meta.fields) + 1)
1552 self.assertIn(Book._meta.pk.name, grouping[0][0])
1553 for index, field in enumerate(Author._meta.fields):
1554 self.assertIn(field.name, grouping[index + 1][0])
1555 assertQuerysetResults(queryset)
1556
1557 @skipUnlessDBFeature("allows_group_by_selected_pks")
1558 def test_aggregate_unmanaged_model_as_tables(self):
1559 qs = Book.objects.select_related("contact").annotate(
1560 num_authors=Count("authors")
1561 )
1562 # Force treating unmanaged models as tables.
1563 with mock.patch(
1564 "django.db.connection.features.allows_group_by_selected_pks_on_model",
1565 return_value=True,
1566 ):
1567 with (
1568 mock.patch.object(Book._meta, "managed", False),
1569 mock.patch.object(Author._meta, "managed", False),
1570 ):
1571 _, _, grouping = qs.query.get_compiler(using="default").pre_sql_setup()
1572 self.assertEqual(len(grouping), 2)
1573 self.assertIn("id", grouping[0][0])
1574 self.assertIn("id", grouping[1][0])
1575 self.assertQuerySetEqual(
1576 qs.order_by("name"),
1577 [
1578 ("Artificial Intelligence: A Modern Approach", 2),
1579 (
1580 "Paradigms of Artificial Intelligence Programming: Case "
1581 "Studies in Common Lisp",
1582 1,
1583 ),
1584 ("Practical Django Projects", 1),
1585 ("Python Web Development with Django", 3),
1586 ("Sams Teach Yourself Django in 24 Hours", 1),
1587 (
1588 "The Definitive Guide to Django: Web Development Done "
1589 "Right",
1590 2,
1591 ),
1592 ],
1593 attrgetter("name", "num_authors"),
1594 )
1595
1596 def test_reverse_join_trimming(self):
1597 qs = Author.objects.annotate(Count("book_contact_set__contact"))
1598 self.assertIn(" JOIN ", str(qs.query))
1599
1600 def test_aggregation_with_generic_reverse_relation(self):
1601 """
1602 Regression test for #10870: Aggregates with joins ignore extra
1603 filters provided by setup_joins
1604
1605 tests aggregations with generic reverse relations
1606 """
1607 django_book = Book.objects.get(name="Practical Django Projects")
1608 ItemTag.objects.create(
1609 object_id=django_book.id,
1610 tag="intermediate",
1611 content_type=ContentType.objects.get_for_model(django_book),
1612 )
1613 ItemTag.objects.create(
1614 object_id=django_book.id,
1615 tag="django",
1616 content_type=ContentType.objects.get_for_model(django_book),
1617 )
1618 # Assign a tag to model with same PK as the book above. If the JOIN
1619 # used in aggregation doesn't have content type as part of the
1620 # condition the annotation will also count the 'hi mom' tag for b.
1621 wmpk = WithManualPK.objects.create(id=django_book.pk)
1622 ItemTag.objects.create(
1623 object_id=wmpk.id,
1624 tag="hi mom",
1625 content_type=ContentType.objects.get_for_model(wmpk),
1626 )
1627 ai_book = Book.objects.get(
1628 name__startswith="Paradigms of Artificial Intelligence"
1629 )
1630 ItemTag.objects.create(
1631 object_id=ai_book.id,
1632 tag="intermediate",
1633 content_type=ContentType.objects.get_for_model(ai_book),
1634 )
1635
1636 self.assertEqual(Book.objects.aggregate(Count("tags")), {"tags__count": 3})
1637 results = Book.objects.annotate(Count("tags")).order_by("-tags__count", "name")
1638 self.assertEqual(
1639 [(b.name, b.tags__count) for b in results],
1640 [
1641 ("Practical Django Projects", 2),
1642 (
1643 "Paradigms of Artificial Intelligence Programming: Case Studies in "
1644 "Common Lisp",
1645 1,
1646 ),
1647 ("Artificial Intelligence: A Modern Approach", 0),
1648 ("Python Web Development with Django", 0),
1649 ("Sams Teach Yourself Django in 24 Hours", 0),
1650 ("The Definitive Guide to Django: Web Development Done Right", 0),
1651 ],
1652 )
1653
1654 def test_negated_aggregation(self):
1655 expected_results = Author.objects.exclude(
1656 pk__in=Author.objects.annotate(book_cnt=Count("book")).filter(book_cnt=2)
1657 ).order_by("name")
1658 expected_results = [a.name for a in expected_results]
1659 qs = (
1660 Author.objects.annotate(book_cnt=Count("book"))
1661 .exclude(Q(book_cnt=2), Q(book_cnt=2))
1662 .order_by("name")
1663 )
1664 self.assertQuerySetEqual(qs, expected_results, lambda b: b.name)
1665 expected_results = Author.objects.exclude(
1666 pk__in=Author.objects.annotate(book_cnt=Count("book")).filter(book_cnt=2)
1667 ).order_by("name")
1668 expected_results = [a.name for a in expected_results]
1669 qs = (
1670 Author.objects.annotate(book_cnt=Count("book"))
1671 .exclude(Q(book_cnt=2) | Q(book_cnt=2))
1672 .order_by("name")
1673 )
1674 self.assertQuerySetEqual(qs, expected_results, lambda b: b.name)
1675
1676 def test_name_filters(self):
1677 qs = (
1678 Author.objects.annotate(Count("book"))
1679 .filter(Q(book__count__exact=2) | Q(name="Adrian Holovaty"))
1680 .order_by("name")
1681 )
1682 self.assertQuerySetEqual(
1683 qs, ["Adrian Holovaty", "Peter Norvig"], lambda b: b.name
1684 )
1685
1686 def test_name_expressions(self):
1687 # Aggregates are spotted correctly from F objects.
1688 # Note that Adrian's age is 34 in the fixtures, and he has one book
1689 # so both conditions match one author.
1690 qs = (
1691 Author.objects.annotate(Count("book"))
1692 .filter(Q(name="Peter Norvig") | Q(age=F("book__count") + 33))
1693 .order_by("name")
1694 )
1695 self.assertQuerySetEqual(
1696 qs, ["Adrian Holovaty", "Peter Norvig"], lambda b: b.name
1697 )
1698
1699 def test_filter_aggregates_or_connector(self):
1700 q1 = Q(price__gt=50)
1701 q2 = Q(authors__count__gt=1)
1702 query = Book.objects.annotate(Count("authors")).filter(q1 | q2).order_by("pk")
1703 self.assertQuerySetEqual(
1704 query,
1705 [self.b1.pk, self.b4.pk, self.b5.pk, self.b6.pk],
1706 attrgetter("pk"),
1707 )
1708
1709 def test_filter_aggregates_negated_and_connector(self):
1710 q1 = Q(price__gt=50)
1711 q2 = Q(authors__count__gt=1)
1712 query = (
1713 Book.objects.annotate(Count("authors")).filter(~(q1 & q2)).order_by("pk")
1714 )
1715 self.assertQuerySetEqual(
1716 query,
1717 [self.b1.pk, self.b2.pk, self.b3.pk, self.b4.pk, self.b6.pk],
1718 attrgetter("pk"),
1719 )
1720
1721 def test_filter_aggregates_xor_connector(self):
1722 q1 = Q(price__gt=50)
1723 q2 = Q(authors__count__gt=1)
1724 query = Book.objects.annotate(Count("authors")).filter(q1 ^ q2).order_by("pk")
1725 self.assertQuerySetEqual(
1726 query,
1727 [self.b1.pk, self.b4.pk, self.b6.pk],
1728 attrgetter("pk"),
1729 )
1730
1731 def test_filter_aggregates_negated_xor_connector(self):
1732 q1 = Q(price__gt=50)
1733 q2 = Q(authors__count__gt=1)
1734 query = (
1735 Book.objects.annotate(Count("authors")).filter(~(q1 ^ q2)).order_by("pk")
1736 )
1737 self.assertQuerySetEqual(
1738 query,
1739 [self.b2.pk, self.b3.pk, self.b5.pk],
1740 attrgetter("pk"),
1741 )
1742
1743 def test_ticket_11293_q_immutable(self):
1744 """
1745 Splitting a q object to parts for where/having doesn't alter
1746 the original q-object.
1747 """
1748 q1 = Q(isbn="")
1749 q2 = Q(authors__count__gt=1)
1750 query = Book.objects.annotate(Count("authors"))
1751 query.filter(q1 | q2)
1752 self.assertEqual(len(q2.children), 1)
1753
1754 def test_fobj_group_by(self):
1755 """
1756 An F() object referring to related column works correctly in group by.
1757 """
1758 qs = Book.objects.annotate(account=Count("authors")).filter(
1759 account=F("publisher__num_awards")
1760 )
1761 self.assertQuerySetEqual(
1762 qs, ["Sams Teach Yourself Django in 24 Hours"], lambda b: b.name
1763 )
1764
1765 def test_annotate_reserved_word(self):
1766 """
1767 Regression #18333 - Ensure annotated column name is properly quoted.
1768 """
1769 vals = Book.objects.annotate(select=Count("authors__id")).aggregate(
1770 Sum("select"), Avg("select")
1771 )
1772 self.assertEqual(
1773 vals,
1774 {
1775 "select__sum": 10,
1776 "select__avg": Approximate(1.666, places=2),
1777 },
1778 )
1779
1780 def test_annotate_on_relation(self):
1781 book = Book.objects.annotate(
1782 avg_price=Avg("price"), publisher_name=F("publisher__name")
1783 ).get(pk=self.b1.pk)
1784 self.assertEqual(book.avg_price, 30.00)
1785 self.assertEqual(book.publisher_name, "Apress")
1786
1787 def test_aggregate_on_relation(self):
1788 # A query with an existing annotation aggregation on a relation should
1789 # succeed.
1790 qs = Book.objects.annotate(avg_price=Avg("price")).aggregate(
1791 publisher_awards=Sum("publisher__num_awards")
1792 )
1793 self.assertEqual(qs["publisher_awards"], 30)
1794
1795 def test_annotate_distinct_aggregate(self):
1796 # There are three books with rating of 4.0 and two of the books have
1797 # the same price. Hence, the distinct removes one rating of 4.0
1798 # from the results.
1799 vals1 = (
1800 Book.objects.values("rating", "price")
1801 .distinct()
1802 .aggregate(result=Sum("rating"))
1803 )
1804 vals2 = Book.objects.aggregate(result=Sum("rating") - Value(4.0))
1805 self.assertEqual(vals1, vals2)
1806
1807 def test_annotate_values_list_flat(self):
1808 """Find ages that are shared by at least two authors."""
1809 qs = (
1810 Author.objects.values_list("age", flat=True)
1811 .annotate(age_count=Count("age"))
1812 .filter(age_count__gt=1)
1813 )
1814 self.assertSequenceEqual(qs, [29])
1815
1816 def test_allow_distinct(self):
1817 class MyAggregate(Aggregate):
1818 pass
1819
1820 with self.assertRaisesMessage(TypeError, "MyAggregate does not allow distinct"):
1821 MyAggregate("foo", distinct=True)
1822
1823 class DistinctAggregate(Aggregate):
1824 allow_distinct = True
1825
1826 DistinctAggregate("foo", distinct=True)
1827
1828 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1829 def test_having_subquery_select(self):
1830 authors = Author.objects.filter(pk=self.a1.pk)
1831 books = Book.objects.annotate(Count("authors")).filter(
1832 Q(authors__in=authors) | Q(authors__count__gt=2)
1833 )
1834 self.assertEqual(set(books), {self.b1, self.b4})
1835
1836 def test_aggregate_and_annotate_duplicate_columns(self):
1837 books = (
1838 Book.objects.values("isbn")
1839 .annotate(
1840 name=F("publisher__name"),
1841 num_authors=Count("authors"),
1842 )
1843 .order_by("isbn")
1844 )
1845 self.assertSequenceEqual(
1846 books,
1847 [
1848 {"isbn": "013235613", "name": "Prentice Hall", "num_authors": 3},
1849 {"isbn": "013790395", "name": "Prentice Hall", "num_authors": 2},
1850 {"isbn": "067232959", "name": "Sams", "num_authors": 1},
1851 {"isbn": "155860191", "name": "Morgan Kaufmann", "num_authors": 1},
1852 {"isbn": "159059725", "name": "Apress", "num_authors": 2},
1853 {"isbn": "159059996", "name": "Apress", "num_authors": 1},
1854 ],
1855 )
1856
1857 def test_aggregate_and_annotate_duplicate_columns_proxy(self):
1858 author = AuthorProxy.objects.latest("pk")
1859 recipe = RecipeProxy.objects.create(name="Dahl", author=author)
1860 recipe.tasters.add(author)
1861 recipes = RecipeProxy.objects.values("pk").annotate(
1862 name=F("author__name"),
1863 num_tasters=Count("tasters"),
1864 )
1865 self.assertSequenceEqual(
1866 recipes,
1867 [{"pk": recipe.pk, "name": "Stuart Russell", "num_tasters": 1}],
1868 )
1869
1870 def test_aggregate_and_annotate_duplicate_columns_unmanaged(self):
1871 author = AuthorProxy.objects.latest("pk")
1872 recipe = RecipeProxy.objects.create(name="Dahl", author=author)
1873 recipe.tasters.add(author)
1874 recipes = RecipeUnmanaged.objects.values("pk").annotate(
1875 name=F("author__age"),
1876 num_tasters=Count("tasters"),
1877 )
1878 self.assertSequenceEqual(
1879 recipes,
1880 [{"pk": recipe.pk, "name": 46, "num_tasters": 1}],
1881 )
1882
1883 def test_aggregate_group_by_unseen_columns_unmanaged(self):
1884 author = AuthorProxy.objects.latest("pk")
1885 shadow_author = AuthorProxy.objects.create(name=author.name, age=author.age - 2)
1886 recipe = RecipeProxy.objects.create(name="Dahl", author=author)
1887 shadow_recipe = RecipeProxy.objects.create(
1888 name="Shadow Dahl",
1889 author=shadow_author,
1890 )
1891 recipe.tasters.add(shadow_author)
1892 shadow_recipe.tasters.add(author)
1893 # This selects how many tasters each author had according to a
1894 # calculated field "name". The table has a column "name" that Django is
1895 # unaware of, and is equal for the two authors. The grouping column
1896 # cannot be referenced by its name ("name"), as it'd return one result
1897 # which is incorrect.
1898 author_recipes = (
1899 AuthorUnmanaged.objects.annotate(
1900 name=Concat(
1901 Value("Writer at "),
1902 Cast(F("age"), output_field=CharField()),
1903 )
1904 )
1905 .values("name") # Field used for grouping.
1906 .annotate(num_recipes=Count("recipeunmanaged"))
1907 .filter(num_recipes__gt=0)
1908 .values("num_recipes") # Drop grouping column.
1909 )
1910 self.assertSequenceEqual(
1911 author_recipes,
1912 [{"num_recipes": 1}, {"num_recipes": 1}],
1913 )
1914
1915
1916 class JoinPromotionTests(TestCase):
1917 def test_ticket_21150(self):
1918 b = Bravo.objects.create()
1919 c = Charlie.objects.create(bravo=b)
1920 qs = Charlie.objects.select_related("alfa").annotate(Count("bravo__charlie"))
1921 self.assertSequenceEqual(qs, [c])
1922 self.assertIs(qs[0].alfa, None)
1923 a = Alfa.objects.create()
1924 c.alfa = a
1925 c.save()
1926 # Force re-evaluation
1927 qs = qs.all()
1928 self.assertSequenceEqual(qs, [c])
1929 self.assertEqual(qs[0].alfa, a)
1930
1931 def test_existing_join_not_promoted(self):
1932 # No promotion for existing joins
1933 qs = Charlie.objects.filter(alfa__name__isnull=False).annotate(
1934 Count("alfa__name")
1935 )
1936 self.assertIn(" INNER JOIN ", str(qs.query))
1937 # Also, the existing join is unpromoted when doing filtering for already
1938 # promoted join.
1939 qs = Charlie.objects.annotate(Count("alfa__name")).filter(
1940 alfa__name__isnull=False
1941 )
1942 self.assertIn(" INNER JOIN ", str(qs.query))
1943 # But, as the join is nullable first use by annotate will be LOUTER
1944 qs = Charlie.objects.annotate(Count("alfa__name"))
1945 self.assertIn(" LEFT OUTER JOIN ", str(qs.query))
1946
1947 def test_non_nullable_fk_not_promoted(self):
1948 qs = Book.objects.annotate(Count("contact__name"))
1949 self.assertIn(" INNER JOIN ", str(qs.query))
1950
1951
1952 class SelfReferentialFKTests(TestCase):
1953 def test_ticket_24748(self):
1954 t1 = SelfRefFK.objects.create(name="t1")
1955 SelfRefFK.objects.create(name="t2", parent=t1)
1956 SelfRefFK.objects.create(name="t3", parent=t1)
1957 self.assertQuerySetEqual(
1958 SelfRefFK.objects.annotate(num_children=Count("children")).order_by("name"),
1959 [("t1", 2), ("t2", 0), ("t3", 0)],
1960 lambda x: (x.name, x.num_children),
1961 )