Python django.db.models.expressions.When() Examples

def test_lookup_in_condition(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(
                test=Case(
                    When(integer__lt=2, then=Value('less than 2')),
                    When(integer__gt=2, then=Value('greater than 2')),
                    default=Value('equal to 2'),
                    output_field=models.CharField(),
                ),
            ).order_by('pk'),
            [
                (1, 'less than 2'), (2, 'equal to 2'), (3, 'greater than 2'), (2, 'equal to 2'), (3, 'greater than 2'),
                (3, 'greater than 2'), (4, 'greater than 2')
            ],
            transform=attrgetter('integer', 'test')
        ) 

def test_m2m_exclude(self):
        CaseTestModel.objects.create(integer=10, integer2=1, string='1')
        qs = CaseTestModel.objects.values_list('id', 'integer').annotate(
            cnt=models.Sum(
                Case(When(~Q(fk_rel__integer=1), then=1), default=2),
                output_field=models.IntegerField()
            ),
        ).order_by('integer')
        # The first o has 2 as its fk_rel__integer=1, thus it hits the
        # default=2 case. The other ones have 2 as the result as they have 2
        # fk_rel objects, except for integer=4 and integer=10 (created above).
        # The integer=4 case has one integer, thus the result is 1, and
        # integer=10 doesn't have any and this too generates 1 (instead of 0)
        # as ~Q() also matches nulls.
        self.assertQuerysetEqual(
            qs,
            [(1, 2), (2, 2), (2, 2), (3, 2), (3, 2), (3, 2), (4, 1), (10, 1)],
            lambda x: x[1:]
        ) 

def test_m2m_reuse(self):
        CaseTestModel.objects.create(integer=10, integer2=1, string='1')
        # Need to use values before annotate so that Oracle will not group
        # by fields it isn't capable of grouping by.
        qs = CaseTestModel.objects.values_list('id', 'integer').annotate(
            cnt=models.Sum(
                Case(When(~Q(fk_rel__integer=1), then=1), default=2),
                output_field=models.IntegerField()
            ),
        ).annotate(
            cnt2=models.Sum(
                Case(When(~Q(fk_rel__integer=1), then=1), default=2),
                output_field=models.IntegerField()
            ),
        ).order_by('integer')
        self.assertEqual(str(qs.query).count(' JOIN '), 1)
        self.assertQuerysetEqual(
            qs,
            [(1, 2, 2), (2, 2, 2), (2, 2, 2), (3, 2, 2), (3, 2, 2), (3, 2, 2), (4, 1, 1), (10, 1, 1)],
            lambda x: x[1:]
        ) 

def test_annotate_with_aggregation_in_predicate(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.values(*self.non_lob_fields).annotate(
                max=Max('fk_rel__integer'),
            ).annotate(
                test=Case(
                    When(max=3, then=Value('max = 3')),
                    When(max=4, then=Value('max = 4')),
                    default=Value(''),
                    output_field=models.CharField(),
                ),
            ).order_by('pk'),
            [(1, 1, ''), (2, 3, 'max = 3'), (3, 4, 'max = 4'), (2, 3, 'max = 3'),
             (3, 4, 'max = 4'), (3, 4, 'max = 4'), (4, 5, '')],
            transform=itemgetter('integer', 'max', 'test')
        ) 

def test_aggregate(self):
        self.assertEqual(
            CaseTestModel.objects.aggregate(
                one=models.Sum(Case(
                    When(integer=1, then=1),
                    output_field=models.IntegerField(),
                )),
                two=models.Sum(Case(
                    When(integer=2, then=1),
                    output_field=models.IntegerField(),
                )),
                three=models.Sum(Case(
                    When(integer=3, then=1),
                    output_field=models.IntegerField(),
                )),
                four=models.Sum(Case(
                    When(integer=4, then=1),
                    output_field=models.IntegerField(),
                )),
            ),
            {'one': 1, 'two': 2, 'three': 3, 'four': 1}
        ) 

def test_annotate_with_annotation_in_predicate(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(
                f_minus_2=F('integer') - 2,
            ).annotate(
                test=Case(
                    When(f_minus_2=-1, then=Value('negative one')),
                    When(f_minus_2=0, then=Value('zero')),
                    When(f_minus_2=1, then=Value('one')),
                    default=Value('other'),
                    output_field=models.CharField(),
                ),
            ).order_by('pk'),
            [(1, 'negative one'), (2, 'zero'), (3, 'one'), (2, 'zero'), (3, 'one'), (3, 'one'), (4, 'other')],
            transform=attrgetter('integer', 'test')
        ) 

