Debian Codegraph @

tests/annotations/tests.py

raw
1 import datetime
2 from decimal import Decimal
3 from itertools import chain
4 from unittest import skipUnless
5
6 from django.core.exceptions import FieldDoesNotExist, FieldError
7 from django.db import connection
8 from django.db.models import (
9 BooleanField,
10 Case,
11 CharField,
12 Count,
13 DateTimeField,
14 DecimalField,
15 Exists,
16 ExpressionWrapper,
17 F,
18 FilteredRelation,
19 FloatField,
20 Func,
21 IntegerField,
22 JSONField,
23 Max,
24 OuterRef,
25 Q,
26 Subquery,
27 Sum,
28 Value,
29 When,
30 )
31 from django.db.models.expressions import RawSQL
32 from django.db.models.functions import (
33 Cast,
34 Coalesce,
35 ExtractYear,
36 Floor,
37 Length,
38 Lower,
39 Trim,
40 )
41 from django.db.models.sql.query import get_field_names_from_opts
42 from django.test import TestCase, skipUnlessDBFeature
43 from django.test.utils import register_lookup
44
45 from .models import (
46 Author,
47 Book,
48 Company,
49 DepartmentStore,
50 Employee,
51 JsonModel,
52 Publisher,
53 Store,
54 Ticket,
55 )
56
57
58 class NonAggregateAnnotationTestCase(TestCase):
59 @classmethod
60 def setUpTestData(cls):
61 cls.a1 = Author.objects.create(name="Adrian Holovaty", age=34)
62 cls.a2 = Author.objects.create(name="Jacob Kaplan-Moss", age=35)
63 cls.a3 = Author.objects.create(name="Brad Dayley", age=45)
64 cls.a4 = Author.objects.create(name="James Bennett", age=29)
65 cls.a5 = Author.objects.create(name="Jeffrey Forcier", age=37)
66 cls.a6 = Author.objects.create(name="Paul Bissex", age=29)
67 cls.a7 = Author.objects.create(name="Wesley J. Chun", age=25)
68 cls.a8 = Author.objects.create(name="Peter Norvig", age=57)
69 cls.a9 = Author.objects.create(name="Stuart Russell", age=46)
70 cls.a1.friends.add(cls.a2, cls.a4)
71 cls.a2.friends.add(cls.a1, cls.a7)
72 cls.a4.friends.add(cls.a1)
73 cls.a5.friends.add(cls.a6, cls.a7)
74 cls.a6.friends.add(cls.a5, cls.a7)
75 cls.a7.friends.add(cls.a2, cls.a5, cls.a6)
76 cls.a8.friends.add(cls.a9)
77 cls.a9.friends.add(cls.a8)
78
79 cls.p1 = Publisher.objects.create(name="Apress", num_awards=3)
80 cls.p2 = Publisher.objects.create(name="Sams", num_awards=1)
81 cls.p3 = Publisher.objects.create(name="Prentice Hall", num_awards=7)
82 cls.p4 = Publisher.objects.create(name="Morgan Kaufmann", num_awards=9)
83 cls.p5 = Publisher.objects.create(name="Jonno's House of Books", num_awards=0)
84
85 cls.b1 = Book.objects.create(
86 isbn="159059725",
87 name="The Definitive Guide to Django: Web Development Done Right",
88 pages=447,
89 rating=4.5,
90 price=Decimal("30.00"),
91 contact=cls.a1,
92 publisher=cls.p1,
93 pubdate=datetime.date(2007, 12, 6),
94 )
95 cls.b2 = Book.objects.create(
96 isbn="067232959",
97 name="Sams Teach Yourself Django in 24 Hours",
98 pages=528,
99 rating=3.0,
100 price=Decimal("23.09"),
101 contact=cls.a3,
102 publisher=cls.p2,
103 pubdate=datetime.date(2008, 3, 3),
104 )
105 cls.b3 = Book.objects.create(
106 isbn="159059996",
107 name="Practical Django Projects",
108 pages=300,
109 rating=4.0,
110 price=Decimal("29.69"),
111 contact=cls.a4,
112 publisher=cls.p1,
113 pubdate=datetime.date(2008, 6, 23),
114 )
115 cls.b4 = Book.objects.create(
116 isbn="013235613",
117 name="Python Web Development with Django",
118 pages=350,
119 rating=4.0,
120 price=Decimal("29.69"),
121 contact=cls.a5,
122 publisher=cls.p3,
123 pubdate=datetime.date(2008, 11, 3),
124 )
125 cls.b5 = Book.objects.create(
126 isbn="013790395",
127 name="Artificial Intelligence: A Modern Approach",
128 pages=1132,
129 rating=4.0,
130 price=Decimal("82.80"),
131 contact=cls.a8,
132 publisher=cls.p3,
133 pubdate=datetime.date(1995, 1, 15),
134 )
135 cls.b6 = Book.objects.create(
136 isbn="155860191",
137 name=(
138 "Paradigms of Artificial Intelligence Programming: Case Studies in "
139 "Common Lisp"
140 ),
141 pages=946,
142 rating=5.0,
143 price=Decimal("75.00"),
144 contact=cls.a8,
145 publisher=cls.p4,
146 pubdate=datetime.date(1991, 10, 15),
147 )
148 cls.b1.authors.add(cls.a1, cls.a2)
149 cls.b2.authors.add(cls.a3)
150 cls.b3.authors.add(cls.a4)
151 cls.b4.authors.add(cls.a5, cls.a6, cls.a7)
152 cls.b5.authors.add(cls.a8, cls.a9)
153 cls.b6.authors.add(cls.a8)
154
155 cls.s1 = Store.objects.create(
156 name="Amazon.com",
157 original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
158 friday_night_closing=datetime.time(23, 59, 59),
159 )
160 cls.s2 = Store.objects.create(
161 name="Books.com",
162 original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
163 friday_night_closing=datetime.time(23, 59, 59),
164 )
165 cls.s3 = Store.objects.create(
166 name="Mamma and Pappa's Books",
167 original_opening=datetime.datetime(1945, 4, 25, 16, 24, 14),
168 friday_night_closing=datetime.time(21, 30),
169 )
170 cls.s1.books.add(cls.b1, cls.b2, cls.b3, cls.b4, cls.b5, cls.b6)
171 cls.s2.books.add(cls.b1, cls.b3, cls.b5, cls.b6)
172 cls.s3.books.add(cls.b3, cls.b4, cls.b6)
173
174 def test_basic_annotation(self):
175 books = Book.objects.annotate(is_book=Value(1))
176 for book in books:
177 self.assertEqual(book.is_book, 1)
178
179 def test_basic_f_annotation(self):
180 books = Book.objects.annotate(another_rating=F("rating"))
181 for book in books:
182 self.assertEqual(book.another_rating, book.rating)
183
184 def test_joined_annotation(self):
185 books = Book.objects.select_related("publisher").annotate(
186 num_awards=F("publisher__num_awards")
187 )
188 for book in books:
189 self.assertEqual(book.num_awards, book.publisher.num_awards)
190
191 def test_joined_transformed_annotation(self):
192 Employee.objects.bulk_create(
193 [
194 Employee(
195 first_name="John",
196 last_name="Doe",
197 age=18,
198 store=self.s1,
199 salary=15000,
200 ),
201 Employee(
202 first_name="Jane",
203 last_name="Jones",
204 age=30,
205 store=self.s2,
206 salary=30000,
207 ),
208 Employee(
209 first_name="Jo",
210 last_name="Smith",
211 age=55,
212 store=self.s3,
213 salary=50000,
214 ),
215 ]
216 )
217 employees = Employee.objects.annotate(
218 store_opened_year=F("store__original_opening__year"),
219 )
220 for employee in employees:
221 self.assertEqual(
222 employee.store_opened_year,
223 employee.store.original_opening.year,
224 )
225
226 def test_custom_transform_annotation(self):
227 with register_lookup(DecimalField, Floor):
228 books = Book.objects.annotate(floor_price=F("price__floor"))
229
230 self.assertCountEqual(
231 books.values_list("pk", "floor_price"),
232 [
233 (self.b1.pk, 30),
234 (self.b2.pk, 23),
235 (self.b3.pk, 29),
236 (self.b4.pk, 29),
237 (self.b5.pk, 82),
238 (self.b6.pk, 75),
239 ],
240 )
241
242 def test_chaining_transforms(self):
243 Company.objects.create(name=" Django Software Foundation ")
244 Company.objects.create(name="Yahoo")
245 with register_lookup(CharField, Trim), register_lookup(CharField, Length):
246 for expr in [Length("name__trim"), F("name__trim__length")]:
247 with self.subTest(expr=expr):
248 self.assertCountEqual(
249 Company.objects.annotate(length=expr).values("name", "length"),
250 [
251 {"name": " Django Software Foundation ", "length": 26},
252 {"name": "Yahoo", "length": 5},
253 ],
254 )
255
256 def test_mixed_type_annotation_date_interval(self):
257 active = datetime.datetime(2015, 3, 20, 14, 0, 0)
258 duration = datetime.timedelta(hours=1)
259 expires = datetime.datetime(2015, 3, 20, 14, 0, 0) + duration
260 Ticket.objects.create(active_at=active, duration=duration)
261 t = Ticket.objects.annotate(
262 expires=ExpressionWrapper(
263 F("active_at") + F("duration"), output_field=DateTimeField()
264 )
265 ).first()
266 self.assertEqual(t.expires, expires)
267
268 def test_mixed_type_annotation_numbers(self):
269 test = self.b1
270 b = Book.objects.annotate(
271 combined=ExpressionWrapper(
272 F("pages") + F("rating"), output_field=IntegerField()
273 )
274 ).get(isbn=test.isbn)
275 combined = int(test.pages + test.rating)
276 self.assertEqual(b.combined, combined)
277
278 def test_empty_expression_annotation(self):
279 books = Book.objects.annotate(
280 selected=ExpressionWrapper(Q(pk__in=[]), output_field=BooleanField())
281 )
282 self.assertEqual(len(books), Book.objects.count())
283 self.assertTrue(all(not book.selected for book in books))
284
285 books = Book.objects.annotate(
286 selected=ExpressionWrapper(
287 Q(pk__in=Book.objects.none()), output_field=BooleanField()
288 )
289 )
290 self.assertEqual(len(books), Book.objects.count())
291 self.assertTrue(all(not book.selected for book in books))
292
293 def test_full_expression_annotation(self):
294 books = Book.objects.annotate(
295 selected=ExpressionWrapper(~Q(pk__in=[]), output_field=BooleanField()),
296 )
297 self.assertEqual(len(books), Book.objects.count())
298 self.assertTrue(all(book.selected for book in books))
299
300 def test_full_expression_wrapped_annotation(self):
301 books = Book.objects.annotate(
302 selected=Coalesce(~Q(pk__in=[]), True),
303 )
304 self.assertEqual(len(books), Book.objects.count())
305 self.assertTrue(all(book.selected for book in books))
306
307 def test_full_expression_annotation_with_aggregation(self):
308 qs = Book.objects.filter(isbn="159059725").annotate(
309 selected=ExpressionWrapper(~Q(pk__in=[]), output_field=BooleanField()),
310 rating_count=Count("rating"),
311 )
312 self.assertEqual([book.rating_count for book in qs], [1])
313
314 def test_aggregate_over_full_expression_annotation(self):
315 qs = Book.objects.annotate(
316 selected=ExpressionWrapper(~Q(pk__in=[]), output_field=BooleanField()),
317 ).aggregate(selected__sum=Sum(Cast("selected", IntegerField())))
318 self.assertEqual(qs["selected__sum"], Book.objects.count())
319
320 def test_empty_queryset_annotation(self):
321 qs = Author.objects.annotate(empty=Subquery(Author.objects.values("id").none()))
322 self.assertIsNone(qs.first().empty)
323
324 def test_annotate_with_aggregation(self):
325 books = Book.objects.annotate(is_book=Value(1), rating_count=Count("rating"))
326 for book in books:
327 self.assertEqual(book.is_book, 1)
328 self.assertEqual(book.rating_count, 1)
329
330 def test_combined_expression_annotation_with_aggregation(self):
331 book = Book.objects.annotate(
332 combined=ExpressionWrapper(
333 Value(3) * Value(4), output_field=IntegerField()
334 ),
335 rating_count=Count("rating"),
336 ).first()
337 self.assertEqual(book.combined, 12)
338 self.assertEqual(book.rating_count, 1)
339
340 def test_combined_f_expression_annotation_with_aggregation(self):
341 book = (
342 Book.objects.filter(isbn="159059725")
343 .annotate(
344 combined=ExpressionWrapper(
345 F("price") * F("pages"), output_field=FloatField()
346 ),
347 rating_count=Count("rating"),
348 )
349 .first()
350 )
351 self.assertEqual(book.combined, 13410.0)
352 self.assertEqual(book.rating_count, 1)
353
354 @skipUnlessDBFeature("supports_boolean_expr_in_select_clause")
355 def test_q_expression_annotation_with_aggregation(self):
356 book = (
357 Book.objects.filter(isbn="159059725")
358 .annotate(
359 isnull_pubdate=ExpressionWrapper(
360 Q(pubdate__isnull=True),
361 output_field=BooleanField(),
362 ),
363 rating_count=Count("rating"),
364 )
365 .first()
366 )
367 self.assertIs(book.isnull_pubdate, False)
368 self.assertEqual(book.rating_count, 1)
369
370 @skipUnlessDBFeature("supports_boolean_expr_in_select_clause")
371 def test_grouping_by_q_expression_annotation(self):
372 authors = (
373 Author.objects.annotate(
374 under_40=ExpressionWrapper(Q(age__lt=40), output_field=BooleanField()),
375 )
376 .values("under_40")
377 .annotate(
378 count_id=Count("id"),
379 )
380 .values("under_40", "count_id")
381 )
382 self.assertCountEqual(
383 authors,
384 [
385 {"under_40": False, "count_id": 3},
386 {"under_40": True, "count_id": 6},
387 ],
388 )
389
390 def test_aggregate_over_annotation(self):
391 agg = Author.objects.annotate(other_age=F("age")).aggregate(
392 otherage_sum=Sum("other_age")
393 )
394 other_agg = Author.objects.aggregate(age_sum=Sum("age"))
395 self.assertEqual(agg["otherage_sum"], other_agg["age_sum"])
396
397 @skipUnlessDBFeature("can_distinct_on_fields")
398 def test_distinct_on_with_annotation(self):
399 store = Store.objects.create(
400 name="test store",
401 original_opening=datetime.datetime.now(),
402 friday_night_closing=datetime.time(21, 00, 00),
403 )
404 names = [
405 "Theodore Roosevelt",
406 "Eleanor Roosevelt",
407 "Franklin Roosevelt",
408 "Ned Stark",
409 "Catelyn Stark",
410 ]
411 for name in names:
412 Employee.objects.create(
413 store=store,
414 first_name=name.split()[0],
415 last_name=name.split()[1],
416 age=30,
417 salary=2000,
418 )
419
420 people = Employee.objects.annotate(
421 name_lower=Lower("last_name"),
422 ).distinct("name_lower")
423
424 self.assertEqual({p.last_name for p in people}, {"Stark", "Roosevelt"})
425 self.assertEqual(len(people), 2)
426
427 people2 = Employee.objects.annotate(
428 test_alias=F("store__name"),
429 ).distinct("test_alias")
430 self.assertEqual(len(people2), 1)
431
432 lengths = (
433 Employee.objects.annotate(
434 name_len=Length("first_name"),
435 )
436 .distinct("name_len")
437 .values_list("name_len", flat=True)
438 )
439 self.assertCountEqual(lengths, [3, 7, 8])
440
441 def test_filter_annotation(self):
442 books = Book.objects.annotate(is_book=Value(1)).filter(is_book=1)
443 for book in books:
444 self.assertEqual(book.is_book, 1)
445
446 def test_filter_annotation_with_f(self):
447 books = Book.objects.annotate(other_rating=F("rating")).filter(other_rating=3.5)
448 for book in books:
449 self.assertEqual(book.other_rating, 3.5)
450
451 def test_filter_annotation_with_double_f(self):
452 books = Book.objects.annotate(other_rating=F("rating")).filter(
453 other_rating=F("rating")
454 )
455 for book in books:
456 self.assertEqual(book.other_rating, book.rating)
457
458 def test_filter_agg_with_double_f(self):
459 books = Book.objects.annotate(sum_rating=Sum("rating")).filter(
460 sum_rating=F("sum_rating")
461 )
462 for book in books:
463 self.assertEqual(book.sum_rating, book.rating)
464
465 def test_filter_wrong_annotation(self):
466 with self.assertRaisesMessage(
467 FieldError, "Cannot resolve keyword 'nope' into field."
468 ):
469 list(
470 Book.objects.annotate(sum_rating=Sum("rating")).filter(
471 sum_rating=F("nope")
472 )
473 )
474
475 def test_values_wrong_annotation(self):
476 expected_message = (
477 "Cannot resolve keyword 'annotation_typo' into field. Choices are: %s"
478 )
479 article_fields = ", ".join(
480 ["annotation"] + sorted(get_field_names_from_opts(Book._meta))
481 )
482 with self.assertRaisesMessage(FieldError, expected_message % article_fields):
483 Book.objects.annotate(annotation=Value(1)).values_list("annotation_typo")
484
485 def test_decimal_annotation(self):
486 salary = Decimal(10) ** -Employee._meta.get_field("salary").decimal_places
487 Employee.objects.create(
488 first_name="Max",
489 last_name="Paine",
490 store=Store.objects.first(),
491 age=23,
492 salary=salary,
493 )
494 self.assertEqual(
495 Employee.objects.annotate(new_salary=F("salary") / 10).get().new_salary,
496 salary / 10,
497 )
498
499 def test_filter_decimal_annotation(self):
500 qs = (
501 Book.objects.annotate(new_price=F("price") + 1)
502 .filter(new_price=Decimal(31))
503 .values_list("new_price")
504 )
505 self.assertEqual(qs.get(), (Decimal(31),))
506
507 def test_combined_annotation_commutative(self):
508 book1 = Book.objects.annotate(adjusted_rating=F("rating") + 2).get(
509 pk=self.b1.pk
510 )
511 book2 = Book.objects.annotate(adjusted_rating=2 + F("rating")).get(
512 pk=self.b1.pk
513 )
514 self.assertEqual(book1.adjusted_rating, book2.adjusted_rating)
515 book1 = Book.objects.annotate(adjusted_rating=F("rating") + None).get(
516 pk=self.b1.pk
517 )
518 book2 = Book.objects.annotate(adjusted_rating=None + F("rating")).get(
519 pk=self.b1.pk
520 )
521 self.assertIs(book1.adjusted_rating, None)
522 self.assertEqual(book1.adjusted_rating, book2.adjusted_rating)
523
524 def test_update_with_annotation(self):
525 book_preupdate = Book.objects.get(pk=self.b2.pk)
526 Book.objects.annotate(other_rating=F("rating") - 1).update(
527 rating=F("other_rating")
528 )
529 book_postupdate = Book.objects.get(pk=self.b2.pk)
530 self.assertEqual(book_preupdate.rating - 1, book_postupdate.rating)
531
532 def test_annotation_with_m2m(self):
533 books = (
534 Book.objects.annotate(author_age=F("authors__age"))
535 .filter(pk=self.b1.pk)
536 .order_by("author_age")
537 )
538 self.assertEqual(books[0].author_age, 34)
539 self.assertEqual(books[1].author_age, 35)
540
541 def test_annotation_reverse_m2m(self):
542 books = (
543 Book.objects.annotate(
544 store_name=F("store__name"),
545 )
546 .filter(
547 name="Practical Django Projects",
548 )
549 .order_by("store_name")
550 )
551
552 self.assertQuerySetEqual(
553 books,
554 ["Amazon.com", "Books.com", "Mamma and Pappa's Books"],
555 lambda b: b.store_name,
556 )
557
558 def test_values_annotation(self):
559 """
560 Annotations can reference fields in a values clause,
561 and contribute to an existing values clause.
562 """
563 # annotate references a field in values()
564 qs = Book.objects.values("rating").annotate(other_rating=F("rating") - 1)
565 book = qs.get(pk=self.b1.pk)
566 self.assertEqual(book["rating"] - 1, book["other_rating"])
567
568 # filter refs the annotated value
569 book = qs.get(other_rating=4)
570 self.assertEqual(book["other_rating"], 4)
571
572 # can annotate an existing values with a new field
573 book = qs.annotate(other_isbn=F("isbn")).get(other_rating=4)
574 self.assertEqual(book["other_rating"], 4)
575 self.assertEqual(book["other_isbn"], "155860191")
576
577 def test_values_fields_annotations_order(self):
578 qs = Book.objects.annotate(other_rating=F("rating") - 1).values(
579 "other_rating", "rating"
580 )
581 book = qs.get(pk=self.b1.pk)
582 self.assertEqual(
583 list(book.items()),
584 [("other_rating", self.b1.rating - 1), ("rating", self.b1.rating)],
585 )
586
587 def test_values_with_pk_annotation(self):
588 # annotate references a field in values() with pk
589 publishers = Publisher.objects.values("id", "book__rating").annotate(
590 total=Sum("book__rating")
591 )
592 for publisher in publishers.filter(pk=self.p1.pk):
593 self.assertEqual(publisher["book__rating"], publisher["total"])
594
595 def test_defer_annotation(self):
596 """
597 Deferred attributes can be referenced by an annotation,
598 but they are not themselves deferred, and cannot be deferred.
599 """
600 qs = Book.objects.defer("rating").annotate(other_rating=F("rating") - 1)
601
602 with self.assertNumQueries(2):
603 book = qs.get(other_rating=4)
604 self.assertEqual(book.rating, 5)
605 self.assertEqual(book.other_rating, 4)
606
607 with self.assertRaisesMessage(
608 FieldDoesNotExist, "Book has no field named 'other_rating'"
609 ):
610 book = qs.defer("other_rating").get(other_rating=4)
611
612 def test_mti_annotations(self):
613 """
614 Fields on an inherited model can be referenced by an
615 annotated field.
616 """
617 d = DepartmentStore.objects.create(
618 name="Angus & Robinson",
619 original_opening=datetime.date(2014, 3, 8),
620 friday_night_closing=datetime.time(21, 00, 00),
621 chain="Westfield",
622 )
623
624 books = Book.objects.filter(rating__gt=4)
625 for b in books:
626 d.books.add(b)
627
628 qs = (
629 DepartmentStore.objects.annotate(
630 other_name=F("name"),
631 other_chain=F("chain"),
632 is_open=Value(True, BooleanField()),
633 book_isbn=F("books__isbn"),
634 )
635 .order_by("book_isbn")
636 .filter(chain="Westfield")
637 )
638
639 self.assertQuerySetEqual(
640 qs,
641 [
642 ("Angus & Robinson", "Westfield", True, "155860191"),
643 ("Angus & Robinson", "Westfield", True, "159059725"),
644 ],
645 lambda d: (d.other_name, d.other_chain, d.is_open, d.book_isbn),
646 )
647
648 def test_null_annotation(self):
649 """
650 Annotating None onto a model round-trips
651 """
652 book = Book.objects.annotate(
653 no_value=Value(None, output_field=IntegerField())
654 ).first()
655 self.assertIsNone(book.no_value)
656
657 def test_order_by_annotation(self):
658 authors = Author.objects.annotate(other_age=F("age")).order_by("other_age")
659 self.assertQuerySetEqual(
660 authors,
661 [
662 25,
663 29,
664 29,
665 34,
666 35,
667 37,
668 45,
669 46,
670 57,
671 ],
672 lambda a: a.other_age,
673 )
674
675 def test_order_by_aggregate(self):
676 authors = (
677 Author.objects.values("age")
678 .annotate(age_count=Count("age"))
679 .order_by("age_count", "age")
680 )
681 self.assertQuerySetEqual(
682 authors,
683 [
684 (25, 1),
685 (34, 1),
686 (35, 1),
687 (37, 1),
688 (45, 1),
689 (46, 1),
690 (57, 1),
691 (29, 2),
692 ],
693 lambda a: (a["age"], a["age_count"]),
694 )
695
696 def test_raw_sql_with_inherited_field(self):
697 DepartmentStore.objects.create(
698 name="Angus & Robinson",
699 original_opening=datetime.date(2014, 3, 8),
700 friday_night_closing=datetime.time(21),
701 chain="Westfield",
702 area=123,
703 )
704 tests = (
705 ("name", "Angus & Robinson"),
706 ("surface", 123),
707 ("case when name='Angus & Robinson' then chain else name end", "Westfield"),
708 )
709 for sql, expected_result in tests:
710 with self.subTest(sql=sql):
711 self.assertSequenceEqual(
712 DepartmentStore.objects.annotate(
713 annotation=RawSQL(sql, ()),
714 ).values_list("annotation", flat=True),
715 [expected_result],
716 )
717
718 def test_annotate_exists(self):
719 authors = Author.objects.annotate(c=Count("id")).filter(c__gt=1)
720 self.assertFalse(authors.exists())
721
722 def test_column_field_ordering(self):
723 """
724 Columns are aligned in the correct order for resolve_columns. This test
725 will fail on MySQL if column ordering is out. Column fields should be
726 aligned as:
727 1. extra_select
728 2. model_fields
729 3. annotation_fields
730 4. model_related_fields
731 """
732 store = Store.objects.first()
733 Employee.objects.create(
734 id=1,
735 first_name="Max",
736 manager=True,
737 last_name="Paine",
738 store=store,
739 age=23,
740 salary=Decimal(50000.00),
741 )
742 Employee.objects.create(
743 id=2,
744 first_name="Buffy",
745 manager=False,
746 last_name="Summers",
747 store=store,
748 age=18,
749 salary=Decimal(40000.00),
750 )
751
752 qs = (
753 Employee.objects.extra(select={"random_value": "42"})
754 .select_related("store")
755 .annotate(
756 annotated_value=Value(17),
757 )
758 )
759
760 rows = [
761 (1, "Max", True, 42, "Paine", 23, Decimal(50000.00), store.name, 17),
762 (2, "Buffy", False, 42, "Summers", 18, Decimal(40000.00), store.name, 17),
763 ]
764
765 self.assertQuerySetEqual(
766 qs.order_by("id"),
767 rows,
768 lambda e: (
769 e.id,
770 e.first_name,
771 e.manager,
772 e.random_value,
773 e.last_name,
774 e.age,
775 e.salary,
776 e.store.name,
777 e.annotated_value,
778 ),
779 )
780
781 def test_column_field_ordering_with_deferred(self):
782 store = Store.objects.first()
783 Employee.objects.create(
784 id=1,
785 first_name="Max",
786 manager=True,
787 last_name="Paine",
788 store=store,
789 age=23,
790 salary=Decimal(50000.00),
791 )
792 Employee.objects.create(
793 id=2,
794 first_name="Buffy",
795 manager=False,
796 last_name="Summers",
797 store=store,
798 age=18,
799 salary=Decimal(40000.00),
800 )
801
802 qs = (
803 Employee.objects.extra(select={"random_value": "42"})
804 .select_related("store")
805 .annotate(
806 annotated_value=Value(17),
807 )
808 )
809
810 rows = [
811 (1, "Max", True, 42, "Paine", 23, Decimal(50000.00), store.name, 17),
812 (2, "Buffy", False, 42, "Summers", 18, Decimal(40000.00), store.name, 17),
813 ]
814
815 # and we respect deferred columns!
816 self.assertQuerySetEqual(
817 qs.defer("age").order_by("id"),
818 rows,
819 lambda e: (
820 e.id,
821 e.first_name,
822 e.manager,
823 e.random_value,
824 e.last_name,
825 e.age,
826 e.salary,
827 e.store.name,
828 e.annotated_value,
829 ),
830 )
831
832 def test_custom_functions(self):
833 Company(
834 name="Apple",
835 motto=None,
836 ticker_name="APPL",
837 description="Beautiful Devices",
838 ).save()
839 Company(
840 name="Django Software Foundation",
841 motto=None,
842 ticker_name=None,
843 description=None,
844 ).save()
845 Company(
846 name="Google",
847 motto="Do No Evil",
848 ticker_name="GOOG",
849 description="Internet Company",
850 ).save()
851 Company(
852 name="Yahoo", motto=None, ticker_name=None, description="Internet Company"
853 ).save()
854
855 qs = Company.objects.annotate(
856 tagline=Func(
857 F("motto"),
858 F("ticker_name"),
859 F("description"),
860 Value("No Tag"),
861 function="COALESCE",
862 )
863 ).order_by("name")
864
865 self.assertQuerySetEqual(
866 qs,
867 [
868 ("Apple", "APPL"),
869 ("Django Software Foundation", "No Tag"),
870 ("Google", "Do No Evil"),
871 ("Yahoo", "Internet Company"),
872 ],
873 lambda c: (c.name, c.tagline),
874 )
875
876 def test_custom_functions_can_ref_other_functions(self):
877 Company(
878 name="Apple",
879 motto=None,
880 ticker_name="APPL",
881 description="Beautiful Devices",
882 ).save()
883 Company(
884 name="Django Software Foundation",
885 motto=None,
886 ticker_name=None,
887 description=None,
888 ).save()
889 Company(
890 name="Google",
891 motto="Do No Evil",
892 ticker_name="GOOG",
893 description="Internet Company",
894 ).save()
895 Company(
896 name="Yahoo", motto=None, ticker_name=None, description="Internet Company"
897 ).save()
898
899 class Lower(Func):
900 function = "LOWER"
901
902 qs = (
903 Company.objects.annotate(
904 tagline=Func(
905 F("motto"),
906 F("ticker_name"),
907 F("description"),
908 Value("No Tag"),
909 function="COALESCE",
910 )
911 )
912 .annotate(
913 tagline_lower=Lower(F("tagline")),
914 )
915 .order_by("name")
916 )
917
918 # LOWER function supported by:
919 # oracle, postgres, mysql, sqlite, sqlserver
920
921 self.assertQuerySetEqual(
922 qs,
923 [
924 ("Apple", "APPL".lower()),
925 ("Django Software Foundation", "No Tag".lower()),
926 ("Google", "Do No Evil".lower()),
927 ("Yahoo", "Internet Company".lower()),
928 ],
929 lambda c: (c.name, c.tagline_lower),
930 )
931
932 def test_boolean_value_annotation(self):
933 books = Book.objects.annotate(
934 is_book=Value(True, output_field=BooleanField()),
935 is_pony=Value(False, output_field=BooleanField()),
936 is_none=Value(None, output_field=BooleanField(null=True)),
937 )
938 self.assertGreater(len(books), 0)
939 for book in books:
940 self.assertIs(book.is_book, True)
941 self.assertIs(book.is_pony, False)
942 self.assertIsNone(book.is_none)
943
944 def test_annotation_in_f_grouped_by_annotation(self):
945 qs = (
946 Publisher.objects.annotate(multiplier=Value(3))
947 # group by option => sum of value * multiplier
948 .values("name")
949 .annotate(multiplied_value_sum=Sum(F("multiplier") * F("num_awards")))
950 .order_by()
951 )
952 self.assertCountEqual(
953 qs,
954 [
955 {"multiplied_value_sum": 9, "name": "Apress"},
956 {"multiplied_value_sum": 0, "name": "Jonno's House of Books"},
957 {"multiplied_value_sum": 27, "name": "Morgan Kaufmann"},
958 {"multiplied_value_sum": 21, "name": "Prentice Hall"},
959 {"multiplied_value_sum": 3, "name": "Sams"},
960 ],
961 )
962
963 def test_arguments_must_be_expressions(self):
964 msg = "QuerySet.annotate() received non-expression(s): %s."
965 with self.assertRaisesMessage(TypeError, msg % BooleanField()):
966 Book.objects.annotate(BooleanField())
967 with self.assertRaisesMessage(TypeError, msg % True):
968 Book.objects.annotate(is_book=True)
969 with self.assertRaisesMessage(
970 TypeError, msg % ", ".join([str(BooleanField()), "True"])
971 ):
972 Book.objects.annotate(BooleanField(), Value(False), is_book=True)
973
974 def test_complex_annotations_must_have_an_alias(self):
975 complex_annotations = [
976 F("rating") * F("price"),
977 Value("title"),
978 Case(When(pages__gte=400, then=Value("Long")), default=Value("Short")),
979 Subquery(
980 Book.objects.filter(publisher_id=OuterRef("pk"))
981 .order_by("-pubdate")
982 .values("name")[:1]
983 ),
984 Exists(Book.objects.filter(publisher_id=OuterRef("pk"))),
985 ]
986 msg = "Complex annotations require an alias"
987 for annotation in complex_annotations:
988 with self.subTest(annotation=annotation):
989 with self.assertRaisesMessage(TypeError, msg):
990 Book.objects.annotate(annotation)
991
992 def test_chaining_annotation_filter_with_m2m(self):
993 qs = (
994 Author.objects.filter(
995 name="Adrian Holovaty",
996 friends__age=35,
997 )
998 .annotate(
999 jacob_name=F("friends__name"),
1000 )
1001 .filter(
1002 friends__age=29,
1003 )
1004 .annotate(
1005 james_name=F("friends__name"),
1006 )
1007 .values("jacob_name", "james_name")
1008 )
1009 self.assertCountEqual(
1010 qs,
1011 [{"jacob_name": "Jacob Kaplan-Moss", "james_name": "James Bennett"}],
1012 )
1013
1014 def test_annotation_filter_with_subquery(self):
1015 long_books_qs = (
1016 Book.objects.filter(
1017 publisher=OuterRef("pk"),
1018 pages__gt=400,
1019 )
1020 .values("publisher")
1021 .annotate(count=Count("pk"))
1022 .values("count")
1023 )
1024 publisher_books_qs = (
1025 Publisher.objects.annotate(
1026 total_books=Count("book"),
1027 )
1028 .filter(
1029 total_books=Subquery(long_books_qs, output_field=IntegerField()),
1030 )
1031 .values("name")
1032 )
1033 self.assertCountEqual(
1034 publisher_books_qs, [{"name": "Sams"}, {"name": "Morgan Kaufmann"}]
1035 )
1036
1037 def test_annotation_and_alias_filter_in_subquery(self):
1038 awarded_publishers_qs = (
1039 Publisher.objects.filter(num_awards__gt=4)
1040 .annotate(publisher_annotate=Value(1))
1041 .alias(publisher_alias=Value(1))
1042 )
1043 qs = Publisher.objects.filter(pk__in=awarded_publishers_qs)
1044 self.assertCountEqual(qs, [self.p3, self.p4])
1045
1046 def test_annotation_and_alias_filter_related_in_subquery(self):
1047 long_books_qs = (
1048 Book.objects.filter(pages__gt=400)
1049 .annotate(book_annotate=Value(1))
1050 .alias(book_alias=Value(1))
1051 )
1052 publisher_books_qs = Publisher.objects.filter(
1053 book__in=long_books_qs,
1054 ).values("name")
1055 self.assertCountEqual(
1056 publisher_books_qs,
1057 [
1058 {"name": "Apress"},
1059 {"name": "Sams"},
1060 {"name": "Prentice Hall"},
1061 {"name": "Morgan Kaufmann"},
1062 ],
1063 )
1064
1065 def test_annotation_exists_none_query(self):
1066 self.assertIs(
1067 Author.objects.annotate(exists=Exists(Company.objects.none()))
1068 .get(pk=self.a1.pk)
1069 .exists,
1070 False,
1071 )
1072
1073 def test_annotation_exists_aggregate_values_chaining(self):
1074 qs = (
1075 Book.objects.values("publisher")
1076 .annotate(
1077 has_authors=Exists(
1078 Book.authors.through.objects.filter(book=OuterRef("pk"))
1079 ),
1080 max_pubdate=Max("pubdate"),
1081 )
1082 .values_list("max_pubdate", flat=True)
1083 .order_by("max_pubdate")
1084 )
1085 self.assertCountEqual(
1086 qs,
1087 [
1088 datetime.date(1991, 10, 15),
1089 datetime.date(2008, 3, 3),
1090 datetime.date(2008, 6, 23),
1091 datetime.date(2008, 11, 3),
1092 ],
1093 )
1094
1095 @skipUnlessDBFeature("supports_subqueries_in_group_by")
1096 def test_annotation_subquery_and_aggregate_values_chaining(self):
1097 qs = (
1098 Book.objects.annotate(pub_year=ExtractYear("pubdate"))
1099 .values("pub_year")
1100 .annotate(
1101 top_rating=Subquery(
1102 Book.objects.filter(pubdate__year=OuterRef("pub_year"))
1103 .order_by("-rating")
1104 .values("rating")[:1]
1105 ),
1106 total_pages=Sum("pages"),
1107 )
1108 .values("pub_year", "total_pages", "top_rating")
1109 )
1110 self.assertCountEqual(
1111 qs,
1112 [
1113 {"pub_year": 1991, "top_rating": 5.0, "total_pages": 946},
1114 {"pub_year": 1995, "top_rating": 4.0, "total_pages": 1132},
1115 {"pub_year": 2007, "top_rating": 4.5, "total_pages": 447},
1116 {"pub_year": 2008, "top_rating": 4.0, "total_pages": 1178},
1117 ],
1118 )
1119
1120 def test_annotation_subquery_outerref_transform(self):
1121 qs = Book.objects.annotate(
1122 top_rating_year=Subquery(
1123 Book.objects.filter(pubdate__year=OuterRef("pubdate__year"))
1124 .order_by("-rating")
1125 .values("rating")[:1]
1126 ),
1127 ).values("pubdate__year", "top_rating_year")
1128 self.assertCountEqual(
1129 qs,
1130 [
1131 {"pubdate__year": 1991, "top_rating_year": 5.0},
1132 {"pubdate__year": 1995, "top_rating_year": 4.0},
1133 {"pubdate__year": 2007, "top_rating_year": 4.5},
1134 {"pubdate__year": 2008, "top_rating_year": 4.0},
1135 {"pubdate__year": 2008, "top_rating_year": 4.0},
1136 {"pubdate__year": 2008, "top_rating_year": 4.0},
1137 ],
1138 )
1139
1140 def test_annotation_aggregate_with_m2o(self):
1141 qs = (
1142 Author.objects.filter(age__lt=30)
1143 .annotate(
1144 max_pages=Case(
1145 When(book_contact_set__isnull=True, then=Value(0)),
1146 default=Max(F("book__pages")),
1147 ),
1148 )
1149 .values("name", "max_pages")
1150 )
1151 self.assertCountEqual(
1152 qs,
1153 [
1154 {"name": "James Bennett", "max_pages": 300},
1155 {"name": "Paul Bissex", "max_pages": 0},
1156 {"name": "Wesley J. Chun", "max_pages": 0},
1157 ],
1158 )
1159
1160 def test_alias_sql_injection(self):
1161 msg = (
1162 "Column aliases cannot contain whitespace characters, hashes, "
1163 "control characters, quotation marks, semicolons, or SQL comments."
1164 )
1165 for crafted_alias in [
1166 """injected_name" from "annotations_book"; --""",
1167 # Control characters.
1168 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
1169 ]:
1170 with self.subTest(crafted_alias):
1171 with self.assertRaisesMessage(ValueError, msg):
1172 Book.objects.annotate(**{crafted_alias: Value(1)})
1173
1174 def test_alias_filtered_relation_sql_injection(self):
1175 msg = (
1176 "Column aliases cannot contain whitespace characters, hashes, "
1177 "control characters, quotation marks, semicolons, or SQL comments."
1178 )
1179 for crafted_alias in [
1180 """injected_name" from "annotations_book"; --""",
1181 # Control characters.
1182 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
1183 ]:
1184 with self.subTest(crafted_alias):
1185 with self.assertRaisesMessage(ValueError, msg):
1186 Book.objects.annotate(**{crafted_alias: FilteredRelation("author")})
1187
1188 def test_alias_forbidden_chars(self):
1189 tests = [
1190 'al"ias',
1191 "a'lias",
1192 "ali`as",
1193 "alia s",
1194 "alias\t",
1195 "ali\nas",
1196 "alias--",
1197 "ali/*as",
1198 "alias*/",
1199 "alias;",
1200 # [] and # are used by MSSQL.
1201 "alias[",
1202 "alias]",
1203 "ali#as",
1204 "ali\0as",
1205 ]
1206 msg = (
1207 "Column aliases cannot contain whitespace characters, hashes, "
1208 "control characters, quotation marks, semicolons, or SQL comments."
1209 )
1210 for crafted_alias in tests:
1211 with self.subTest(crafted_alias):
1212 with self.assertRaisesMessage(ValueError, msg):
1213 Book.objects.annotate(**{crafted_alias: Value(1)})
1214
1215 with self.assertRaisesMessage(ValueError, msg):
1216 Book.objects.annotate(
1217 **{crafted_alias: FilteredRelation("authors")}
1218 )
1219
1220 @skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
1221 @skipUnlessDBFeature("supports_json_field")
1222 def test_set_returning_functions(self):
1223 class JSONBPathQuery(Func):
1224 function = "jsonb_path_query"
1225 output_field = JSONField()
1226 set_returning = True
1227
1228 test_model = JsonModel.objects.create(
1229 data={"key": [{"id": 1, "name": "test1"}, {"id": 2, "name": "test2"}]}, id=1
1230 )
1231 qs = JsonModel.objects.annotate(
1232 table_element=JSONBPathQuery("data", Value("$.key[*]"))
1233 ).filter(pk=test_model.pk)
1234
1235 self.assertEqual(qs.count(), len(qs))
1236
1237
1238 class AliasTests(TestCase):
1239 @classmethod
1240 def setUpTestData(cls):
1241 cls.a1 = Author.objects.create(name="Adrian Holovaty", age=34)
1242 cls.a2 = Author.objects.create(name="Jacob Kaplan-Moss", age=35)
1243 cls.a3 = Author.objects.create(name="James Bennett", age=34)
1244 cls.a4 = Author.objects.create(name="Peter Norvig", age=57)
1245 cls.a5 = Author.objects.create(name="Stuart Russell", age=46)
1246 p1 = Publisher.objects.create(name="Apress", num_awards=3)
1247
1248 cls.b1 = Book.objects.create(
1249 isbn="159059725",
1250 pages=447,
1251 rating=4.5,
1252 price=Decimal("30.00"),
1253 contact=cls.a1,
1254 publisher=p1,
1255 pubdate=datetime.date(2007, 12, 6),
1256 name="The Definitive Guide to Django: Web Development Done Right",
1257 )
1258 cls.b2 = Book.objects.create(
1259 isbn="159059996",
1260 pages=300,
1261 rating=4.0,
1262 price=Decimal("29.69"),
1263 contact=cls.a3,
1264 publisher=p1,
1265 pubdate=datetime.date(2008, 6, 23),
1266 name="Practical Django Projects",
1267 )
1268 cls.b3 = Book.objects.create(
1269 isbn="013790395",
1270 pages=1132,
1271 rating=4.0,
1272 price=Decimal("82.80"),
1273 contact=cls.a4,
1274 publisher=p1,
1275 pubdate=datetime.date(1995, 1, 15),
1276 name="Artificial Intelligence: A Modern Approach",
1277 )
1278 cls.b4 = Book.objects.create(
1279 isbn="155860191",
1280 pages=946,
1281 rating=5.0,
1282 price=Decimal("75.00"),
1283 contact=cls.a4,
1284 publisher=p1,
1285 pubdate=datetime.date(1991, 10, 15),
1286 name=(
1287 "Paradigms of Artificial Intelligence Programming: Case Studies in "
1288 "Common Lisp"
1289 ),
1290 )
1291 cls.b1.authors.add(cls.a1, cls.a2)
1292 cls.b2.authors.add(cls.a3)
1293 cls.b3.authors.add(cls.a4, cls.a5)
1294 cls.b4.authors.add(cls.a4)
1295
1296 Store.objects.create(
1297 name="Amazon.com",
1298 original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
1299 friday_night_closing=datetime.time(23, 59, 59),
1300 )
1301 Store.objects.create(
1302 name="Books.com",
1303 original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
1304 friday_night_closing=datetime.time(23, 59, 59),
1305 )
1306
1307 def test_basic_alias(self):
1308 qs = Book.objects.alias(is_book=Value(1))
1309 self.assertIs(hasattr(qs.first(), "is_book"), False)
1310
1311 def test_basic_alias_annotation(self):
1312 qs = Book.objects.alias(
1313 is_book_alias=Value(1),
1314 ).annotate(is_book=F("is_book_alias"))
1315 self.assertIs(hasattr(qs.first(), "is_book_alias"), False)
1316 for book in qs:
1317 with self.subTest(book=book):
1318 self.assertEqual(book.is_book, 1)
1319
1320 def test_basic_alias_f_annotation(self):
1321 qs = Book.objects.alias(another_rating_alias=F("rating")).annotate(
1322 another_rating=F("another_rating_alias")
1323 )
1324 self.assertIs(hasattr(qs.first(), "another_rating_alias"), False)
1325 for book in qs:
1326 with self.subTest(book=book):
1327 self.assertEqual(book.another_rating, book.rating)
1328
1329 def test_basic_alias_f_transform_annotation(self):
1330 qs = Book.objects.alias(
1331 pubdate_alias=F("pubdate"),
1332 ).annotate(pubdate_year=F("pubdate_alias__year"))
1333 self.assertIs(hasattr(qs.first(), "pubdate_alias"), False)
1334 for book in qs:
1335 with self.subTest(book=book):
1336 self.assertEqual(book.pubdate_year, book.pubdate.year)
1337
1338 def test_alias_after_annotation(self):
1339 qs = Book.objects.annotate(
1340 is_book=Value(1),
1341 ).alias(is_book_alias=F("is_book"))
1342 book = qs.first()
1343 self.assertIs(hasattr(book, "is_book"), True)
1344 self.assertIs(hasattr(book, "is_book_alias"), False)
1345
1346 def test_overwrite_annotation_with_alias(self):
1347 qs = Book.objects.annotate(is_book=Value(1)).alias(is_book=F("is_book"))
1348 self.assertIs(hasattr(qs.first(), "is_book"), False)
1349
1350 def test_overwrite_alias_with_annotation(self):
1351 qs = Book.objects.alias(is_book=Value(1)).annotate(is_book=F("is_book"))
1352 for book in qs:
1353 with self.subTest(book=book):
1354 self.assertEqual(book.is_book, 1)
1355
1356 def test_alias_annotation_expression(self):
1357 qs = Book.objects.alias(
1358 is_book_alias=Value(1),
1359 ).annotate(is_book=Coalesce("is_book_alias", 0))
1360 self.assertIs(hasattr(qs.first(), "is_book_alias"), False)
1361 for book in qs:
1362 with self.subTest(book=book):
1363 self.assertEqual(book.is_book, 1)
1364
1365 def test_alias_default_alias_expression(self):
1366 qs = Author.objects.alias(
1367 Sum("book__pages"),
1368 ).filter(book__pages__sum__gt=2000)
1369 self.assertIs(hasattr(qs.first(), "book__pages__sum"), False)
1370 self.assertSequenceEqual(qs, [self.a4])
1371
1372 def test_joined_alias_annotation(self):
1373 qs = (
1374 Book.objects.select_related("publisher")
1375 .alias(
1376 num_awards_alias=F("publisher__num_awards"),
1377 )
1378 .annotate(num_awards=F("num_awards_alias"))
1379 )
1380 self.assertIs(hasattr(qs.first(), "num_awards_alias"), False)
1381 for book in qs:
1382 with self.subTest(book=book):
1383 self.assertEqual(book.num_awards, book.publisher.num_awards)
1384
1385 def test_alias_annotate_with_aggregation(self):
1386 qs = Book.objects.alias(
1387 is_book_alias=Value(1),
1388 rating_count_alias=Count("rating"),
1389 ).annotate(
1390 is_book=F("is_book_alias"),
1391 rating_count=F("rating_count_alias"),
1392 )
1393 book = qs.first()
1394 self.assertIs(hasattr(book, "is_book_alias"), False)
1395 self.assertIs(hasattr(book, "rating_count_alias"), False)
1396 for book in qs:
1397 with self.subTest(book=book):
1398 self.assertEqual(book.is_book, 1)
1399 self.assertEqual(book.rating_count, 1)
1400
1401 def test_filter_alias_with_f(self):
1402 qs = Book.objects.alias(
1403 other_rating=F("rating"),
1404 ).filter(other_rating=4.5)
1405 self.assertIs(hasattr(qs.first(), "other_rating"), False)
1406 self.assertSequenceEqual(qs, [self.b1])
1407
1408 def test_filter_alias_with_double_f(self):
1409 qs = Book.objects.alias(
1410 other_rating=F("rating"),
1411 ).filter(other_rating=F("rating"))
1412 self.assertIs(hasattr(qs.first(), "other_rating"), False)
1413 self.assertEqual(qs.count(), Book.objects.count())
1414
1415 def test_filter_alias_agg_with_double_f(self):
1416 qs = Book.objects.alias(
1417 sum_rating=Sum("rating"),
1418 ).filter(sum_rating=F("sum_rating"))
1419 self.assertIs(hasattr(qs.first(), "sum_rating"), False)
1420 self.assertEqual(qs.count(), Book.objects.count())
1421
1422 def test_update_with_alias(self):
1423 Book.objects.alias(
1424 other_rating=F("rating") - 1,
1425 ).update(rating=F("other_rating"))
1426 self.b1.refresh_from_db()
1427 self.assertEqual(self.b1.rating, 3.5)
1428
1429 def test_order_by_alias(self):
1430 qs = Author.objects.alias(other_age=F("age")).order_by("other_age")
1431 self.assertIs(hasattr(qs.first(), "other_age"), False)
1432 self.assertQuerySetEqual(qs, [34, 34, 35, 46, 57], lambda a: a.age)
1433
1434 def test_order_by_alias_aggregate(self):
1435 qs = (
1436 Author.objects.values("age")
1437 .alias(age_count=Count("age"))
1438 .order_by("age_count", "age")
1439 )
1440 self.assertIs(hasattr(qs.first(), "age_count"), False)
1441 self.assertQuerySetEqual(qs, [35, 46, 57, 34], lambda a: a["age"])
1442
1443 def test_dates_alias(self):
1444 qs = Book.objects.alias(
1445 pubdate_alias=F("pubdate"),
1446 ).dates("pubdate_alias", "month")
1447 self.assertCountEqual(
1448 qs,
1449 [
1450 datetime.date(1991, 10, 1),
1451 datetime.date(1995, 1, 1),
1452 datetime.date(2007, 12, 1),
1453 datetime.date(2008, 6, 1),
1454 ],
1455 )
1456
1457 def test_datetimes_alias(self):
1458 qs = Store.objects.alias(
1459 original_opening_alias=F("original_opening"),
1460 ).datetimes("original_opening_alias", "year")
1461 self.assertCountEqual(
1462 qs,
1463 [
1464 datetime.datetime(1994, 1, 1),
1465 datetime.datetime(2001, 1, 1),
1466 ],
1467 )
1468
1469 def test_aggregate_alias(self):
1470 msg = (
1471 "Cannot aggregate over the 'other_age' alias. Use annotate() to promote it."
1472 )
1473 with self.assertRaisesMessage(FieldError, msg):
1474 Author.objects.alias(
1475 other_age=F("age"),
1476 ).aggregate(otherage_sum=Sum("other_age"))
1477
1478 def test_defer_only_alias(self):
1479 qs = Book.objects.alias(rating_alias=F("rating") - 1)
1480 msg = "Book has no field named 'rating_alias'"
1481 for operation in ["defer", "only"]:
1482 with self.subTest(operation=operation):
1483 with self.assertRaisesMessage(FieldDoesNotExist, msg):
1484 getattr(qs, operation)("rating_alias").first()
1485
1486 @skipUnlessDBFeature("can_distinct_on_fields")
1487 def test_distinct_on_alias(self):
1488 qs = Book.objects.alias(rating_alias=F("rating") - 1)
1489 msg = "Cannot resolve keyword 'rating_alias' into field."
1490 with self.assertRaisesMessage(FieldError, msg):
1491 qs.distinct("rating_alias").first()
1492
1493 def test_values_alias(self):
1494 qs = Book.objects.alias(rating_alias=F("rating") - 1)
1495 msg = "Cannot select the 'rating_alias' alias. Use annotate() to promote it."
1496 for operation in ["values", "values_list"]:
1497 with self.subTest(operation=operation):
1498 with self.assertRaisesMessage(FieldError, msg):
1499 getattr(qs, operation)("rating_alias")
1500
1501 def test_alias_after_values(self):
1502 qs = Book.objects.values_list("pk").alias(other_pk=F("pk"))
1503 self.assertEqual(qs.get(pk=self.b1.pk), (self.b1.pk,))
1504
1505 def test_alias_sql_injection(self):
1506 msg = (
1507 "Column aliases cannot contain whitespace characters, hashes, "
1508 "control characters, quotation marks, semicolons, or SQL comments."
1509 )
1510 for crafted_alias in [
1511 """injected_name" from "annotations_book"; --""",
1512 # Control characters.
1513 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
1514 ]:
1515 with self.subTest(crafted_alias):
1516 with self.assertRaisesMessage(ValueError, msg):
1517 Book.objects.alias(**{crafted_alias: Value(1)})
1518
1519 def test_alias_filtered_relation_sql_injection(self):
1520 msg = (
1521 "Column aliases cannot contain whitespace characters, hashes, "
1522 "control characters, quotation marks, semicolons, or SQL comments."
1523 )
1524 for crafted_alias in [
1525 """injected_name" from "annotations_book"; --""",
1526 # Control characters.
1527 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
1528 ]:
1529 with self.subTest(crafted_alias):
1530 with self.assertRaisesMessage(ValueError, msg):
1531 Book.objects.alias(**{crafted_alias: FilteredRelation("authors")})
1532
1533 def test_alias_filtered_relation_sql_injection_dollar_sign(self):
1534 qs = Book.objects.alias(
1535 **{"crafted_alia$": FilteredRelation("authors")}
1536 ).values("name", "crafted_alia$")
1537 if connection.vendor == "postgresql":
1538 msg = "Dollar signs are not permitted in column aliases on PostgreSQL."
1539 with self.assertRaisesMessage(ValueError, msg):
1540 list(qs)
1541 else:
1542 self.assertEqual(qs.first()["name"], self.b1.name)