def test_filter_without_default(self):
self.assertQuerysetEqual(
CaseTestModel.objects.filter(integer2=Case(
When(integer=2, then=3),
When(integer=3, then=4),
output_field=models.IntegerField(),
)).order_by('pk'),
[(2, 3), (3, 4), (3, 4)],
transform=attrgetter('integer', 'integer2')
)
def test_annotate_with_annotation_in_condition(self):
self.assertQuerysetEqual(CaseTestModel.objects.annotate(
f_plus_1=F('integer') + 1, ).annotate(f_test=Case(
When(integer2=F('integer'), then=Value('equal')),
When(integer2=F('f_plus_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_filter_with_aggregation_in_condition(self):
self.assertQuerysetEqual(
CaseTestModel.objects.values(*self.non_lob_fields).annotate(
min=Min('fk_rel__integer'),
max=Max('fk_rel__integer'),
).filter(integer=Case(
When(integer2=F('min'), then=2),
When(integer2=F('max'), then=3),
), ).order_by('pk'), [(3, 4, 3, 4), (2, 2, 2, 3), (3, 4, 3, 4)],
transform=itemgetter('integer', 'integer2', 'min', 'max'))
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_annotate_with_join_in_predicate(self):
self.assertQuerysetEqual(CaseTestModel.objects.annotate(join_test=Case(
When(o2o_rel__integer=1, then=Value('one')),
When(o2o_rel__integer=2, then=Value('two')),
When(o2o_rel__integer=3, then=Value('three')),
default=Value('other'),
output_field=models.CharField(),
)).order_by('pk'), [(1, 'one'), (2, 'two'), (3, 'three'), (2, 'two'),
(3, 'three'), (3, 'three'), (4, 'one')],
transform=attrgetter('integer', 'join_test'))
def test_update_boolean(self):
CaseTestModel.objects.update(boolean=Case(
When(integer=1, then=True),
When(integer=2, then=True),
default=False,
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[(1, True), (2, True), (3, False), (2, True),
(3, False), (3, False), (4, False)],
transform=attrgetter('integer', 'boolean'))
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_date(self):
CaseTestModel.objects.update(date=Case(
When(integer=1, then=date(2015, 1, 1)),
When(integer=2, then=date(2015, 1, 2)),
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[(1, date(2015, 1, 1)), (2, date(2015, 1, 2)),
(3, None), (2, date(2015, 1, 2)), (3, None),
(3, None), (4, None)],
transform=attrgetter('integer', 'date'))
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_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_filter_with_join_in_condition(self):
self.assertQuerysetEqual(
CaseTestModel.objects.filter(integer=Case(
When(integer2=F('o2o_rel__integer') + 1, then=2),
When(integer2=F('o2o_rel__integer'), then=3),
output_field=models.IntegerField(),
)).order_by('pk'),
[(2, 3), (3, 3)],
transform=attrgetter('integer', 'integer2')
)
def test_filter_with_join_in_value(self):
self.assertQuerysetEqual(
CaseTestModel.objects.filter(integer2=Case(
When(integer=2, then=F('o2o_rel__integer') + 1),
When(integer=3, then=F('o2o_rel__integer')),
default='o2o_rel__integer',
)).order_by('pk'),
[(1, 1), (2, 3), (3, 3)],
transform=attrgetter('integer', 'integer2')
)
def test_filter_with_expression_as_condition(self):
self.assertQuerysetEqual(
CaseTestModel.objects.filter(string=Case(
When(integer2=F('integer'), then=Value('2')),
When(integer2=F('integer') + 1, then=Value('3')),
output_field=models.CharField(),
)).order_by('pk'),
[(3, 4, '3'), (2, 2, '2'), (3, 4, '3')],
transform=attrgetter('integer', 'integer2', 'string')
)
def test_update_with_expression_as_condition(self):
CaseTestModel.objects.update(string=Case(
When(integer2=F('integer'), then=Value('equal')),
When(integer2=F('integer') + 1, then=Value('+1')),
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[(1, 'equal'), (2, '+1'), (3, '+1'),
(2, 'equal'), (3, '+1'), (3, 'equal'),
(4, '+1')],
transform=attrgetter('integer', 'string'))
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_big_integer(self):
CaseTestModel.objects.update(big_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',
'big_integer'))
def test_update_with_expression_as_value(self):
CaseTestModel.objects.update(integer=Case(
When(integer=1, then=F('integer') + 1),
When(integer=2, then=F('integer') + 3),
default='integer',
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[('1', 2), ('2', 5), ('3', 3), ('2', 5),
('3', 3), ('3', 3), ('4', 4)],
transform=attrgetter('string', 'integer'))
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_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_lookup_example(self):
a_month_ago = date.today() - timedelta(days=30)
a_year_ago = date.today() - timedelta(days=365)
self.assertQuerysetEqual(Client.objects.annotate(discount=Case(
When(registered_on__lte=a_year_ago, then=Value('10%')),
When(registered_on__lte=a_month_ago, then=Value('5%')),
default=Value('0%'),
output_field=models.CharField(),
), ).order_by('pk'), [('Jane Doe', '5%'), ('James Smith', '0%'),
('Jack Black', '10%')],
transform=attrgetter('name', 'discount'))
def test_update_with_join_in_predicate_raise_field_error(self):
with self.assertRaisesMessage(
FieldError,
'Joined field references are not permitted in this query'):
CaseTestModel.objects.update(string=Case(
When(o2o_rel__integer=1, then=Value('one')),
When(o2o_rel__integer=2, then=Value('two')),
When(o2o_rel__integer=3, then=Value('three')),
default=Value('other'),
output_field=models.CharField(),
), )
def test_update_email(self):
CaseTestModel.objects.update(email=Case(
When(integer=1, then=Value('*****@*****.**')),
When(integer=2, then=Value('*****@*****.**')),
default=Value(''),
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[(1, '*****@*****.**'), (2, '*****@*****.**'),
(3, ''), (2, '*****@*****.**'), (3, ''),
(3, ''), (4, '')],
transform=attrgetter('integer', 'email'))
def test_aggregate_with_expression_as_value(self):
self.assertEqual(
CaseTestModel.objects.aggregate(
one=models.Sum(Case(When(integer=1, then='integer'))),
two=models.Sum(Case(When(integer=2, then=F('integer') - 1))),
three=models.Sum(Case(When(integer=3, then=F('integer') + 1))),
), {
'one': 1,
'two': 2,
'three': 12
})
def test_update(self):
CaseTestModel.objects.update(string=Case(
When(integer=1, then=Value('one')),
When(integer=2, then=Value('two')),
default=Value('other'),
), )
self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'),
[(1, 'one'), (2, 'two'), (3, 'other'),
(2, 'two'), (3, 'other'), (3, 'other'),
(4, 'other')],
transform=attrgetter('integer', 'string'))
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_filter_with_join_in_predicate(self):
self.assertQuerysetEqual(
CaseTestModel.objects.filter(integer2=Case(
When(o2o_rel__integer=1, then=Value(1)),
When(o2o_rel__integer=2, then=Value(3)),
When(o2o_rel__integer=3, then=Value(4)),
output_field=models.IntegerField(),
)).order_by('pk'),
[(1, 1), (2, 3), (3, 4), (3, 4)],
transform=attrgetter('integer', 'integer2')
)
def test_annotate_values_not_in_order_by(self):
self.assertEqual(
list(CaseTestModel.objects.annotate(test=Case(
When(integer=1, then=Value('one')),
When(integer=2, then=Value('two')),
When(integer=3, then=Value('three')),
default=Value('other'),
output_field=models.CharField(),
)).order_by('test').values_list('integer', flat=True)),
[1, 4, 3, 3, 3, 2, 2]
)
def test_annotate_exclude(self):
self.assertQuerysetEqual(
CaseTestModel.objects.annotate(test=Case(
When(integer=1, then=Value('one')),
When(integer=2, then=Value('two')),
default=Value('other'),
output_field=models.CharField(),
)).exclude(test='other').order_by('pk'),
[(1, 'one'), (2, 'two'), (2, 'two')],
transform=attrgetter('integer', 'test')
)
def test_order_by_conditional_explicit(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(F('test').asc(), 'pk'),
[(2, 1), (2, 1), (1, 2)],
transform=attrgetter('integer', 'test')
)