def test_update_url(self):
        CaseTestModel.objects.update(
            url=Case(
                When(integer=1, then=Value('http://1.example.com/')),
                When(integer=2, then=Value('http://2.example.com/')),
                default=Value(''),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [
                (1, 'http://1.example.com/'), (2, 'http://2.example.com/'), (3, ''), (2, 'http://2.example.com/'),
                (3, ''), (3, ''), (4, '')
            ],
            transform=attrgetter('integer', 'url')
        ) 

def as_sql(self, compiler, connection, **extra_context):
        if self.filter:
            if connection.features.supports_aggregate_filter_clause:
                filter_sql, filter_params = self.filter.as_sql(compiler, connection)
                template = self.filter_template % extra_context.get('template', self.template)
                sql, params = super().as_sql(compiler, connection, template=template, filter=filter_sql)
                return sql, params + filter_params
            else:
                copy = self.copy()
                copy.filter = None
                condition = When(Q())
                source_expressions = copy.get_source_expressions()
                condition.set_source_expressions([self.filter, source_expressions[0]])
                copy.set_source_expressions([Case(condition)] + source_expressions[1:])
                return super(Aggregate, copy).as_sql(compiler, connection, **extra_context)
        return super().as_sql(compiler, connection, **extra_context) 

def test_update_decimal(self):
        CaseTestModel.objects.update(
            decimal=Case(
                When(integer=1, then=Decimal('1.1')),
                When(integer=2, then=Decimal('2.2')),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [
                (1, Decimal('1.1')),
                (2, Decimal('2.2')),
                (3, None),
                (2, Decimal('2.2')),
                (3, None),
                (3, None),
                (4, None)
            ],
            transform=attrgetter('integer', 'decimal')
        ) 

def test_annotate_without_default(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(test=Case(
                When(integer=1, then=1),
                When(integer=2, then=2),
                output_field=models.IntegerField(),
            )).order_by('pk'),
            [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'test')
        ) 

def test_annotate_with_join_in_condition(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(join_test=Case(
                When(integer2=F('o2o_rel__integer'), then=Value('equal')),
                When(integer2=F('o2o_rel__integer') + 1, then=Value('+1')),
                default=Value('other'),
                output_field=models.CharField(),
            )).order_by('pk'),
            [(1, 'equal'), (2, '+1'), (3, '+1'), (2, 'equal'), (3, '+1'), (3, 'equal'), (4, 'other')],
            transform=attrgetter('integer', 'join_test')
        ) 

def test_annotate_with_annotation_in_value(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(
                f_plus_1=F('integer') + 1,
                f_plus_3=F('integer') + 3,
            ).annotate(
                f_test=Case(
                    When(integer=1, then='f_plus_1'),
                    When(integer=2, then='f_plus_3'),
                    default='integer',
                ),
            ).order_by('pk'),
            [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 4)],
            transform=attrgetter('integer', 'f_test')
        ) 

def test_update_fk(self):
        obj1, obj2 = CaseTestModel.objects.all()[:2]

        CaseTestModel.objects.update(
            fk=Case(
                When(integer=1, then=obj1.pk),
                When(integer=2, then=obj2.pk),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, obj1.pk), (2, obj2.pk), (3, None), (2, obj2.pk), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'fk_id')
        ) 

def test_annotate_with_expression_as_value(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(f_test=Case(
                When(integer=1, then=F('integer') + 1),
                When(integer=2, then=F('integer') + 3),
                default='integer',
            )).order_by('pk'),
            [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 4)],
            transform=attrgetter('integer', 'f_test')
        ) 

def test_annotate_with_expression_as_condition(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(f_test=Case(
                When(integer2=F('integer'), then=Value('equal')),
                When(integer2=F('integer') + 1, then=Value('+1')),
                output_field=models.CharField(),
            )).order_by('pk'),
            [(1, 'equal'), (2, '+1'), (3, '+1'), (2, 'equal'), (3, '+1'), (3, 'equal'), (4, '+1')],
            transform=attrgetter('integer', 'f_test')
        ) 

def test_annotate_with_join_in_value(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.annotate(join_test=Case(
                When(integer=1, then=F('o2o_rel__integer') + 1),
                When(integer=2, then=F('o2o_rel__integer') + 3),
                default='o2o_rel__integer',
            )).order_by('pk'),
            [(1, 2), (2, 5), (3, 3), (2, 5), (3, 3), (3, 3), (4, 1)],
            transform=attrgetter('integer', 'join_test')
        ) 

def test_order_by_conditional_implicit(self):
        self.assertQuerysetEqual(
            CaseTestModel.objects.filter(integer__lte=2).annotate(test=Case(
                When(integer=1, then=2),
                When(integer=2, then=1),
                default=3,
                output_field=models.IntegerField(),
            )).order_by('test', 'pk'),
            [(2, 1), (2, 1), (1, 2)],
            transform=attrgetter('integer', 'test')
        ) 

