Debian Codegraph @

tests/expressions_case/tests.py

raw
1 import unittest
2 from datetime import date, datetime, time, timedelta
3 from decimal import Decimal
4 from operator import attrgetter, itemgetter
5 from uuid import UUID
6
7 from django.core.exceptions import FieldError
8 from django.db import connection
9 from django.db.models import (
10 BinaryField,
11 BooleanField,
12 Case,
13 Count,
14 DecimalField,
15 F,
16 GenericIPAddressField,
17 IntegerField,
18 Max,
19 Min,
20 Q,
21 Sum,
22 TextField,
23 Value,
24 When,
25 )
26 from django.test import SimpleTestCase, TestCase
27
28 from .models import CaseTestModel, Client, FKCaseTestModel, O2OCaseTestModel
29
30 try:
31 from PIL import Image
32 except ImportError:
33 Image = None
34
35
36 class CaseExpressionTests(TestCase):
37 @classmethod
38 def setUpTestData(cls):
39 o = CaseTestModel.objects.create(integer=1, integer2=1, string="1")
40 O2OCaseTestModel.objects.create(o2o=o, integer=1)
41 FKCaseTestModel.objects.create(fk=o, integer=1)
42
43 o = CaseTestModel.objects.create(integer=2, integer2=3, string="2")
44 O2OCaseTestModel.objects.create(o2o=o, integer=2)
45 FKCaseTestModel.objects.create(fk=o, integer=2)
46 FKCaseTestModel.objects.create(fk=o, integer=3)
47
48 o = CaseTestModel.objects.create(integer=3, integer2=4, string="3")
49 O2OCaseTestModel.objects.create(o2o=o, integer=3)
50 FKCaseTestModel.objects.create(fk=o, integer=3)
51 FKCaseTestModel.objects.create(fk=o, integer=4)
52
53 o = CaseTestModel.objects.create(integer=2, integer2=2, string="2")
54 O2OCaseTestModel.objects.create(o2o=o, integer=2)
55 FKCaseTestModel.objects.create(fk=o, integer=2)
56 FKCaseTestModel.objects.create(fk=o, integer=3)
57
58 o = CaseTestModel.objects.create(integer=3, integer2=4, string="3")
59 O2OCaseTestModel.objects.create(o2o=o, integer=3)
60 FKCaseTestModel.objects.create(fk=o, integer=3)
61 FKCaseTestModel.objects.create(fk=o, integer=4)
62
63 o = CaseTestModel.objects.create(integer=3, integer2=3, string="3")
64 O2OCaseTestModel.objects.create(o2o=o, integer=3)
65 FKCaseTestModel.objects.create(fk=o, integer=3)
66 FKCaseTestModel.objects.create(fk=o, integer=4)
67
68 o = CaseTestModel.objects.create(integer=4, integer2=5, string="4")
69 O2OCaseTestModel.objects.create(o2o=o, integer=1)
70 FKCaseTestModel.objects.create(fk=o, integer=5)
71
72 cls.group_by_fields = [
73 f.name
74 for f in CaseTestModel._meta.get_fields()
75 if not (f.is_relation and f.auto_created)
76 and (
77 connection.features.allows_group_by_lob
78 or not isinstance(f, (BinaryField, TextField))
79 )
80 ]
81
82 def test_annotate(self):
83 self.assertQuerySetEqual(
84 CaseTestModel.objects.annotate(
85 test=Case(
86 When(integer=1, then=Value("one")),
87 When(integer=2, then=Value("two")),
88 default=Value("other"),
89 )
90 ).order_by("pk"),
91 [
92 (1, "one"),
93 (2, "two"),
94 (3, "other"),
95 (2, "two"),
96 (3, "other"),
97 (3, "other"),
98 (4, "other"),
99 ],
100 transform=attrgetter("integer", "test"),
101 )
102
103 def test_annotate_without_default(self):
104 self.assertQuerySetEqual(
105 CaseTestModel.objects.annotate(
106 test=Case(
107 When(integer=1, then=1),
108 When(integer=2, then=2),
109 )
110 ).order_by("pk"),
111 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
112 transform=attrgetter("integer", "test"),
113 )
114
115 def test_annotate_with_expression_as_value(self):
116 self.assertQuerySetEqual(
117 CaseTestModel.objects.annotate(
118 f_test=Case(
119 When(integer=1, then=F("integer") + 1),
120 When(integer=2, then=F("integer") + 3),
121 default="integer",
122 )
123 ).order_by("pk"),
124 [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 4)],
125 transform=attrgetter("integer", "f_test"),
126 )
127
128 def test_annotate_with_expression_as_condition(self):
129 self.assertQuerySetEqual(
130 CaseTestModel.objects.annotate(
131 f_test=Case(
132 When(integer2=F("integer"), then=Value("equal")),
133 When(integer2=F("integer") + 1, then=Value("+1")),
134 )
135 ).order_by("pk"),
136 [
137 (1, "equal"),
138 (2, "+1"),
139 (3, "+1"),
140 (2, "equal"),
141 (3, "+1"),
142 (3, "equal"),
143 (4, "+1"),
144 ],
145 transform=attrgetter("integer", "f_test"),
146 )
147
148 def test_annotate_with_join_in_value(self):
149 self.assertQuerySetEqual(
150 CaseTestModel.objects.annotate(
151 join_test=Case(
152 When(integer=1, then=F("o2o_rel__integer") + 1),
153 When(integer=2, then=F("o2o_rel__integer") + 3),
154 default="o2o_rel__integer",
155 )
156 ).order_by("pk"),
157 [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 1)],
158 transform=attrgetter("integer", "join_test"),
159 )
160
161 def test_annotate_with_in_clause(self):
162 fk_rels = FKCaseTestModel.objects.filter(integer__in=[5])
163 self.assertQuerySetEqual(
164 CaseTestModel.objects.only("pk", "integer")
165 .annotate(
166 in_test=Sum(
167 Case(
168 When(fk_rel__in=fk_rels, then=F("fk_rel__integer")),
169 default=Value(0),
170 )
171 )
172 )
173 .order_by("pk"),
174 [(1, 0), (2, 0), (3, 0), (2, 0), (3, 0), (3, 0), (4, 5)],
175 transform=attrgetter("integer", "in_test"),
176 )
177
178 def test_annotate_with_join_in_condition(self):
179 self.assertQuerySetEqual(
180 CaseTestModel.objects.annotate(
181 join_test=Case(
182 When(integer2=F("o2o_rel__integer"), then=Value("equal")),
183 When(integer2=F("o2o_rel__integer") + 1, then=Value("+1")),
184 default=Value("other"),
185 )
186 ).order_by("pk"),
187 [
188 (1, "equal"),
189 (2, "+1"),
190 (3, "+1"),
191 (2, "equal"),
192 (3, "+1"),
193 (3, "equal"),
194 (4, "other"),
195 ],
196 transform=attrgetter("integer", "join_test"),
197 )
198
199 def test_annotate_with_join_in_predicate(self):
200 self.assertQuerySetEqual(
201 CaseTestModel.objects.annotate(
202 join_test=Case(
203 When(o2o_rel__integer=1, then=Value("one")),
204 When(o2o_rel__integer=2, then=Value("two")),
205 When(o2o_rel__integer=3, then=Value("three")),
206 default=Value("other"),
207 )
208 ).order_by("pk"),
209 [
210 (1, "one"),
211 (2, "two"),
212 (3, "three"),
213 (2, "two"),
214 (3, "three"),
215 (3, "three"),
216 (4, "one"),
217 ],
218 transform=attrgetter("integer", "join_test"),
219 )
220
221 def test_annotate_with_annotation_in_value(self):
222 self.assertQuerySetEqual(
223 CaseTestModel.objects.annotate(
224 f_plus_1=F("integer") + 1,
225 f_plus_3=F("integer") + 3,
226 )
227 .annotate(
228 f_test=Case(
229 When(integer=1, then="f_plus_1"),
230 When(integer=2, then="f_plus_3"),
231 default="integer",
232 ),
233 )
234 .order_by("pk"),
235 [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 4)],
236 transform=attrgetter("integer", "f_test"),
237 )
238
239 def test_annotate_with_annotation_in_condition(self):
240 self.assertQuerySetEqual(
241 CaseTestModel.objects.annotate(
242 f_plus_1=F("integer") + 1,
243 )
244 .annotate(
245 f_test=Case(
246 When(integer2=F("integer"), then=Value("equal")),
247 When(integer2=F("f_plus_1"), then=Value("+1")),
248 ),
249 )
250 .order_by("pk"),
251 [
252 (1, "equal"),
253 (2, "+1"),
254 (3, "+1"),
255 (2, "equal"),
256 (3, "+1"),
257 (3, "equal"),
258 (4, "+1"),
259 ],
260 transform=attrgetter("integer", "f_test"),
261 )
262
263 def test_annotate_with_annotation_in_predicate(self):
264 self.assertQuerySetEqual(
265 CaseTestModel.objects.annotate(
266 f_minus_2=F("integer") - 2,
267 )
268 .annotate(
269 test=Case(
270 When(f_minus_2=-1, then=Value("negative one")),
271 When(f_minus_2=0, then=Value("zero")),
272 When(f_minus_2=1, then=Value("one")),
273 default=Value("other"),
274 ),
275 )
276 .order_by("pk"),
277 [
278 (1, "negative one"),
279 (2, "zero"),
280 (3, "one"),
281 (2, "zero"),
282 (3, "one"),
283 (3, "one"),
284 (4, "other"),
285 ],
286 transform=attrgetter("integer", "test"),
287 )
288
289 def test_annotate_with_aggregation_in_value(self):
290 self.assertQuerySetEqual(
291 CaseTestModel.objects.values(*self.group_by_fields)
292 .annotate(
293 min=Min("fk_rel__integer"),
294 max=Max("fk_rel__integer"),
295 )
296 .annotate(
297 test=Case(
298 When(integer=2, then="min"),
299 When(integer=3, then="max"),
300 ),
301 )
302 .order_by("pk"),
303 [
304 (1, None, 1, 1),
305 (2, 2, 2, 3),
306 (3, 4, 3, 4),
307 (2, 2, 2, 3),
308 (3, 4, 3, 4),
309 (3, 4, 3, 4),
310 (4, None, 5, 5),
311 ],
312 transform=itemgetter("integer", "test", "min", "max"),
313 )
314
315 def test_annotate_with_aggregation_in_condition(self):
316 self.assertQuerySetEqual(
317 CaseTestModel.objects.values(*self.group_by_fields)
318 .annotate(
319 min=Min("fk_rel__integer"),
320 max=Max("fk_rel__integer"),
321 )
322 .annotate(
323 test=Case(
324 When(integer2=F("min"), then=Value("min")),
325 When(integer2=F("max"), then=Value("max")),
326 ),
327 )
328 .order_by("pk"),
329 [
330 (1, 1, "min"),
331 (2, 3, "max"),
332 (3, 4, "max"),
333 (2, 2, "min"),
334 (3, 4, "max"),
335 (3, 3, "min"),
336 (4, 5, "min"),
337 ],
338 transform=itemgetter("integer", "integer2", "test"),
339 )
340
341 def test_annotate_with_aggregation_in_predicate(self):
342 self.assertQuerySetEqual(
343 CaseTestModel.objects.values(*self.group_by_fields)
344 .annotate(
345 max=Max("fk_rel__integer"),
346 )
347 .annotate(
348 test=Case(
349 When(max=3, then=Value("max = 3")),
350 When(max=4, then=Value("max = 4")),
351 default=Value(""),
352 ),
353 )
354 .order_by("pk"),
355 [
356 (1, 1, ""),
357 (2, 3, "max = 3"),
358 (3, 4, "max = 4"),
359 (2, 3, "max = 3"),
360 (3, 4, "max = 4"),
361 (3, 4, "max = 4"),
362 (4, 5, ""),
363 ],
364 transform=itemgetter("integer", "max", "test"),
365 )
366
367 def test_annotate_exclude(self):
368 self.assertQuerySetEqual(
369 CaseTestModel.objects.annotate(
370 test=Case(
371 When(integer=1, then=Value("one")),
372 When(integer=2, then=Value("two")),
373 default=Value("other"),
374 )
375 )
376 .exclude(test="other")
377 .order_by("pk"),
378 [(1, "one"), (2, "two"), (2, "two")],
379 transform=attrgetter("integer", "test"),
380 )
381
382 def test_annotate_filter_decimal(self):
383 obj = CaseTestModel.objects.create(integer=0, decimal=Decimal("1"))
384 qs = CaseTestModel.objects.annotate(
385 x=Case(When(integer=0, then=F("decimal"))),
386 y=Case(When(integer=0, then=Value(Decimal("1")))),
387 )
388 self.assertSequenceEqual(qs.filter(Q(x=1) & Q(x=Decimal("1"))), [obj])
389 self.assertSequenceEqual(qs.filter(Q(y=1) & Q(y=Decimal("1"))), [obj])
390
391 def test_annotate_values_not_in_order_by(self):
392 self.assertEqual(
393 list(
394 CaseTestModel.objects.annotate(
395 test=Case(
396 When(integer=1, then=Value("one")),
397 When(integer=2, then=Value("two")),
398 When(integer=3, then=Value("three")),
399 default=Value("other"),
400 )
401 )
402 .order_by("test")
403 .values_list("integer", flat=True)
404 ),
405 [1, 4, 3, 3, 3, 2, 2],
406 )
407
408 def test_annotate_with_empty_when(self):
409 objects = CaseTestModel.objects.annotate(
410 selected=Case(
411 When(pk__in=[], then=Value("selected")),
412 default=Value("not selected"),
413 )
414 )
415 self.assertEqual(len(objects), CaseTestModel.objects.count())
416 self.assertTrue(all(obj.selected == "not selected" for obj in objects))
417
418 def test_annotate_with_full_when(self):
419 objects = CaseTestModel.objects.annotate(
420 selected=Case(
421 When(~Q(pk__in=[]), then=Value("selected")),
422 default=Value("not selected"),
423 )
424 )
425 self.assertEqual(len(objects), CaseTestModel.objects.count())
426 self.assertTrue(all(obj.selected == "selected" for obj in objects))
427
428 def test_combined_expression(self):
429 self.assertQuerySetEqual(
430 CaseTestModel.objects.annotate(
431 test=Case(
432 When(integer=1, then=2),
433 When(integer=2, then=1),
434 default=3,
435 )
436 + 1,
437 ).order_by("pk"),
438 [(1, 3), (2, 2), (3, 4), (2, 2), (3, 4), (3, 4), (4, 4)],
439 transform=attrgetter("integer", "test"),
440 )
441
442 def test_in_subquery(self):
443 self.assertQuerySetEqual(
444 CaseTestModel.objects.filter(
445 pk__in=CaseTestModel.objects.annotate(
446 test=Case(
447 When(integer=F("integer2"), then="pk"),
448 When(integer=4, then="pk"),
449 ),
450 ).values("test")
451 ).order_by("pk"),
452 [(1, 1), (2, 2), (3, 3), (4, 5)],
453 transform=attrgetter("integer", "integer2"),
454 )
455
456 def test_condition_with_lookups(self):
457 qs = CaseTestModel.objects.annotate(
458 test=Case(
459 When(Q(integer2=1), string="2", then=Value(False)),
460 When(Q(integer2=1), string="1", then=Value(True)),
461 default=Value(False),
462 output_field=BooleanField(),
463 ),
464 )
465 self.assertIs(qs.get(integer=1).test, True)
466
467 def test_case_reuse(self):
468 SOME_CASE = Case(
469 When(pk=0, then=Value("0")),
470 default=Value("1"),
471 )
472 self.assertQuerySetEqual(
473 CaseTestModel.objects.annotate(somecase=SOME_CASE).order_by("pk"),
474 CaseTestModel.objects.annotate(somecase=SOME_CASE)
475 .order_by("pk")
476 .values_list("pk", "somecase"),
477 lambda x: (x.pk, x.somecase),
478 )
479
480 def test_aggregate(self):
481 self.assertEqual(
482 CaseTestModel.objects.aggregate(
483 one=Sum(
484 Case(
485 When(integer=1, then=1),
486 )
487 ),
488 two=Sum(
489 Case(
490 When(integer=2, then=1),
491 )
492 ),
493 three=Sum(
494 Case(
495 When(integer=3, then=1),
496 )
497 ),
498 four=Sum(
499 Case(
500 When(integer=4, then=1),
501 )
502 ),
503 ),
504 {"one": 1, "two": 2, "three": 3, "four": 1},
505 )
506
507 def test_aggregate_with_expression_as_value(self):
508 self.assertEqual(
509 CaseTestModel.objects.aggregate(
510 one=Sum(Case(When(integer=1, then="integer"))),
511 two=Sum(Case(When(integer=2, then=F("integer") - 1))),
512 three=Sum(Case(When(integer=3, then=F("integer") + 1))),
513 ),
514 {"one": 1, "two": 2, "three": 12},
515 )
516
517 def test_aggregate_with_expression_as_condition(self):
518 self.assertEqual(
519 CaseTestModel.objects.aggregate(
520 equal=Sum(
521 Case(
522 When(integer2=F("integer"), then=1),
523 )
524 ),
525 plus_one=Sum(
526 Case(
527 When(integer2=F("integer") + 1, then=1),
528 )
529 ),
530 ),
531 {"equal": 3, "plus_one": 4},
532 )
533
534 def test_filter(self):
535 self.assertQuerySetEqual(
536 CaseTestModel.objects.filter(
537 integer2=Case(
538 When(integer=2, then=3),
539 When(integer=3, then=4),
540 default=1,
541 )
542 ).order_by("pk"),
543 [(1, 1), (2, 3), (3, 4), (3, 4)],
544 transform=attrgetter("integer", "integer2"),
545 )
546
547 def test_filter_without_default(self):
548 self.assertQuerySetEqual(
549 CaseTestModel.objects.filter(
550 integer2=Case(
551 When(integer=2, then=3),
552 When(integer=3, then=4),
553 )
554 ).order_by("pk"),
555 [(2, 3), (3, 4), (3, 4)],
556 transform=attrgetter("integer", "integer2"),
557 )
558
559 def test_filter_with_expression_as_value(self):
560 self.assertQuerySetEqual(
561 CaseTestModel.objects.filter(
562 integer2=Case(
563 When(integer=2, then=F("integer") + 1),
564 When(integer=3, then=F("integer")),
565 default="integer",
566 )
567 ).order_by("pk"),
568 [(1, 1), (2, 3), (3, 3)],
569 transform=attrgetter("integer", "integer2"),
570 )
571
572 def test_filter_with_expression_as_condition(self):
573 self.assertQuerySetEqual(
574 CaseTestModel.objects.filter(
575 string=Case(
576 When(integer2=F("integer"), then=Value("2")),
577 When(integer2=F("integer") + 1, then=Value("3")),
578 )
579 ).order_by("pk"),
580 [(3, 4, "3"), (2, 2, "2"), (3, 4, "3")],
581 transform=attrgetter("integer", "integer2", "string"),
582 )
583
584 def test_filter_with_join_in_value(self):
585 self.assertQuerySetEqual(
586 CaseTestModel.objects.filter(
587 integer2=Case(
588 When(integer=2, then=F("o2o_rel__integer") + 1),
589 When(integer=3, then=F("o2o_rel__integer")),
590 default="o2o_rel__integer",
591 )
592 ).order_by("pk"),
593 [(1, 1), (2, 3), (3, 3)],
594 transform=attrgetter("integer", "integer2"),
595 )
596
597 def test_filter_with_join_in_condition(self):
598 self.assertQuerySetEqual(
599 CaseTestModel.objects.filter(
600 integer=Case(
601 When(integer2=F("o2o_rel__integer") + 1, then=2),
602 When(integer2=F("o2o_rel__integer"), then=3),
603 )
604 ).order_by("pk"),
605 [(2, 3), (3, 3)],
606 transform=attrgetter("integer", "integer2"),
607 )
608
609 def test_filter_with_join_in_predicate(self):
610 self.assertQuerySetEqual(
611 CaseTestModel.objects.filter(
612 integer2=Case(
613 When(o2o_rel__integer=1, then=1),
614 When(o2o_rel__integer=2, then=3),
615 When(o2o_rel__integer=3, then=4),
616 )
617 ).order_by("pk"),
618 [(1, 1), (2, 3), (3, 4), (3, 4)],
619 transform=attrgetter("integer", "integer2"),
620 )
621
622 def test_filter_with_annotation_in_value(self):
623 self.assertQuerySetEqual(
624 CaseTestModel.objects.annotate(
625 f=F("integer"),
626 f_plus_1=F("integer") + 1,
627 )
628 .filter(
629 integer2=Case(
630 When(integer=2, then="f_plus_1"),
631 When(integer=3, then="f"),
632 ),
633 )
634 .order_by("pk"),
635 [(2, 3), (3, 3)],
636 transform=attrgetter("integer", "integer2"),
637 )
638
639 def test_filter_with_annotation_in_condition(self):
640 self.assertQuerySetEqual(
641 CaseTestModel.objects.annotate(
642 f_plus_1=F("integer") + 1,
643 )
644 .filter(
645 integer=Case(
646 When(integer2=F("integer"), then=2),
647 When(integer2=F("f_plus_1"), then=3),
648 ),
649 )
650 .order_by("pk"),
651 [(3, 4), (2, 2), (3, 4)],
652 transform=attrgetter("integer", "integer2"),
653 )
654
655 def test_filter_with_annotation_in_predicate(self):
656 self.assertQuerySetEqual(
657 CaseTestModel.objects.annotate(
658 f_plus_1=F("integer") + 1,
659 )
660 .filter(
661 integer2=Case(
662 When(f_plus_1=3, then=3),
663 When(f_plus_1=4, then=4),
664 default=1,
665 ),
666 )
667 .order_by("pk"),
668 [(1, 1), (2, 3), (3, 4), (3, 4)],
669 transform=attrgetter("integer", "integer2"),
670 )
671
672 def test_filter_with_aggregation_in_value(self):
673 self.assertQuerySetEqual(
674 CaseTestModel.objects.values(*self.group_by_fields)
675 .annotate(
676 min=Min("fk_rel__integer"),
677 max=Max("fk_rel__integer"),
678 )
679 .filter(
680 integer2=Case(
681 When(integer=2, then="min"),
682 When(integer=3, then="max"),
683 ),
684 )
685 .order_by("pk"),
686 [(3, 4, 3, 4), (2, 2, 2, 3), (3, 4, 3, 4)],
687 transform=itemgetter("integer", "integer2", "min", "max"),
688 )
689
690 def test_filter_with_aggregation_in_condition(self):
691 self.assertQuerySetEqual(
692 CaseTestModel.objects.values(*self.group_by_fields)
693 .annotate(
694 min=Min("fk_rel__integer"),
695 max=Max("fk_rel__integer"),
696 )
697 .filter(
698 integer=Case(
699 When(integer2=F("min"), then=2),
700 When(integer2=F("max"), then=3),
701 ),
702 )
703 .order_by("pk"),
704 [(3, 4, 3, 4), (2, 2, 2, 3), (3, 4, 3, 4)],
705 transform=itemgetter("integer", "integer2", "min", "max"),
706 )
707
708 def test_filter_with_aggregation_in_predicate(self):
709 self.assertQuerySetEqual(
710 CaseTestModel.objects.values(*self.group_by_fields)
711 .annotate(
712 max=Max("fk_rel__integer"),
713 )
714 .filter(
715 integer=Case(
716 When(max=3, then=2),
717 When(max=4, then=3),
718 ),
719 )
720 .order_by("pk"),
721 [(2, 3, 3), (3, 4, 4), (2, 2, 3), (3, 4, 4), (3, 3, 4)],
722 transform=itemgetter("integer", "integer2", "max"),
723 )
724
725 def test_update(self):
726 CaseTestModel.objects.update(
727 string=Case(
728 When(integer=1, then=Value("one")),
729 When(integer=2, then=Value("two")),
730 default=Value("other"),
731 ),
732 )
733 self.assertQuerySetEqual(
734 CaseTestModel.objects.order_by("pk"),
735 [
736 (1, "one"),
737 (2, "two"),
738 (3, "other"),
739 (2, "two"),
740 (3, "other"),
741 (3, "other"),
742 (4, "other"),
743 ],
744 transform=attrgetter("integer", "string"),
745 )
746
747 def test_update_without_default(self):
748 CaseTestModel.objects.update(
749 integer2=Case(
750 When(integer=1, then=1),
751 When(integer=2, then=2),
752 ),
753 )
754 self.assertQuerySetEqual(
755 CaseTestModel.objects.order_by("pk"),
756 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
757 transform=attrgetter("integer", "integer2"),
758 )
759
760 def test_update_with_expression_as_value(self):
761 CaseTestModel.objects.update(
762 integer=Case(
763 When(integer=1, then=F("integer") + 1),
764 When(integer=2, then=F("integer") + 3),
765 default="integer",
766 ),
767 )
768 self.assertQuerySetEqual(
769 CaseTestModel.objects.order_by("pk"),
770 [("1", 2), ("2", 5), ("3", 3), ("2", 5), ("3", 3), ("3", 3), ("4", 4)],
771 transform=attrgetter("string", "integer"),
772 )
773
774 def test_update_with_expression_as_condition(self):
775 CaseTestModel.objects.update(
776 string=Case(
777 When(integer2=F("integer"), then=Value("equal")),
778 When(integer2=F("integer") + 1, then=Value("+1")),
779 ),
780 )
781 self.assertQuerySetEqual(
782 CaseTestModel.objects.order_by("pk"),
783 [
784 (1, "equal"),
785 (2, "+1"),
786 (3, "+1"),
787 (2, "equal"),
788 (3, "+1"),
789 (3, "equal"),
790 (4, "+1"),
791 ],
792 transform=attrgetter("integer", "string"),
793 )
794
795 def test_update_with_join_in_condition_raise_field_error(self):
796 with self.assertRaisesMessage(
797 FieldError, "Joined field references are not permitted in this query"
798 ):
799 CaseTestModel.objects.update(
800 integer=Case(
801 When(integer2=F("o2o_rel__integer") + 1, then=2),
802 When(integer2=F("o2o_rel__integer"), then=3),
803 ),
804 )
805
806 def test_update_with_join_in_predicate_raise_field_error(self):
807 with self.assertRaisesMessage(
808 FieldError, "Joined field references are not permitted in this query"
809 ):
810 CaseTestModel.objects.update(
811 string=Case(
812 When(o2o_rel__integer=1, then=Value("one")),
813 When(o2o_rel__integer=2, then=Value("two")),
814 When(o2o_rel__integer=3, then=Value("three")),
815 default=Value("other"),
816 ),
817 )
818
819 def test_update_big_integer(self):
820 CaseTestModel.objects.update(
821 big_integer=Case(
822 When(integer=1, then=1),
823 When(integer=2, then=2),
824 ),
825 )
826 self.assertQuerySetEqual(
827 CaseTestModel.objects.order_by("pk"),
828 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
829 transform=attrgetter("integer", "big_integer"),
830 )
831
832 def test_update_binary(self):
833 CaseTestModel.objects.update(
834 binary=Case(
835 When(integer=1, then=b"one"),
836 When(integer=2, then=b"two"),
837 default=b"",
838 ),
839 )
840 self.assertQuerySetEqual(
841 CaseTestModel.objects.order_by("pk"),
842 [
843 (1, b"one"),
844 (2, b"two"),
845 (3, b""),
846 (2, b"two"),
847 (3, b""),
848 (3, b""),
849 (4, b""),
850 ],
851 transform=lambda o: (o.integer, bytes(o.binary)),
852 )
853
854 def test_update_boolean(self):
855 CaseTestModel.objects.update(
856 boolean=Case(
857 When(integer=1, then=True),
858 When(integer=2, then=True),
859 default=False,
860 ),
861 )
862 self.assertQuerySetEqual(
863 CaseTestModel.objects.order_by("pk"),
864 [
865 (1, True),
866 (2, True),
867 (3, False),
868 (2, True),
869 (3, False),
870 (3, False),
871 (4, False),
872 ],
873 transform=attrgetter("integer", "boolean"),
874 )
875
876 def test_update_date(self):
877 CaseTestModel.objects.update(
878 date=Case(
879 When(integer=1, then=date(2015, 1, 1)),
880 When(integer=2, then=date(2015, 1, 2)),
881 ),
882 )
883 self.assertQuerySetEqual(
884 CaseTestModel.objects.order_by("pk"),
885 [
886 (1, date(2015, 1, 1)),
887 (2, date(2015, 1, 2)),
888 (3, None),
889 (2, date(2015, 1, 2)),
890 (3, None),
891 (3, None),
892 (4, None),
893 ],
894 transform=attrgetter("integer", "date"),
895 )
896
897 def test_update_date_time(self):
898 CaseTestModel.objects.update(
899 date_time=Case(
900 When(integer=1, then=datetime(2015, 1, 1)),
901 When(integer=2, then=datetime(2015, 1, 2)),
902 ),
903 )
904 self.assertQuerySetEqual(
905 CaseTestModel.objects.order_by("pk"),
906 [
907 (1, datetime(2015, 1, 1)),
908 (2, datetime(2015, 1, 2)),
909 (3, None),
910 (2, datetime(2015, 1, 2)),
911 (3, None),
912 (3, None),
913 (4, None),
914 ],
915 transform=attrgetter("integer", "date_time"),
916 )
917
918 def test_update_decimal(self):
919 CaseTestModel.objects.update(
920 decimal=Case(
921 When(integer=1, then=Decimal("1.1")),
922 When(
923 integer=2, then=Value(Decimal("2.2"), output_field=DecimalField())
924 ),
925 ),
926 )
927 self.assertQuerySetEqual(
928 CaseTestModel.objects.order_by("pk"),
929 [
930 (1, Decimal("1.1")),
931 (2, Decimal("2.2")),
932 (3, None),
933 (2, Decimal("2.2")),
934 (3, None),
935 (3, None),
936 (4, None),
937 ],
938 transform=attrgetter("integer", "decimal"),
939 )
940
941 def test_update_duration(self):
942 CaseTestModel.objects.update(
943 duration=Case(
944 When(integer=1, then=timedelta(1)),
945 When(integer=2, then=timedelta(2)),
946 ),
947 )
948 self.assertQuerySetEqual(
949 CaseTestModel.objects.order_by("pk"),
950 [
951 (1, timedelta(1)),
952 (2, timedelta(2)),
953 (3, None),
954 (2, timedelta(2)),
955 (3, None),
956 (3, None),
957 (4, None),
958 ],
959 transform=attrgetter("integer", "duration"),
960 )
961
962 def test_update_email(self):
963 CaseTestModel.objects.update(
964 email=Case(
965 When(integer=1, then=Value("[email protected]")),
966 When(integer=2, then=Value("[email protected]")),
967 default=Value(""),
968 ),
969 )
970 self.assertQuerySetEqual(
971 CaseTestModel.objects.order_by("pk"),
972 [
973 (1, "[email protected]"),
974 (2, "[email protected]"),
975 (3, ""),
976 (2, "[email protected]"),
977 (3, ""),
978 (3, ""),
979 (4, ""),
980 ],
981 transform=attrgetter("integer", "email"),
982 )
983
984 def test_update_file(self):
985 CaseTestModel.objects.update(
986 file=Case(
987 When(integer=1, then=Value("~/1")),
988 When(integer=2, then=Value("~/2")),
989 ),
990 )
991 self.assertQuerySetEqual(
992 CaseTestModel.objects.order_by("pk"),
993 [(1, "~/1"), (2, "~/2"), (3, ""), (2, "~/2"), (3, ""), (3, ""), (4, "")],
994 transform=lambda o: (o.integer, str(o.file)),
995 )
996
997 def test_update_file_path(self):
998 CaseTestModel.objects.update(
999 file_path=Case(
1000 When(integer=1, then=Value("~/1")),
1001 When(integer=2, then=Value("~/2")),
1002 default=Value(""),
1003 ),
1004 )
1005 self.assertQuerySetEqual(
1006 CaseTestModel.objects.order_by("pk"),
1007 [(1, "~/1"), (2, "~/2"), (3, ""), (2, "~/2"), (3, ""), (3, ""), (4, "")],
1008 transform=attrgetter("integer", "file_path"),
1009 )
1010
1011 def test_update_float(self):
1012 CaseTestModel.objects.update(
1013 float=Case(
1014 When(integer=1, then=1.1),
1015 When(integer=2, then=2.2),
1016 ),
1017 )
1018 self.assertQuerySetEqual(
1019 CaseTestModel.objects.order_by("pk"),
1020 [(1, 1.1), (2, 2.2), (3, None), (2, 2.2), (3, None), (3, None), (4, None)],
1021 transform=attrgetter("integer", "float"),
1022 )
1023
1024 @unittest.skipUnless(Image, "Pillow not installed")
1025 def test_update_image(self):
1026 CaseTestModel.objects.update(
1027 image=Case(
1028 When(integer=1, then=Value("~/1")),
1029 When(integer=2, then=Value("~/2")),
1030 ),
1031 )
1032 self.assertQuerySetEqual(
1033 CaseTestModel.objects.order_by("pk"),
1034 [(1, "~/1"), (2, "~/2"), (3, ""), (2, "~/2"), (3, ""), (3, ""), (4, "")],
1035 transform=lambda o: (o.integer, str(o.image)),
1036 )
1037
1038 def test_update_generic_ip_address(self):
1039 CaseTestModel.objects.update(
1040 generic_ip_address=Case(
1041 When(integer=1, then=Value("1.1.1.1")),
1042 When(integer=2, then=Value("2.2.2.2")),
1043 output_field=GenericIPAddressField(),
1044 ),
1045 )
1046 self.assertQuerySetEqual(
1047 CaseTestModel.objects.order_by("pk"),
1048 [
1049 (1, "1.1.1.1"),
1050 (2, "2.2.2.2"),
1051 (3, None),
1052 (2, "2.2.2.2"),
1053 (3, None),
1054 (3, None),
1055 (4, None),
1056 ],
1057 transform=attrgetter("integer", "generic_ip_address"),
1058 )
1059
1060 def test_update_null_boolean(self):
1061 CaseTestModel.objects.update(
1062 null_boolean=Case(
1063 When(integer=1, then=True),
1064 When(integer=2, then=False),
1065 ),
1066 )
1067 self.assertQuerySetEqual(
1068 CaseTestModel.objects.order_by("pk"),
1069 [
1070 (1, True),
1071 (2, False),
1072 (3, None),
1073 (2, False),
1074 (3, None),
1075 (3, None),
1076 (4, None),
1077 ],
1078 transform=attrgetter("integer", "null_boolean"),
1079 )
1080
1081 def test_update_positive_big_integer(self):
1082 CaseTestModel.objects.update(
1083 positive_big_integer=Case(
1084 When(integer=1, then=1),
1085 When(integer=2, then=2),
1086 ),
1087 )
1088 self.assertQuerySetEqual(
1089 CaseTestModel.objects.order_by("pk"),
1090 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
1091 transform=attrgetter("integer", "positive_big_integer"),
1092 )
1093
1094 def test_update_positive_integer(self):
1095 CaseTestModel.objects.update(
1096 positive_integer=Case(
1097 When(integer=1, then=1),
1098 When(integer=2, then=2),
1099 ),
1100 )
1101 self.assertQuerySetEqual(
1102 CaseTestModel.objects.order_by("pk"),
1103 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
1104 transform=attrgetter("integer", "positive_integer"),
1105 )
1106
1107 def test_update_positive_small_integer(self):
1108 CaseTestModel.objects.update(
1109 positive_small_integer=Case(
1110 When(integer=1, then=1),
1111 When(integer=2, then=2),
1112 ),
1113 )
1114 self.assertQuerySetEqual(
1115 CaseTestModel.objects.order_by("pk"),
1116 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
1117 transform=attrgetter("integer", "positive_small_integer"),
1118 )
1119
1120 def test_update_slug(self):
1121 CaseTestModel.objects.update(
1122 slug=Case(
1123 When(integer=1, then=Value("1")),
1124 When(integer=2, then=Value("2")),
1125 default=Value(""),
1126 ),
1127 )
1128 self.assertQuerySetEqual(
1129 CaseTestModel.objects.order_by("pk"),
1130 [(1, "1"), (2, "2"), (3, ""), (2, "2"), (3, ""), (3, ""), (4, "")],
1131 transform=attrgetter("integer", "slug"),
1132 )
1133
1134 def test_update_small_integer(self):
1135 CaseTestModel.objects.update(
1136 small_integer=Case(
1137 When(integer=1, then=1),
1138 When(integer=2, then=2),
1139 ),
1140 )
1141 self.assertQuerySetEqual(
1142 CaseTestModel.objects.order_by("pk"),
1143 [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
1144 transform=attrgetter("integer", "small_integer"),
1145 )
1146
1147 def test_update_string(self):
1148 CaseTestModel.objects.filter(string__in=["1", "2"]).update(
1149 string=Case(
1150 When(integer=1, then=Value("1")),
1151 When(integer=2, then=Value("2")),
1152 ),
1153 )
1154 self.assertQuerySetEqual(
1155 CaseTestModel.objects.filter(string__in=["1", "2"]).order_by("pk"),
1156 [(1, "1"), (2, "2"), (2, "2")],
1157 transform=attrgetter("integer", "string"),
1158 )
1159
1160 def test_update_text(self):
1161 CaseTestModel.objects.update(
1162 text=Case(
1163 When(integer=1, then=Value("1")),
1164 When(integer=2, then=Value("2")),
1165 default=Value(""),
1166 ),
1167 )
1168 self.assertQuerySetEqual(
1169 CaseTestModel.objects.order_by("pk"),
1170 [(1, "1"), (2, "2"), (3, ""), (2, "2"), (3, ""), (3, ""), (4, "")],
1171 transform=attrgetter("integer", "text"),
1172 )
1173
1174 def test_update_time(self):
1175 CaseTestModel.objects.update(
1176 time=Case(
1177 When(integer=1, then=time(1)),
1178 When(integer=2, then=time(2)),
1179 ),
1180 )
1181 self.assertQuerySetEqual(
1182 CaseTestModel.objects.order_by("pk"),
1183 [
1184 (1, time(1)),
1185 (2, time(2)),
1186 (3, None),
1187 (2, time(2)),
1188 (3, None),
1189 (3, None),
1190 (4, None),
1191 ],
1192 transform=attrgetter("integer", "time"),
1193 )
1194
1195 def test_update_url(self):
1196 CaseTestModel.objects.update(
1197 url=Case(
1198 When(integer=1, then=Value("http://1.example.com/")),
1199 When(integer=2, then=Value("http://2.example.com/")),
1200 default=Value(""),
1201 ),
1202 )
1203 self.assertQuerySetEqual(
1204 CaseTestModel.objects.order_by("pk"),
1205 [
1206 (1, "http://1.example.com/"),
1207 (2, "http://2.example.com/"),
1208 (3, ""),
1209 (2, "http://2.example.com/"),
1210 (3, ""),
1211 (3, ""),
1212 (4, ""),
1213 ],
1214 transform=attrgetter("integer", "url"),
1215 )
1216
1217 def test_update_uuid(self):
1218 CaseTestModel.objects.update(
1219 uuid=Case(
1220 When(integer=1, then=UUID("11111111111111111111111111111111")),
1221 When(integer=2, then=UUID("22222222222222222222222222222222")),
1222 ),
1223 )
1224 self.assertQuerySetEqual(
1225 CaseTestModel.objects.order_by("pk"),
1226 [
1227 (1, UUID("11111111111111111111111111111111")),
1228 (2, UUID("22222222222222222222222222222222")),
1229 (3, None),
1230 (2, UUID("22222222222222222222222222222222")),
1231 (3, None),
1232 (3, None),
1233 (4, None),
1234 ],
1235 transform=attrgetter("integer", "uuid"),
1236 )
1237
1238 def test_update_fk(self):
1239 obj1, obj2 = CaseTestModel.objects.all()[:2]
1240
1241 CaseTestModel.objects.update(
1242 fk=Case(
1243 When(integer=1, then=obj1.pk),
1244 When(integer=2, then=obj2.pk),
1245 ),
1246 )
1247 self.assertQuerySetEqual(
1248 CaseTestModel.objects.order_by("pk"),
1249 [
1250 (1, obj1.pk),
1251 (2, obj2.pk),
1252 (3, None),
1253 (2, obj2.pk),
1254 (3, None),
1255 (3, None),
1256 (4, None),
1257 ],
1258 transform=attrgetter("integer", "fk_id"),
1259 )
1260
1261 def test_lookup_in_condition(self):
1262 self.assertQuerySetEqual(
1263 CaseTestModel.objects.annotate(
1264 test=Case(
1265 When(integer__lt=2, then=Value("less than 2")),
1266 When(integer__gt=2, then=Value("greater than 2")),
1267 default=Value("equal to 2"),
1268 ),
1269 ).order_by("pk"),
1270 [
1271 (1, "less than 2"),
1272 (2, "equal to 2"),
1273 (3, "greater than 2"),
1274 (2, "equal to 2"),
1275 (3, "greater than 2"),
1276 (3, "greater than 2"),
1277 (4, "greater than 2"),
1278 ],
1279 transform=attrgetter("integer", "test"),
1280 )
1281
1282 def test_lookup_different_fields(self):
1283 self.assertQuerySetEqual(
1284 CaseTestModel.objects.annotate(
1285 test=Case(
1286 When(integer=2, integer2=3, then=Value("when")),
1287 default=Value("default"),
1288 ),
1289 ).order_by("pk"),
1290 [
1291 (1, 1, "default"),
1292 (2, 3, "when"),
1293 (3, 4, "default"),
1294 (2, 2, "default"),
1295 (3, 4, "default"),
1296 (3, 3, "default"),
1297 (4, 5, "default"),
1298 ],
1299 transform=attrgetter("integer", "integer2", "test"),
1300 )
1301
1302 def test_combined_q_object(self):
1303 self.assertQuerySetEqual(
1304 CaseTestModel.objects.annotate(
1305 test=Case(
1306 When(Q(integer=2) | Q(integer2=3), then=Value("when")),
1307 default=Value("default"),
1308 ),
1309 ).order_by("pk"),
1310 [
1311 (1, 1, "default"),
1312 (2, 3, "when"),
1313 (3, 4, "default"),
1314 (2, 2, "when"),
1315 (3, 4, "default"),
1316 (3, 3, "when"),
1317 (4, 5, "default"),
1318 ],
1319 transform=attrgetter("integer", "integer2", "test"),
1320 )
1321
1322 def test_order_by_conditional_implicit(self):
1323 self.assertQuerySetEqual(
1324 CaseTestModel.objects.filter(integer__lte=2)
1325 .annotate(
1326 test=Case(
1327 When(integer=1, then=2),
1328 When(integer=2, then=1),
1329 default=3,
1330 )
1331 )
1332 .order_by("test", "pk"),
1333 [(2, 1), (2, 1), (1, 2)],
1334 transform=attrgetter("integer", "test"),
1335 )
1336
1337 def test_order_by_conditional_explicit(self):
1338 self.assertQuerySetEqual(
1339 CaseTestModel.objects.filter(integer__lte=2)
1340 .annotate(
1341 test=Case(
1342 When(integer=1, then=2),
1343 When(integer=2, then=1),
1344 default=3,
1345 )
1346 )
1347 .order_by(F("test").asc(), "pk"),
1348 [(2, 1), (2, 1), (1, 2)],
1349 transform=attrgetter("integer", "test"),
1350 )
1351
1352 def test_join_promotion(self):
1353 o = CaseTestModel.objects.create(integer=1, integer2=1, string="1")
1354 # Testing that:
1355 # 1. There isn't any object on the remote side of the fk_rel
1356 # relation. If the query used inner joins, then the join to fk_rel
1357 # would remove o from the results. So, in effect we are testing that
1358 # we are promoting the fk_rel join to a left outer join here.
1359 # 2. The default value of 3 is generated for the case expression.
1360 self.assertQuerySetEqual(
1361 CaseTestModel.objects.filter(pk=o.pk).annotate(
1362 foo=Case(
1363 When(fk_rel__pk=1, then=2),
1364 default=3,
1365 ),
1366 ),
1367 [(o, 3)],
1368 lambda x: (x, x.foo),
1369 )
1370 # Now 2 should be generated, as the fk_rel is null.
1371 self.assertQuerySetEqual(
1372 CaseTestModel.objects.filter(pk=o.pk).annotate(
1373 foo=Case(
1374 When(fk_rel__isnull=True, then=2),
1375 default=3,
1376 ),
1377 ),
1378 [(o, 2)],
1379 lambda x: (x, x.foo),
1380 )
1381
1382 def test_join_promotion_multiple_annotations(self):
1383 o = CaseTestModel.objects.create(integer=1, integer2=1, string="1")
1384 # Testing that:
1385 # 1. There isn't any object on the remote side of the fk_rel
1386 # relation. If the query used inner joins, then the join to fk_rel
1387 # would remove o from the results. So, in effect we are testing that
1388 # we are promoting the fk_rel join to a left outer join here.
1389 # 2. The default value of 3 is generated for the case expression.
1390 self.assertQuerySetEqual(
1391 CaseTestModel.objects.filter(pk=o.pk).annotate(
1392 foo=Case(
1393 When(fk_rel__pk=1, then=2),
1394 default=3,
1395 ),
1396 bar=Case(
1397 When(fk_rel__pk=1, then=4),
1398 default=5,
1399 ),
1400 ),
1401 [(o, 3, 5)],
1402 lambda x: (x, x.foo, x.bar),
1403 )
1404 # Now 2 should be generated, as the fk_rel is null.
1405 self.assertQuerySetEqual(
1406 CaseTestModel.objects.filter(pk=o.pk).annotate(
1407 foo=Case(
1408 When(fk_rel__isnull=True, then=2),
1409 default=3,
1410 ),
1411 bar=Case(
1412 When(fk_rel__isnull=True, then=4),
1413 default=5,
1414 ),
1415 ),
1416 [(o, 2, 4)],
1417 lambda x: (x, x.foo, x.bar),
1418 )
1419
1420 def test_m2m_exclude(self):
1421 CaseTestModel.objects.create(integer=10, integer2=1, string="1")
1422 qs = (
1423 CaseTestModel.objects.values_list("id", "integer")
1424 .annotate(
1425 cnt=Sum(
1426 Case(When(~Q(fk_rel__integer=1), then=1), default=2),
1427 ),
1428 )
1429 .order_by("integer")
1430 )
1431 # The first o has 2 as its fk_rel__integer=1, thus it hits the
1432 # default=2 case. The other ones have 2 as the result as they have 2
1433 # fk_rel objects, except for integer=4 and integer=10 (created above).
1434 # The integer=4 case has one integer, thus the result is 1, and
1435 # integer=10 doesn't have any and this too generates 1 (instead of 0)
1436 # as ~Q() also matches nulls.
1437 self.assertQuerySetEqual(
1438 qs,
1439 [(1, 2), (2, 2), (2, 2), (3, 2), (3, 2), (3, 2), (4, 1), (10, 1)],
1440 lambda x: x[1:],
1441 )
1442
1443 def test_m2m_reuse(self):
1444 CaseTestModel.objects.create(integer=10, integer2=1, string="1")
1445 # Need to use values before annotate so that Oracle will not group
1446 # by fields it isn't capable of grouping by.
1447 qs = (
1448 CaseTestModel.objects.values_list("id", "integer")
1449 .annotate(
1450 cnt=Sum(
1451 Case(When(~Q(fk_rel__integer=1), then=1), default=2),
1452 ),
1453 )
1454 .annotate(
1455 cnt2=Sum(
1456 Case(When(~Q(fk_rel__integer=1), then=1), default=2),
1457 ),
1458 )
1459 .order_by("integer")
1460 )
1461 self.assertEqual(str(qs.query).count(" JOIN "), 1)
1462 self.assertQuerySetEqual(
1463 qs,
1464 [
1465 (1, 2, 2),
1466 (2, 2, 2),
1467 (2, 2, 2),
1468 (3, 2, 2),
1469 (3, 2, 2),
1470 (3, 2, 2),
1471 (4, 1, 1),
1472 (10, 1, 1),
1473 ],
1474 lambda x: x[1:],
1475 )
1476
1477 def test_aggregation_empty_cases(self):
1478 tests = [
1479 # Empty cases and default.
1480 (Case(output_field=IntegerField()), None),
1481 # Empty cases and a constant default.
1482 (Case(default=Value("empty")), "empty"),
1483 # Empty cases and column in the default.
1484 (Case(default=F("url")), ""),
1485 ]
1486 for case, value in tests:
1487 with self.subTest(case=case):
1488 self.assertQuerySetEqual(
1489 CaseTestModel.objects.values("string")
1490 .annotate(
1491 case=case,
1492 integer_sum=Sum("integer"),
1493 )
1494 .order_by("string"),
1495 [
1496 ("1", value, 1),
1497 ("2", value, 4),
1498 ("3", value, 9),
1499 ("4", value, 4),
1500 ],
1501 transform=itemgetter("string", "case", "integer_sum"),
1502 )
1503
1504
1505 class CaseDocumentationExamples(TestCase):
1506 @classmethod
1507 def setUpTestData(cls):
1508 Client.objects.create(
1509 name="Jane Doe",
1510 account_type=Client.REGULAR,
1511 registered_on=date.today() - timedelta(days=36),
1512 )
1513 Client.objects.create(
1514 name="James Smith",
1515 account_type=Client.GOLD,
1516 registered_on=date.today() - timedelta(days=5),
1517 )
1518 Client.objects.create(
1519 name="Jack Black",
1520 account_type=Client.PLATINUM,
1521 registered_on=date.today() - timedelta(days=10 * 365),
1522 )
1523
1524 def test_simple_example(self):
1525 self.assertQuerySetEqual(
1526 Client.objects.annotate(
1527 discount=Case(
1528 When(account_type=Client.GOLD, then=Value("5%")),
1529 When(account_type=Client.PLATINUM, then=Value("10%")),
1530 default=Value("0%"),
1531 ),
1532 ).order_by("pk"),
1533 [("Jane Doe", "0%"), ("James Smith", "5%"), ("Jack Black", "10%")],
1534 transform=attrgetter("name", "discount"),
1535 )
1536
1537 def test_lookup_example(self):
1538 a_month_ago = date.today() - timedelta(days=30)
1539 a_year_ago = date.today() - timedelta(days=365)
1540 self.assertQuerySetEqual(
1541 Client.objects.annotate(
1542 discount=Case(
1543 When(registered_on__lte=a_year_ago, then=Value("10%")),
1544 When(registered_on__lte=a_month_ago, then=Value("5%")),
1545 default=Value("0%"),
1546 ),
1547 ).order_by("pk"),
1548 [("Jane Doe", "5%"), ("James Smith", "0%"), ("Jack Black", "10%")],
1549 transform=attrgetter("name", "discount"),
1550 )
1551
1552 def test_conditional_update_example(self):
1553 a_month_ago = date.today() - timedelta(days=30)
1554 a_year_ago = date.today() - timedelta(days=365)
1555 Client.objects.update(
1556 account_type=Case(
1557 When(registered_on__lte=a_year_ago, then=Value(Client.PLATINUM)),
1558 When(registered_on__lte=a_month_ago, then=Value(Client.GOLD)),
1559 default=Value(Client.REGULAR),
1560 ),
1561 )
1562 self.assertQuerySetEqual(
1563 Client.objects.order_by("pk"),
1564 [("Jane Doe", "G"), ("James Smith", "R"), ("Jack Black", "P")],
1565 transform=attrgetter("name", "account_type"),
1566 )
1567
1568 def test_conditional_aggregation_example(self):
1569 Client.objects.create(
1570 name="Jean Grey",
1571 account_type=Client.REGULAR,
1572 registered_on=date.today(),
1573 )
1574 Client.objects.create(
1575 name="James Bond",
1576 account_type=Client.PLATINUM,
1577 registered_on=date.today(),
1578 )
1579 Client.objects.create(
1580 name="Jane Porter",
1581 account_type=Client.PLATINUM,
1582 registered_on=date.today(),
1583 )
1584 self.assertEqual(
1585 Client.objects.aggregate(
1586 regular=Count("pk", filter=Q(account_type=Client.REGULAR)),
1587 gold=Count("pk", filter=Q(account_type=Client.GOLD)),
1588 platinum=Count("pk", filter=Q(account_type=Client.PLATINUM)),
1589 ),
1590 {"regular": 2, "gold": 1, "platinum": 3},
1591 )
1592 # This was the example before the filter argument was added.
1593 self.assertEqual(
1594 Client.objects.aggregate(
1595 regular=Sum(
1596 Case(
1597 When(account_type=Client.REGULAR, then=1),
1598 )
1599 ),
1600 gold=Sum(
1601 Case(
1602 When(account_type=Client.GOLD, then=1),
1603 )
1604 ),
1605 platinum=Sum(
1606 Case(
1607 When(account_type=Client.PLATINUM, then=1),
1608 )
1609 ),
1610 ),
1611 {"regular": 2, "gold": 1, "platinum": 3},
1612 )
1613
1614 def test_filter_example(self):
1615 a_month_ago = date.today() - timedelta(days=30)
1616 a_year_ago = date.today() - timedelta(days=365)
1617 self.assertQuerySetEqual(
1618 Client.objects.filter(
1619 registered_on__lte=Case(
1620 When(account_type=Client.GOLD, then=a_month_ago),
1621 When(account_type=Client.PLATINUM, then=a_year_ago),
1622 ),
1623 ),
1624 [("Jack Black", "P")],
1625 transform=attrgetter("name", "account_type"),
1626 )
1627
1628 def test_hash(self):
1629 expression_1 = Case(
1630 When(account_type__in=[Client.REGULAR, Client.GOLD], then=1),
1631 default=2,
1632 output_field=IntegerField(),
1633 )
1634 expression_2 = Case(
1635 When(account_type__in=(Client.REGULAR, Client.GOLD), then=1),
1636 default=2,
1637 output_field=IntegerField(),
1638 )
1639 expression_3 = Case(
1640 When(account_type__in=[Client.REGULAR, Client.GOLD], then=1), default=2
1641 )
1642 expression_4 = Case(
1643 When(account_type__in=[Client.PLATINUM, Client.GOLD], then=2), default=1
1644 )
1645 self.assertEqual(hash(expression_1), hash(expression_2))
1646 self.assertNotEqual(hash(expression_2), hash(expression_3))
1647 self.assertNotEqual(hash(expression_1), hash(expression_4))
1648 self.assertNotEqual(hash(expression_3), hash(expression_4))
1649
1650
1651 class CaseWhenTests(SimpleTestCase):
1652 def test_only_when_arguments(self):
1653 msg = "Positional arguments must all be When objects."
1654 with self.assertRaisesMessage(TypeError, msg):
1655 Case(When(Q(pk__in=[])), object())
1656
1657 def test_invalid_when_constructor_args(self):
1658 msg = (
1659 "When() supports a Q object, a boolean expression, or lookups as "
1660 "a condition."
1661 )
1662 with self.assertRaisesMessage(TypeError, msg):
1663 When(condition=object())
1664 with self.assertRaisesMessage(TypeError, msg):
1665 When(condition=Value(1))
1666 with self.assertRaisesMessage(TypeError, msg):
1667 When(Value(1), string="1")
1668 with self.assertRaisesMessage(TypeError, msg):
1669 When()
1670
1671 def test_empty_q_object(self):
1672 msg = "An empty Q() can't be used as a When() condition."
1673 with self.assertRaisesMessage(ValueError, msg):
1674 When(Q(), then=Value(True))