Debian Codegraph @

tests/filtered_relation/tests.py

raw
1 from datetime import date
2 from decimal import Decimal
3 from unittest import mock
4
5 from django.db import connection, transaction
6 from django.db.models import (
7 BooleanField,
8 Case,
9 Count,
10 DecimalField,
11 ExpressionWrapper,
12 F,
13 FilteredRelation,
14 Q,
15 Sum,
16 Value,
17 When,
18 )
19 from django.db.models.functions import Concat
20 from django.db.models.lookups import Exact, IStartsWith
21 from django.test import TestCase
22 from django.test.testcases import skipUnlessDBFeature
23
24 from .models import (
25 Author,
26 Book,
27 BookDailySales,
28 Borrower,
29 Currency,
30 Editor,
31 ExchangeRate,
32 RentalSession,
33 Reservation,
34 Seller,
35 )
36
37
38 class FilteredRelationTests(TestCase):
39 @classmethod
40 def setUpTestData(cls):
41 cls.author1 = Author.objects.create(name="Alice")
42 cls.author2 = Author.objects.create(name="Jane")
43 cls.editor_a = Editor.objects.create(name="a")
44 cls.editor_b = Editor.objects.create(name="b")
45 cls.book1 = Book.objects.create(
46 title="Poem by Alice",
47 editor=cls.editor_a,
48 author=cls.author1,
49 )
50 cls.book1.generic_author.set([cls.author2])
51 cls.book2 = Book.objects.create(
52 title="The book by Jane A",
53 editor=cls.editor_b,
54 author=cls.author2,
55 )
56 cls.book3 = Book.objects.create(
57 title="The book by Jane B",
58 editor=cls.editor_b,
59 author=cls.author2,
60 )
61 cls.book4 = Book.objects.create(
62 title="The book by Alice",
63 editor=cls.editor_a,
64 author=cls.author1,
65 )
66 cls.author1.favorite_books.add(cls.book2)
67 cls.author1.favorite_books.add(cls.book3)
68
69 def test_select_related(self):
70 qs = (
71 Author.objects.annotate(
72 book_join=FilteredRelation("book"),
73 )
74 .select_related("book_join__editor")
75 .order_by("pk", "book_join__pk")
76 )
77 with self.assertNumQueries(1):
78 self.assertQuerySetEqual(
79 qs,
80 [
81 (self.author1, self.book1, self.editor_a, self.author1),
82 (self.author1, self.book4, self.editor_a, self.author1),
83 (self.author2, self.book2, self.editor_b, self.author2),
84 (self.author2, self.book3, self.editor_b, self.author2),
85 ],
86 lambda x: (x, x.book_join, x.book_join.editor, x.book_join.author),
87 )
88
89 def test_select_related_multiple(self):
90 qs = (
91 Book.objects.annotate(
92 author_join=FilteredRelation("author"),
93 editor_join=FilteredRelation("editor"),
94 )
95 .select_related("author_join", "editor_join")
96 .order_by("pk")
97 )
98 self.assertQuerySetEqual(
99 qs,
100 [
101 (self.book1, self.author1, self.editor_a),
102 (self.book2, self.author2, self.editor_b),
103 (self.book3, self.author2, self.editor_b),
104 (self.book4, self.author1, self.editor_a),
105 ],
106 lambda x: (x, x.author_join, x.editor_join),
107 )
108
109 def test_select_related_with_empty_relation(self):
110 qs = (
111 Author.objects.annotate(
112 book_join=FilteredRelation("book", condition=Q(pk=-1)),
113 )
114 .select_related("book_join")
115 .order_by("pk")
116 )
117 self.assertSequenceEqual(qs, [self.author1, self.author2])
118
119 def test_select_related_foreign_key(self):
120 qs = (
121 Book.objects.annotate(
122 author_join=FilteredRelation("author"),
123 )
124 .select_related("author_join")
125 .order_by("pk")
126 )
127 with self.assertNumQueries(1):
128 self.assertQuerySetEqual(
129 qs,
130 [
131 (self.book1, self.author1),
132 (self.book2, self.author2),
133 (self.book3, self.author2),
134 (self.book4, self.author1),
135 ],
136 lambda x: (x, x.author_join),
137 )
138
139 @skipUnlessDBFeature("has_select_for_update", "has_select_for_update_of")
140 def test_select_related_foreign_key_for_update_of(self):
141 with transaction.atomic():
142 qs = (
143 Book.objects.annotate(
144 author_join=FilteredRelation("author"),
145 )
146 .select_related("author_join")
147 .select_for_update(of=("self",))
148 .order_by("pk")
149 )
150 with self.assertNumQueries(1):
151 self.assertQuerySetEqual(
152 qs,
153 [
154 (self.book1, self.author1),
155 (self.book2, self.author2),
156 (self.book3, self.author2),
157 (self.book4, self.author1),
158 ],
159 lambda x: (x, x.author_join),
160 )
161
162 def test_without_join(self):
163 self.assertCountEqual(
164 Author.objects.annotate(
165 book_alice=FilteredRelation(
166 "book", condition=Q(book__title__iexact="poem by alice")
167 ),
168 ),
169 [self.author1, self.author2],
170 )
171
172 def test_with_join(self):
173 self.assertSequenceEqual(
174 Author.objects.annotate(
175 book_alice=FilteredRelation(
176 "book", condition=Q(book__title__iexact="poem by alice")
177 ),
178 ).filter(book_alice__isnull=False),
179 [self.author1],
180 )
181
182 def test_with_exclude(self):
183 self.assertSequenceEqual(
184 Author.objects.annotate(
185 book_alice=FilteredRelation(
186 "book", condition=Q(book__title__iexact="poem by alice")
187 ),
188 ).exclude(book_alice__isnull=False),
189 [self.author2],
190 )
191
192 def test_with_join_and_complex_condition(self):
193 self.assertSequenceEqual(
194 Author.objects.annotate(
195 book_alice=FilteredRelation(
196 "book",
197 condition=Q(
198 Q(book__title__iexact="poem by alice")
199 | Q(book__state=Book.RENTED)
200 ),
201 ),
202 ).filter(book_alice__isnull=False),
203 [self.author1],
204 )
205
206 def test_internal_queryset_alias_mapping(self):
207 queryset = Author.objects.annotate(
208 book_alice=FilteredRelation(
209 "book", condition=Q(book__title__iexact="poem by alice")
210 ),
211 ).filter(book_alice__isnull=False)
212 self.assertIn(
213 "INNER JOIN {} book_alice ON".format(
214 connection.ops.quote_name("filtered_relation_book")
215 ),
216 str(queryset.query),
217 )
218
219 def test_period_forbidden(self):
220 msg = (
221 "FilteredRelation doesn't support aliases with periods (got 'book.alice')."
222 )
223 with self.assertRaisesMessage(ValueError, msg):
224 Author.objects.annotate(
225 **{
226 "book.alice": FilteredRelation(
227 "book", condition=Q(book__title__iexact="poem by alice")
228 )
229 }
230 )
231
232 def test_multiple(self):
233 qs = (
234 Author.objects.annotate(
235 book_title_alice=FilteredRelation(
236 "book", condition=Q(book__title__contains="Alice")
237 ),
238 book_title_jane=FilteredRelation(
239 "book", condition=Q(book__title__icontains="Jane")
240 ),
241 )
242 .filter(name="Jane")
243 .values("book_title_alice__title", "book_title_jane__title")
244 )
245 empty = "" if connection.features.interprets_empty_strings_as_nulls else None
246 self.assertCountEqual(
247 qs,
248 [
249 {
250 "book_title_alice__title": empty,
251 "book_title_jane__title": "The book by Jane A",
252 },
253 {
254 "book_title_alice__title": empty,
255 "book_title_jane__title": "The book by Jane B",
256 },
257 ],
258 )
259
260 def test_with_multiple_filter(self):
261 self.assertSequenceEqual(
262 Author.objects.annotate(
263 book_editor_a=FilteredRelation(
264 "book",
265 condition=Q(
266 book__title__icontains="book", book__editor_id=self.editor_a.pk
267 ),
268 ),
269 ).filter(book_editor_a__isnull=False),
270 [self.author1],
271 )
272
273 def test_multiple_times(self):
274 self.assertSequenceEqual(
275 Author.objects.annotate(
276 book_title_alice=FilteredRelation(
277 "book", condition=Q(book__title__icontains="alice")
278 ),
279 )
280 .filter(book_title_alice__isnull=False)
281 .filter(book_title_alice__isnull=False)
282 .distinct(),
283 [self.author1],
284 )
285
286 def test_exclude_relation_with_join(self):
287 self.assertSequenceEqual(
288 Author.objects.annotate(
289 book_alice=FilteredRelation(
290 "book", condition=~Q(book__title__icontains="alice")
291 ),
292 )
293 .filter(book_alice__isnull=False)
294 .distinct(),
295 [self.author2],
296 )
297
298 def test_with_m2m(self):
299 qs = Author.objects.annotate(
300 favorite_books_written_by_jane=FilteredRelation(
301 "favorite_books",
302 condition=Q(favorite_books__in=[self.book2]),
303 ),
304 ).filter(favorite_books_written_by_jane__isnull=False)
305 self.assertSequenceEqual(qs, [self.author1])
306
307 def test_with_m2m_deep(self):
308 qs = Author.objects.annotate(
309 favorite_books_written_by_jane=FilteredRelation(
310 "favorite_books",
311 condition=Q(favorite_books__author=self.author2),
312 ),
313 ).filter(favorite_books_written_by_jane__title="The book by Jane B")
314 self.assertSequenceEqual(qs, [self.author1])
315
316 def test_with_m2m_multijoin(self):
317 qs = (
318 Author.objects.annotate(
319 favorite_books_written_by_jane=FilteredRelation(
320 "favorite_books",
321 condition=Q(favorite_books__author=self.author2),
322 )
323 )
324 .filter(favorite_books_written_by_jane__editor__name="b")
325 .distinct()
326 )
327 self.assertSequenceEqual(qs, [self.author1])
328
329 def test_values_list(self):
330 self.assertSequenceEqual(
331 Author.objects.annotate(
332 book_alice=FilteredRelation(
333 "book", condition=Q(book__title__iexact="poem by alice")
334 ),
335 )
336 .filter(book_alice__isnull=False)
337 .values_list("book_alice__title", flat=True),
338 ["Poem by Alice"],
339 )
340
341 def test_values(self):
342 self.assertSequenceEqual(
343 Author.objects.annotate(
344 book_alice=FilteredRelation(
345 "book", condition=Q(book__title__iexact="poem by alice")
346 ),
347 )
348 .filter(book_alice__isnull=False)
349 .values(),
350 [
351 {
352 "id": self.author1.pk,
353 "name": "Alice",
354 "content_type_id": None,
355 "object_id": None,
356 }
357 ],
358 )
359
360 def test_extra(self):
361 self.assertSequenceEqual(
362 Author.objects.annotate(
363 book_alice=FilteredRelation(
364 "book", condition=Q(book__title__iexact="poem by alice")
365 ),
366 )
367 .filter(book_alice__isnull=False)
368 .extra(where=["1 = 1"]),
369 [self.author1],
370 )
371
372 @skipUnlessDBFeature("supports_select_union")
373 def test_union(self):
374 qs1 = Author.objects.annotate(
375 book_alice=FilteredRelation(
376 "book", condition=Q(book__title__iexact="poem by alice")
377 ),
378 ).filter(book_alice__isnull=False)
379 qs2 = Author.objects.annotate(
380 book_jane=FilteredRelation(
381 "book", condition=Q(book__title__iexact="the book by jane a")
382 ),
383 ).filter(book_jane__isnull=False)
384 self.assertSequenceEqual(qs1.union(qs2), [self.author1, self.author2])
385
386 @skipUnlessDBFeature("supports_select_intersection")
387 def test_intersection(self):
388 qs1 = Author.objects.annotate(
389 book_alice=FilteredRelation(
390 "book", condition=Q(book__title__iexact="poem by alice")
391 ),
392 ).filter(book_alice__isnull=False)
393 qs2 = Author.objects.annotate(
394 book_jane=FilteredRelation(
395 "book", condition=Q(book__title__iexact="the book by jane a")
396 ),
397 ).filter(book_jane__isnull=False)
398 self.assertSequenceEqual(qs1.intersection(qs2), [])
399
400 @skipUnlessDBFeature("supports_select_difference")
401 def test_difference(self):
402 qs1 = Author.objects.annotate(
403 book_alice=FilteredRelation(
404 "book", condition=Q(book__title__iexact="poem by alice")
405 ),
406 ).filter(book_alice__isnull=False)
407 qs2 = Author.objects.annotate(
408 book_jane=FilteredRelation(
409 "book", condition=Q(book__title__iexact="the book by jane a")
410 ),
411 ).filter(book_jane__isnull=False)
412 self.assertSequenceEqual(qs1.difference(qs2), [self.author1])
413
414 def test_select_for_update(self):
415 self.assertSequenceEqual(
416 Author.objects.annotate(
417 book_jane=FilteredRelation(
418 "book", condition=Q(book__title__iexact="the book by jane a")
419 ),
420 )
421 .filter(book_jane__isnull=False)
422 .select_for_update(),
423 [self.author2],
424 )
425
426 def test_defer(self):
427 # One query for the list and one query for the deferred title.
428 with self.assertNumQueries(2):
429 self.assertQuerySetEqual(
430 Author.objects.annotate(
431 book_alice=FilteredRelation(
432 "book", condition=Q(book__title__iexact="poem by alice")
433 ),
434 )
435 .filter(book_alice__isnull=False)
436 .select_related("book_alice")
437 .defer("book_alice__title"),
438 ["Poem by Alice"],
439 lambda author: author.book_alice.title,
440 )
441
442 def test_only_not_supported(self):
443 msg = "only() is not supported with FilteredRelation."
444 with self.assertRaisesMessage(ValueError, msg):
445 Author.objects.annotate(
446 book_alice=FilteredRelation(
447 "book", condition=Q(book__title__iexact="poem by alice")
448 ),
449 ).filter(book_alice__isnull=False).select_related("book_alice").only(
450 "book_alice__state"
451 )
452
453 def test_as_subquery(self):
454 inner_qs = Author.objects.annotate(
455 book_alice=FilteredRelation(
456 "book", condition=Q(book__title__iexact="poem by alice")
457 ),
458 ).filter(book_alice__isnull=False)
459 qs = Author.objects.filter(id__in=inner_qs)
460 self.assertSequenceEqual(qs, [self.author1])
461
462 def test_nested_foreign_key(self):
463 qs = (
464 Author.objects.annotate(
465 book_editor_worked_with=FilteredRelation(
466 "book__editor",
467 condition=Q(book__title__icontains="book by"),
468 ),
469 )
470 .filter(
471 book_editor_worked_with__isnull=False,
472 )
473 .select_related(
474 "book_editor_worked_with",
475 )
476 .order_by("pk", "book_editor_worked_with__pk")
477 )
478 with self.assertNumQueries(1):
479 self.assertQuerySetEqual(
480 qs,
481 [
482 (self.author1, self.editor_a),
483 (self.author2, self.editor_b),
484 (self.author2, self.editor_b),
485 ],
486 lambda x: (x, x.book_editor_worked_with),
487 )
488
489 def test_nested_foreign_key_nested_field(self):
490 qs = (
491 Author.objects.annotate(
492 book_editor_worked_with=FilteredRelation(
493 "book__editor", condition=Q(book__title__icontains="book by")
494 ),
495 )
496 .filter(
497 book_editor_worked_with__isnull=False,
498 )
499 .values(
500 "name",
501 "book_editor_worked_with__name",
502 )
503 .order_by("name", "book_editor_worked_with__name")
504 .distinct()
505 )
506 self.assertSequenceEqual(
507 qs,
508 [
509 {
510 "name": self.author1.name,
511 "book_editor_worked_with__name": self.editor_a.name,
512 },
513 {
514 "name": self.author2.name,
515 "book_editor_worked_with__name": self.editor_b.name,
516 },
517 ],
518 )
519
520 def test_nested_foreign_key_filtered_base_object(self):
521 qs = (
522 Author.objects.annotate(
523 alice_editors=FilteredRelation(
524 "book__editor",
525 condition=Q(name="Alice"),
526 ),
527 )
528 .values(
529 "name",
530 "alice_editors__pk",
531 )
532 .order_by("name", "alice_editors__name")
533 .distinct()
534 )
535 self.assertSequenceEqual(
536 qs,
537 [
538 {"name": self.author1.name, "alice_editors__pk": self.editor_a.pk},
539 {"name": self.author2.name, "alice_editors__pk": None},
540 ],
541 )
542
543 def test_nested_m2m_filtered(self):
544 qs = (
545 Book.objects.annotate(
546 favorite_book=FilteredRelation(
547 "author__favorite_books",
548 condition=Q(author__favorite_books__title__icontains="book by"),
549 ),
550 )
551 .values(
552 "title",
553 "favorite_book__pk",
554 )
555 .order_by("title", "favorite_book__title")
556 )
557 self.assertSequenceEqual(
558 qs,
559 [
560 {"title": self.book1.title, "favorite_book__pk": self.book2.pk},
561 {"title": self.book1.title, "favorite_book__pk": self.book3.pk},
562 {"title": self.book4.title, "favorite_book__pk": self.book2.pk},
563 {"title": self.book4.title, "favorite_book__pk": self.book3.pk},
564 {"title": self.book2.title, "favorite_book__pk": None},
565 {"title": self.book3.title, "favorite_book__pk": None},
566 ],
567 )
568
569 def test_nested_chained_relations(self):
570 qs = (
571 Author.objects.annotate(
572 my_books=FilteredRelation(
573 "book",
574 condition=Q(book__title__icontains="book by"),
575 ),
576 preferred_by_authors=FilteredRelation(
577 "my_books__preferred_by_authors",
578 condition=Q(my_books__preferred_by_authors__name="Alice"),
579 ),
580 )
581 .annotate(
582 author=F("name"),
583 book_title=F("my_books__title"),
584 preferred_by_author_pk=F("preferred_by_authors"),
585 )
586 .order_by("author", "book_title", "preferred_by_author_pk")
587 )
588 self.assertQuerySetEqual(
589 qs,
590 [
591 ("Alice", "The book by Alice", None),
592 ("Jane", "The book by Jane A", self.author1.pk),
593 ("Jane", "The book by Jane B", self.author1.pk),
594 ],
595 lambda x: (x.author, x.book_title, x.preferred_by_author_pk),
596 )
597
598 def test_three_level_nested_chained_relations(self):
599 borrower = Borrower.objects.create(name="Jenny")
600 Reservation.objects.create(
601 borrower=borrower,
602 book=self.book1,
603 state=Reservation.STOPPED,
604 )
605 qs = Author.objects.annotate(
606 my_books=FilteredRelation("book"),
607 my_reserved_books=FilteredRelation(
608 "my_books__reservation",
609 condition=Q(my_books__reservation__state=Reservation.STOPPED),
610 ),
611 my_readers=FilteredRelation(
612 "my_reserved_books__borrower",
613 condition=Q(my_reserved_books__borrower=borrower),
614 ),
615 )
616 self.assertSequenceEqual(
617 qs.filter(my_readers=borrower).values_list("name", flat=True), ["Alice"]
618 )
619
620 def test_deep_nested_foreign_key(self):
621 qs = (
622 Book.objects.annotate(
623 author_favorite_book_editor=FilteredRelation(
624 "author__favorite_books__editor",
625 condition=Q(author__favorite_books__title__icontains="Jane A"),
626 ),
627 )
628 .filter(
629 author_favorite_book_editor__isnull=False,
630 )
631 .select_related(
632 "author_favorite_book_editor",
633 )
634 .order_by("pk", "author_favorite_book_editor__pk")
635 )
636 with self.assertNumQueries(1):
637 self.assertQuerySetEqual(
638 qs,
639 [
640 (self.book1, self.editor_b),
641 (self.book4, self.editor_b),
642 ],
643 lambda x: (x, x.author_favorite_book_editor),
644 )
645
646 def test_relation_name_lookup(self):
647 msg = (
648 "FilteredRelation's relation_name cannot contain lookups (got "
649 "'book__title__icontains')."
650 )
651 with self.assertRaisesMessage(ValueError, msg):
652 Author.objects.annotate(
653 book_title=FilteredRelation(
654 "book__title__icontains",
655 condition=Q(book__title="Poem by Alice"),
656 ),
657 )
658
659 def test_condition_outside_relation_name(self):
660 msg = (
661 "FilteredRelation's condition doesn't support relations outside "
662 "the 'book__editor' (got 'book__author__name__icontains')."
663 )
664 with self.assertRaisesMessage(ValueError, msg):
665 Author.objects.annotate(
666 book_editor=FilteredRelation(
667 "book__editor",
668 condition=Q(book__author__name__icontains="book"),
669 ),
670 )
671
672 def test_condition_with_exact_lookup_outside_relation_name(self):
673 qs = Author.objects.annotate(
674 book_editor=FilteredRelation(
675 "book__editor",
676 condition=Q(book__author__name="book"),
677 ),
678 ).filter(book_editor__isnull=True)
679 self.assertEqual(qs.count(), 4)
680
681 def test_condition_with_func_and_lookup_outside_relation_name(self):
682 qs = Author.objects.annotate(
683 book_editor=FilteredRelation(
684 "book__editor",
685 condition=Q(
686 book__title=Concat(Value("The book by "), F("book__author__name"))
687 ),
688 ),
689 ).filter(book_editor__isnull=False)
690 self.assertEqual(qs.count(), 1)
691
692 def test_condition_deeper_relation_name(self):
693 msg = (
694 "FilteredRelation's condition doesn't support nested relations "
695 "deeper than the relation_name (got "
696 "'book__editor__name__icontains' for 'book')."
697 )
698 with self.assertRaisesMessage(ValueError, msg):
699 Author.objects.annotate(
700 book_editor=FilteredRelation(
701 "book",
702 condition=Q(book__editor__name__icontains="b"),
703 ),
704 )
705
706 def test_condition_deeper_relation_name_implicit_exact(self):
707 msg = (
708 "FilteredRelation's condition doesn't support nested relations "
709 "deeper than the relation_name (got 'book__editor__name' for 'book')."
710 )
711 with self.assertRaisesMessage(ValueError, msg):
712 Author.objects.annotate(
713 book_editor=FilteredRelation(
714 "book",
715 condition=Q(book__editor__name="b"),
716 ),
717 )
718
719 def test_with_empty_relation_name_error(self):
720 with self.assertRaisesMessage(ValueError, "relation_name cannot be empty."):
721 FilteredRelation("", condition=Q(blank=""))
722
723 def test_with_condition_as_expression_error(self):
724 msg = "condition argument must be a Q() instance."
725 expression = Case(
726 When(book__title__iexact="poem by alice", then=True),
727 default=False,
728 )
729 with self.assertRaisesMessage(ValueError, msg):
730 FilteredRelation("book", condition=expression)
731
732 def test_with_prefetch_related(self):
733 msg = "prefetch_related() is not supported with FilteredRelation."
734 qs = Author.objects.annotate(
735 book_title_contains_b=FilteredRelation(
736 "book", condition=Q(book__title__icontains="b")
737 ),
738 ).filter(
739 book_title_contains_b__isnull=False,
740 )
741 with self.assertRaisesMessage(ValueError, msg):
742 qs.prefetch_related("book_title_contains_b")
743 with self.assertRaisesMessage(ValueError, msg):
744 qs.prefetch_related("book_title_contains_b__editor")
745
746 def test_with_generic_foreign_key(self):
747 self.assertSequenceEqual(
748 Book.objects.annotate(
749 generic_authored_book=FilteredRelation(
750 "generic_author", condition=Q(generic_author__isnull=False)
751 ),
752 ).filter(generic_authored_book__isnull=False),
753 [self.book1],
754 )
755
756 def test_eq(self):
757 self.assertEqual(
758 FilteredRelation("book", condition=Q(book__title="b")), mock.ANY
759 )
760
761 def test_conditional_expression(self):
762 qs = Author.objects.annotate(
763 the_book=FilteredRelation("book", condition=Q(Value(False))),
764 ).filter(the_book__isnull=False)
765 self.assertSequenceEqual(qs, [])
766
767 def test_expression_outside_relation_name(self):
768 qs = Author.objects.annotate(
769 book_editor=FilteredRelation(
770 "book__editor",
771 condition=Q(
772 Exact(F("book__author__name"), "Alice"),
773 Value(True),
774 book__title__startswith="Poem",
775 ),
776 ),
777 ).filter(book_editor__isnull=False)
778 self.assertSequenceEqual(qs, [self.author1])
779
780 def test_conditional_expression_with_case(self):
781 qs = Book.objects.annotate(
782 alice_author=FilteredRelation(
783 "author",
784 condition=Q(
785 Case(When(author__name="Alice", then=True), default=False),
786 ),
787 ),
788 ).filter(alice_author__isnull=False)
789 self.assertCountEqual(qs, [self.book1, self.book4])
790
791 def test_conditional_expression_outside_relation_name(self):
792 tests = [
793 Q(Case(When(book__author__name="Alice", then=True), default=False)),
794 Q(
795 ExpressionWrapper(
796 Q(Value(True), Exact(F("book__author__name"), "Alice")),
797 output_field=BooleanField(),
798 ),
799 ),
800 ]
801 for condition in tests:
802 with self.subTest(condition=condition):
803 qs = Author.objects.annotate(
804 book_editor=FilteredRelation("book__editor", condition=condition),
805 ).filter(book_editor__isnull=True)
806 self.assertSequenceEqual(qs, [self.author2, self.author2])
807
808 def test_conditional_expression_with_lookup(self):
809 lookups = [
810 Q(book__title__istartswith="poem"),
811 Q(IStartsWith(F("book__title"), "poem")),
812 ]
813 for condition in lookups:
814 with self.subTest(condition=condition):
815 qs = Author.objects.annotate(
816 poem_book=FilteredRelation("book", condition=condition)
817 ).filter(poem_book__isnull=False)
818 self.assertSequenceEqual(qs, [self.author1])
819
820 def test_conditional_expression_with_expressionwrapper(self):
821 qs = Author.objects.annotate(
822 poem_book=FilteredRelation(
823 "book",
824 condition=Q(
825 ExpressionWrapper(
826 Q(Exact(F("book__title"), "Poem by Alice")),
827 output_field=BooleanField(),
828 ),
829 ),
830 ),
831 ).filter(poem_book__isnull=False)
832 self.assertSequenceEqual(qs, [self.author1])
833
834 def test_conditional_expression_with_multiple_fields(self):
835 qs = Author.objects.annotate(
836 my_books=FilteredRelation(
837 "book__author",
838 condition=Q(Exact(F("book__author__name"), F("book__author__name"))),
839 ),
840 ).filter(my_books__isnull=True)
841 self.assertSequenceEqual(qs, [])
842
843 def test_conditional_expression_rhs_contains_relation_name(self):
844 qs = Book.objects.annotate(
845 rel=FilteredRelation(
846 "editor",
847 condition=Q(id=1 * F("number_editor")),
848 )
849 ).filter(rel__isnull=True)
850 self.assertSequenceEqual(qs, [])
851
852 def test_conditional_expression_rhs_startswith_relation_name(self):
853 qs = Book.objects.annotate(
854 rel=FilteredRelation(
855 "editor",
856 condition=Q(id=1 * F("editor_number")),
857 )
858 ).filter(rel__isnull=True)
859 self.assertSequenceEqual(qs, [])
860
861 def test_conditional_expression_lhs_startswith_relation_name(self):
862 qs = Book.objects.annotate(
863 rel=FilteredRelation(
864 "editor",
865 condition=Q(editor_number__gt=1),
866 )
867 ).filter(rel__isnull=True)
868 self.assertSequenceEqual(qs, [])
869
870 def test_conditional_expression_lhs_contains_relation_name(self):
871 qs = Book.objects.annotate(
872 rel=FilteredRelation(
873 "editor",
874 condition=Q(number_editor__gt=1),
875 )
876 ).filter(rel__isnull=True)
877 self.assertSequenceEqual(qs, [])
878
879 def test_conditional_expression_does_not_support_queryset(self):
880 msg = "Passing a QuerySet within a FilteredRelation is not supported."
881 with self.assertRaisesMessage(ValueError, msg):
882 Author.objects.annotate(
883 poem_book=FilteredRelation(
884 "book",
885 condition=Q(book__in=Book.objects.filter(title__istartswith="a")),
886 ),
887 ).filter(poem_book__isnull=False)
888
889
890 class FilteredRelationAggregationTests(TestCase):
891 @classmethod
892 def setUpTestData(cls):
893 cls.author1 = Author.objects.create(name="Alice")
894 cls.editor_a = Editor.objects.create(name="a")
895 cls.book1 = Book.objects.create(
896 title="Poem by Alice",
897 editor=cls.editor_a,
898 author=cls.author1,
899 )
900 cls.borrower1 = Borrower.objects.create(name="Jenny")
901 cls.borrower2 = Borrower.objects.create(name="Kevin")
902 # borrower 1 reserves, rents, and returns book1.
903 Reservation.objects.create(
904 borrower=cls.borrower1,
905 book=cls.book1,
906 state=Reservation.STOPPED,
907 )
908 RentalSession.objects.create(
909 borrower=cls.borrower1,
910 book=cls.book1,
911 state=RentalSession.STOPPED,
912 )
913 # borrower2 reserves, rents, and returns book1.
914 Reservation.objects.create(
915 borrower=cls.borrower2,
916 book=cls.book1,
917 state=Reservation.STOPPED,
918 )
919 RentalSession.objects.create(
920 borrower=cls.borrower2,
921 book=cls.book1,
922 state=RentalSession.STOPPED,
923 )
924
925 def test_aggregate(self):
926 """
927 filtered_relation() not only improves performance but also creates
928 correct results when aggregating with multiple LEFT JOINs.
929
930 Books can be reserved then rented by a borrower. Each reservation and
931 rental session are recorded with Reservation and RentalSession models.
932 Every time a reservation or a rental session is over, their state is
933 changed to 'stopped'.
934
935 Goal: Count number of books that are either currently reserved or
936 rented by borrower1 or available.
937 """
938 qs = (
939 Book.objects.annotate(
940 is_reserved_or_rented_by=Case(
941 When(
942 reservation__state=Reservation.NEW,
943 then=F("reservation__borrower__pk"),
944 ),
945 When(
946 rental_session__state=RentalSession.NEW,
947 then=F("rental_session__borrower__pk"),
948 ),
949 default=None,
950 )
951 )
952 .filter(
953 Q(is_reserved_or_rented_by=self.borrower1.pk) | Q(state=Book.AVAILABLE)
954 )
955 .distinct()
956 )
957 self.assertEqual(qs.count(), 1)
958 # If count is equal to 1, the same aggregation should return in the
959 # same result but it returns 4.
960 self.assertSequenceEqual(
961 qs.annotate(total=Count("pk")).values("total"), [{"total": 4}]
962 )
963 # With FilteredRelation, the result is as expected (1).
964 qs = (
965 Book.objects.annotate(
966 active_reservations=FilteredRelation(
967 "reservation",
968 condition=Q(
969 reservation__state=Reservation.NEW,
970 reservation__borrower=self.borrower1,
971 ),
972 ),
973 )
974 .annotate(
975 active_rental_sessions=FilteredRelation(
976 "rental_session",
977 condition=Q(
978 rental_session__state=RentalSession.NEW,
979 rental_session__borrower=self.borrower1,
980 ),
981 ),
982 )
983 .filter(
984 (
985 Q(active_reservations__isnull=False)
986 | Q(active_rental_sessions__isnull=False)
987 )
988 | Q(state=Book.AVAILABLE)
989 )
990 .distinct()
991 )
992 self.assertEqual(qs.count(), 1)
993 self.assertSequenceEqual(
994 qs.annotate(total=Count("pk")).values("total"), [{"total": 1}]
995 )
996
997 def test_condition_spans_join(self):
998 self.assertSequenceEqual(
999 Book.objects.annotate(
1000 contains_editor_author=FilteredRelation(
1001 "author", condition=Q(author__name__icontains=F("editor__name"))
1002 )
1003 ).filter(
1004 contains_editor_author__isnull=False,
1005 ),
1006 [self.book1],
1007 )
1008
1009 def test_condition_spans_join_chained(self):
1010 self.assertSequenceEqual(
1011 Book.objects.annotate(
1012 contains_editor_author=FilteredRelation(
1013 "author", condition=Q(author__name__icontains=F("editor__name"))
1014 ),
1015 contains_editor_author_ref=FilteredRelation(
1016 "author",
1017 condition=Q(author__name=F("contains_editor_author__name")),
1018 ),
1019 ).filter(
1020 contains_editor_author_ref__isnull=False,
1021 ),
1022 [self.book1],
1023 )
1024
1025 def test_condition_self_ref(self):
1026 self.assertSequenceEqual(
1027 Book.objects.annotate(
1028 contains_author=FilteredRelation(
1029 "author",
1030 condition=Q(title__icontains=F("author__name")),
1031 )
1032 ).filter(
1033 contains_author__isnull=False,
1034 ),
1035 [self.book1],
1036 )
1037
1038
1039 class FilteredRelationAnalyticalAggregationTests(TestCase):
1040 @classmethod
1041 def setUpTestData(cls):
1042 author = Author.objects.create(name="Author")
1043 editor = Editor.objects.create(name="Editor")
1044 cls.book1 = Book.objects.create(
1045 title="Poem by Alice",
1046 editor=editor,
1047 author=author,
1048 )
1049 cls.book2 = Book.objects.create(
1050 title="The book by Jane A",
1051 editor=editor,
1052 author=author,
1053 )
1054 cls.book3 = Book.objects.create(
1055 title="The book by Jane B",
1056 editor=editor,
1057 author=author,
1058 )
1059 cls.seller1 = Seller.objects.create(name="Seller 1")
1060 cls.seller2 = Seller.objects.create(name="Seller 2")
1061 cls.usd = Currency.objects.create(currency="USD")
1062 cls.eur = Currency.objects.create(currency="EUR")
1063 cls.sales_date1 = date(2020, 7, 6)
1064 cls.sales_date2 = date(2020, 7, 7)
1065 ExchangeRate.objects.bulk_create(
1066 [
1067 ExchangeRate(
1068 rate_date=cls.sales_date1,
1069 from_currency=cls.usd,
1070 to_currency=cls.eur,
1071 rate=0.40,
1072 ),
1073 ExchangeRate(
1074 rate_date=cls.sales_date1,
1075 from_currency=cls.eur,
1076 to_currency=cls.usd,
1077 rate=1.60,
1078 ),
1079 ExchangeRate(
1080 rate_date=cls.sales_date2,
1081 from_currency=cls.usd,
1082 to_currency=cls.eur,
1083 rate=0.50,
1084 ),
1085 ExchangeRate(
1086 rate_date=cls.sales_date2,
1087 from_currency=cls.eur,
1088 to_currency=cls.usd,
1089 rate=1.50,
1090 ),
1091 ExchangeRate(
1092 rate_date=cls.sales_date2,
1093 from_currency=cls.usd,
1094 to_currency=cls.usd,
1095 rate=1.00,
1096 ),
1097 ]
1098 )
1099 BookDailySales.objects.bulk_create(
1100 [
1101 BookDailySales(
1102 book=cls.book1,
1103 sale_date=cls.sales_date1,
1104 currency=cls.usd,
1105 sales=100.00,
1106 seller=cls.seller1,
1107 ),
1108 BookDailySales(
1109 book=cls.book2,
1110 sale_date=cls.sales_date1,
1111 currency=cls.eur,
1112 sales=200.00,
1113 seller=cls.seller1,
1114 ),
1115 BookDailySales(
1116 book=cls.book1,
1117 sale_date=cls.sales_date2,
1118 currency=cls.usd,
1119 sales=50.00,
1120 seller=cls.seller2,
1121 ),
1122 BookDailySales(
1123 book=cls.book2,
1124 sale_date=cls.sales_date2,
1125 currency=cls.eur,
1126 sales=100.00,
1127 seller=cls.seller2,
1128 ),
1129 ]
1130 )
1131
1132 def test_aggregate(self):
1133 tests = [
1134 Q(daily_sales__sale_date__gte=self.sales_date2),
1135 ~Q(daily_sales__seller=self.seller1),
1136 ]
1137 for condition in tests:
1138 with self.subTest(condition=condition):
1139 qs = (
1140 Book.objects.annotate(
1141 recent_sales=FilteredRelation(
1142 "daily_sales", condition=condition
1143 ),
1144 recent_sales_rates=FilteredRelation(
1145 "recent_sales__currency__rates_from",
1146 condition=Q(
1147 recent_sales__currency__rates_from__rate_date=F(
1148 "recent_sales__sale_date"
1149 ),
1150 recent_sales__currency__rates_from__to_currency=(
1151 self.usd
1152 ),
1153 ),
1154 ),
1155 )
1156 .annotate(
1157 sales_sum=Sum(
1158 F("recent_sales__sales") * F("recent_sales_rates__rate"),
1159 output_field=DecimalField(),
1160 ),
1161 )
1162 .values("title", "sales_sum")
1163 .order_by(
1164 F("sales_sum").desc(nulls_last=True),
1165 )
1166 )
1167 self.assertSequenceEqual(
1168 qs,
1169 [
1170 {"title": self.book2.title, "sales_sum": Decimal(150.00)},
1171 {"title": self.book1.title, "sales_sum": Decimal(50.00)},
1172 {"title": self.book3.title, "sales_sum": None},
1173 ],
1174 )