def test_update_time(self):
        CaseTestModel.objects.update(
            time=Case(
                # fails on sqlite if output_field is not set explicitly on all
                # Values containing times
                When(integer=1, then=Value(time(1), output_field=models.TimeField())),
                When(integer=2, then=Value(time(2), output_field=models.TimeField())),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, time(1)), (2, time(2)), (3, None), (2, time(2)), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'time')
        ) 

def test_update_text(self):
        CaseTestModel.objects.update(
            text=Case(
                When(integer=1, then=Value('1')),
                When(integer=2, then=Value('2')),
                default=Value(''),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '1'), (2, '2'), (3, ''), (2, '2'), (3, ''), (3, ''), (4, '')],
            transform=attrgetter('integer', 'text')
        ) 

def test_update_string(self):
        CaseTestModel.objects.filter(string__in=['1', '2']).update(
            string=Case(
                When(integer=1, then=Value('1', output_field=models.CharField())),
                When(integer=2, then=Value('2', output_field=models.CharField())),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.filter(string__in=['1', '2']).order_by('pk'),
            [(1, '1'), (2, '2'), (2, '2')],
            transform=attrgetter('integer', 'string')
        ) 

def test_update_small_integer(self):
        CaseTestModel.objects.update(
            small_integer=Case(
                When(integer=1, then=1),
                When(integer=2, then=2),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'small_integer')
        ) 

def test_update_positive_small_integer(self):
        CaseTestModel.objects.update(
            positive_small_integer=Case(
                When(integer=1, then=1),
                When(integer=2, then=2),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'positive_small_integer')
        ) 

def test_update_positive_integer(self):
        CaseTestModel.objects.update(
            positive_integer=Case(
                When(integer=1, then=1),
                When(integer=2, then=2),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, 1), (2, 2), (3, None), (2, 2), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'positive_integer')
        ) 

def test_update_null_boolean(self):
        CaseTestModel.objects.update(
            null_boolean=Case(
                When(integer=1, then=True),
                When(integer=2, then=False),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, True), (2, False), (3, None), (2, False), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'null_boolean')
        ) 

def test_update_generic_ip_address(self):
        CaseTestModel.objects.update(
            generic_ip_address=Case(
                # fails on postgresql if output_field is not set explicitly
                When(integer=1, then=Value('1.1.1.1')),
                When(integer=2, then=Value('2.2.2.2')),
                output_field=models.GenericIPAddressField(),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '1.1.1.1'), (2, '2.2.2.2'), (3, None), (2, '2.2.2.2'), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'generic_ip_address')
        ) 

def test_update_image(self):
        CaseTestModel.objects.update(
            image=Case(
                When(integer=1, then=Value('~/1')),
                When(integer=2, then=Value('~/2')),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '~/1'), (2, '~/2'), (3, ''), (2, '~/2'), (3, ''), (3, ''), (4, '')],
            transform=lambda o: (o.integer, six.text_type(o.image))
        ) 

def test_update_file_path(self):
        CaseTestModel.objects.update(
            file_path=Case(
                When(integer=1, then=Value('~/1')),
                When(integer=2, then=Value('~/2')),
                default=Value(''),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '~/1'), (2, '~/2'), (3, ''), (2, '~/2'), (3, ''), (3, ''), (4, '')],
            transform=attrgetter('integer', 'file_path')
        ) 

def test_update_file(self):
        CaseTestModel.objects.update(
            file=Case(
                When(integer=1, then=Value('~/1')),
                When(integer=2, then=Value('~/2')),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '~/1'), (2, '~/2'), (3, ''), (2, '~/2'), (3, ''), (3, ''), (4, '')],
            transform=lambda o: (o.integer, six.text_type(o.file))
        ) 

def test_update_email(self):
        CaseTestModel.objects.update(
            email=Case(
                When(integer=1, then=Value('1@example.com')),
                When(integer=2, then=Value('2@example.com')),
                default=Value(''),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, '1@example.com'), (2, '2@example.com'), (3, ''), (2, '2@example.com'), (3, ''), (3, ''), (4, '')],
            transform=attrgetter('integer', 'email')
        ) 

def test_update_duration(self):
        CaseTestModel.objects.update(
            duration=Case(
                # fails on sqlite if output_field is not set explicitly on all
                # Values containing timedeltas
                When(integer=1, then=Value(timedelta(1), output_field=models.DurationField())),
                When(integer=2, then=Value(timedelta(2), output_field=models.DurationField())),
            ),
        )
        self.assertQuerysetEqual(
            CaseTestModel.objects.all().order_by('pk'),
            [(1, timedelta(1)), (2, timedelta(2)), (3, None), (2, timedelta(2)), (3, None), (3, None), (4, None)],
            transform=attrgetter('integer', 'duration')
        )