def test_update(self):
Author.objects.update(name=Replace(F('name'), Value('R. R. '),
Value('')), )
self.assertQuerysetEqual(Author.objects.all(), [
('George Martin'),
('J. Tolkien'),
],
transform=lambda x: x.name,
ordered=False)
def test_case_sensitive(self):
qs = Author.objects.annotate(
same_name=Replace(F('name'), Value('r. r.'), Value('')))
self.assertQuerysetEqual(qs, [
('George R. R. Martin', 'George R. R. Martin'),
('J. R. R. Tolkien', 'J. R. R. Tolkien'),
],
transform=lambda x: (x.name, x.same_name),
ordered=False)
def test_union_with_two_annotated_values_list(self):
qs1 = Number.objects.filter(num=1).annotate(
count=Value(0, IntegerField()),
).values_list('num', 'count')
qs2 = Number.objects.filter(num=2).values('pk').annotate(
count=F('num'),
).annotate(
num=Value(1, IntegerField()),
).values_list('num', 'count')
self.assertCountEqual(qs1.union(qs2), [(1, 0), (2, 1)])
def test_replace_expression(self):
qs = Author.objects.annotate(same_name=Replace(
Concat(Value('Author: '), F('name')), Value('Author: '),
Value('')), )
self.assertQuerysetEqual(qs, [
('George R. R. Martin', 'George R. R. Martin'),
('J. R. R. Tolkien', 'J. R. R. Tolkien'),
],
transform=lambda x: (x.name, x.same_name),
ordered=False)
def test_nonfield_annotation(self):
book = Book.objects.annotate(
val=Max(Value(2, output_field=IntegerField()))).first()
self.assertEqual(book.val, 2)
book = Book.objects.annotate(
val=Max(Value(2), output_field=IntegerField())).first()
self.assertEqual(book.val, 2)
book = Book.objects.annotate(
val=Max(2, output_field=IntegerField())).first()
self.assertEqual(book.val, 2)
def test_replace_with_empty_string(self):
qs = Author.objects.annotate(without_middlename=Replace(
F('name'), Value('R. R. '), Value('')), )
self.assertQuerysetEqual(qs, [
('George R. R. Martin', 'George Martin'),
('J. R. R. Tolkien', 'J. Tolkien'),
],
transform=lambda x:
(x.name, x.without_middlename),
ordered=False)
def test_get_bound(self):
look_up = YearComparisonLookup(
lhs=Value(datetime(2010, 1, 1, 0, 0, 0),
output_field=DateTimeField()),
rhs=Value(datetime(2010, 1, 1, 23, 59, 59),
output_field=DateTimeField()),
)
msg = 'subclasses of YearComparisonLookup must provide a get_bound() method'
with self.assertRaisesMessage(NotImplementedError, msg):
look_up.get_bound(datetime(2010, 1, 1, 0, 0, 0),
datetime(2010, 1, 1, 23, 59, 59))
def test_boolean_value_annotation(self):
books = Book.objects.annotate(
is_book=Value(True, output_field=BooleanField()),
is_pony=Value(False, output_field=BooleanField()),
is_none=Value(None, output_field=BooleanField(null=True)),
is_none_old=Value(None, output_field=NullBooleanField()),
)
self.assertGreater(len(books), 0)
for book in books:
self.assertIs(book.is_book, True)
self.assertIs(book.is_pony, False)
self.assertIsNone(book.is_none)
self.assertIsNone(book.is_none_old)
def test_bulk_insert_expressions(self):
Restaurant.objects.bulk_create([
Restaurant(name="Sam's Shake Shack"),
Restaurant(name=Lower(Value("Betty's Beetroot Bar")))
])
bbb = Restaurant.objects.filter(name="betty's beetroot bar")
self.assertEqual(bbb.count(), 1)
def test_column_field_ordering_with_deferred(self):
store = Store.objects.first()
Employee.objects.create(id=1,
first_name='Max',
manager=True,
last_name='Paine',
store=store,
age=23,
salary=Decimal(50000.00))
Employee.objects.create(id=2,
first_name='Buffy',
manager=False,
last_name='Summers',
store=store,
age=18,
salary=Decimal(40000.00))
qs = Employee.objects.extra(select={
'random_value': '42'
}).select_related('store').annotate(
annotated_value=Value(17, output_field=IntegerField()))
rows = [(1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00),
store.name, 17),
(2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00),
store.name, 17)]
# and we respect deferred columns!
self.assertQuerysetEqual(
qs.defer('age').order_by('id'), rows, lambda e:
(e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age,
e.salary, e.store.name, e.annotated_value))
def test_annotation_in_f_grouped_by_annotation(self):
qs = (
Publisher.objects.annotate(multiplier=Value(3))
# group by option => sum of value * multiplier
.values('name').annotate(
multiplied_value_sum=Sum(F('multiplier') *
F('num_awards'))).order_by())
self.assertCountEqual(qs, [
{
'multiplied_value_sum': 9,
'name': 'Apress'
},
{
'multiplied_value_sum': 0,
'name': "Jonno's House of Books"
},
{
'multiplied_value_sum': 27,
'name': 'Morgan Kaufmann'
},
{
'multiplied_value_sum': 21,
'name': 'Prentice Hall'
},
{
'multiplied_value_sum': 3,
'name': 'Sams'
},
])
def test_annotate_with_aggregation(self):
books = Book.objects.annotate(is_book=Value(
1, output_field=IntegerField()),
rating_count=Count('rating'))
for book in books:
self.assertEqual(book.is_book, 1)
self.assertEqual(book.rating_count, 1)
def test_null_annotation(self):
"""
Annotating None onto a model round-trips
"""
book = Book.objects.annotate(
no_value=Value(None, output_field=IntegerField())).first()
self.assertIsNone(book.no_value)
def test_custom_functions(self):
Company(name='Apple',
motto=None,
ticker_name='APPL',
description='Beautiful Devices').save()
Company(name='Django Software Foundation',
motto=None,
ticker_name=None,
description=None).save()
Company(name='Google',
motto='Do No Evil',
ticker_name='GOOG',
description='Internet Company').save()
Company(name='Yahoo',
motto=None,
ticker_name=None,
description='Internet Company').save()
qs = Company.objects.annotate(
tagline=Func(F('motto'),
F('ticker_name'),
F('description'),
Value('No Tag'),
function='COALESCE')).order_by('name')
self.assertQuerysetEqual(qs, [('Apple', 'APPL'),
('Django Software Foundation', 'No Tag'),
('Google', 'Do No Evil'),
('Yahoo', 'Internet Company')], lambda c:
(c.name, c.tagline))
def test_mti_annotations(self):
"""
Fields on an inherited model can be referenced by an
annotated field.
"""
d = DepartmentStore.objects.create(
name='Angus & Robinson',
original_opening=datetime.date(2014, 3, 8),
friday_night_closing=datetime.time(21, 00, 00),
chain='Westfield')
books = Book.objects.filter(rating__gt=4)
for b in books:
d.books.add(b)
qs = DepartmentStore.objects.annotate(
other_name=F('name'),
other_chain=F('chain'),
is_open=Value(True, BooleanField()),
book_isbn=F('books__isbn')).order_by('book_isbn').filter(
chain='Westfield')
self.assertQuerysetEqual(
qs, [('Angus & Robinson', 'Westfield', True, '155860191'),
('Angus & Robinson', 'Westfield', True, '159059725')],
lambda d: (d.other_name, d.other_chain, d.is_open, d.book_isbn))
def test_filtering(self):
Author.objects.create(name='George. R. R. Martin')
Author.objects.create(name='Terry Pratchett')
self.assertQuerysetEqual(
Author.objects.annotate(middle_name=StrIndex('name', Value('R.'))).filter(middle_name__gt=0),
['George. R. R. Martin'],
lambda a: a.name
)
def coalesce(self):
# null on either side results in null for expression, wrap with coalesce
c = self.copy()
expressions = [
Coalesce(expression, Value(''))
for expression in c.get_source_expressions()
]
c.set_source_expressions(expressions)
return c
def test_arguments_must_be_expressions(self):
msg = 'QuerySet.annotate() received non-expression(s): %s.'
with self.assertRaisesMessage(TypeError, msg % BooleanField()):
Book.objects.annotate(BooleanField())
with self.assertRaisesMessage(TypeError, msg % True):
Book.objects.annotate(is_book=True)
with self.assertRaisesMessage(
TypeError, msg % ', '.join([str(BooleanField()), 'True'])):
Book.objects.annotate(BooleanField(), Value(False), is_book=True)
def test_replace_with_default_arg(self):
# The default replacement is an empty string.
qs = Author.objects.annotate(
same_name=Replace(F('name'), Value('R. R. ')))
self.assertQuerysetEqual(qs, [
('George R. R. Martin', 'George Martin'),
('J. R. R. Tolkien', 'J. Tolkien'),
],
transform=lambda x: (x.name, x.same_name),
ordered=False)
def test_pad(self):
Author.objects.create(name='John', alias='j')
tests = (
(LPad('name', 7, Value('xy')), 'xyxJohn'),
(RPad('name', 7, Value('xy')), 'Johnxyx'),
(LPad('name', 6, Value('x')), 'xxJohn'),
(RPad('name', 6, Value('x')), 'Johnxx'),
# The default pad string is a space.
(LPad('name', 6), ' John'),
(RPad('name', 6), 'John '),
# If string is longer than length it is truncated.
(LPad('name', 2), 'Jo'),
(RPad('name', 2), 'Jo'),
(LPad('name', 0), ''),
(RPad('name', 0), ''),
)
for function, padded_name in tests:
with self.subTest(function=function):
authors = Author.objects.annotate(padded_name=function)
self.assertQuerysetEqual(authors, [padded_name], lambda a: a.padded_name, ordered=False)
def test_fail_insert(self):
"""Window expressions can't be used in an INSERT statement."""
msg = 'Window expressions are not allowed in this query'
with self.assertRaisesMessage(FieldError, msg):
Employee.objects.create(
name='Jameson',
department='Management',
hire_date=datetime.date(2007, 7, 1),
salary=Window(
expression=Sum(Value(10000), order_by=F('pk').asc())),
)
def test_order_by(self):
Author.objects.create(name='Terry Pratchett')
Author.objects.create(name='J. R. R. Tolkien')
Author.objects.create(name='George. R. R. Martin')
self.assertQuerysetEqual(
Author.objects.order_by(StrIndex('name', Value('R.')).asc()), [
'Terry Pratchett',
'J. R. R. Tolkien',
'George. R. R. Martin',
],
lambda a: a.name
)
self.assertQuerysetEqual(
Author.objects.order_by(StrIndex('name', Value('R.')).desc()), [
'George. R. R. Martin',
'J. R. R. Tolkien',
'Terry Pratchett',
],
lambda a: a.name
)
def test_basic(self):
Author.objects.create(name='John', alias='xyz')
tests = (
(Repeat('name', 0), ''),
(Repeat('name', 2), 'JohnJohn'),
(Repeat('name', Length('alias'), output_field=CharField()), 'JohnJohnJohn'),
(Repeat(Value('x'), 3, output_field=CharField()), 'xxx'),
)
for function, repeated_text in tests:
with self.subTest(function=function):
authors = Author.objects.annotate(repeated_text=function)
self.assertQuerysetEqual(authors, [repeated_text], lambda a: a.repeated_text, ordered=False)
def test_date_range_contains(self):
filter_args = (
self.timestamps[1],
(self.dates[1], self.dates[2]),
Value(self.dates[0], output_field=DateField()),
Func(F('timestamps'), function='lower', output_field=DateField()),
)
for filter_arg in filter_args:
with self.subTest(filter_arg=filter_arg):
self.assertCountEqual(
RangesModel.objects.filter(**{'dates__contains': filter_arg}),
[self.obj, self.aware_obj],
)
def test_order_raises_on_non_selected_column(self):
qs1 = Number.objects.filter().annotate(
annotation=Value(1, IntegerField()),
).values('annotation', num2=F('num'))
qs2 = Number.objects.filter().values('id', 'num')
# Should not raise
list(qs1.union(qs2).order_by('annotation'))
list(qs1.union(qs2).order_by('num2'))
msg = 'ORDER BY term does not match any column in the result set'
# 'id' is not part of the select
with self.assertRaisesMessage(DatabaseError, msg):
list(qs1.union(qs2).order_by('id'))
# 'num' got realiased to num2
with self.assertRaisesMessage(DatabaseError, msg):
list(qs1.union(qs2).order_by('num'))
# switched order, now 'exists' again:
list(qs2.union(qs1).order_by('num'))
def test_column_field_ordering(self):
"""
Columns are aligned in the correct order for resolve_columns. This test
will fail on MySQL if column ordering is out. Column fields should be
aligned as:
1. extra_select
2. model_fields
3. annotation_fields
4. model_related_fields
"""
store = Store.objects.first()
Employee.objects.create(id=1,
first_name='Max',
manager=True,
last_name='Paine',
store=store,
age=23,
salary=Decimal(50000.00))
Employee.objects.create(id=2,
first_name='Buffy',
manager=False,
last_name='Summers',
store=store,
age=18,
salary=Decimal(40000.00))
qs = Employee.objects.extra(select={
'random_value': '42'
}).select_related('store').annotate(
annotated_value=Value(17, output_field=IntegerField()))
rows = [(1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00),
store.name, 17),
(2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00),
store.name, 17)]
self.assertQuerysetEqual(
qs.order_by('id'), rows, lambda e:
(e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age,
e.salary, e.store.name, e.annotated_value))
def test_custom_functions_can_ref_other_functions(self):
Company(name='Apple',
motto=None,
ticker_name='APPL',
description='Beautiful Devices').save()
Company(name='Django Software Foundation',
motto=None,
ticker_name=None,
description=None).save()
Company(name='Google',
motto='Do No Evil',
ticker_name='GOOG',
description='Internet Company').save()
Company(name='Yahoo',
motto=None,
ticker_name=None,
description='Internet Company').save()
class Lower(Func):
function = 'LOWER'
qs = Company.objects.annotate(tagline=Func(
F('motto'),
F('ticker_name'),
F('description'),
Value('No Tag'),
function='COALESCE',
)).annotate(tagline_lower=Lower(F(
'tagline'), output_field=CharField())).order_by('name')
# LOWER function supported by:
# oracle, postgres, mysql, sqlite, sqlserver
self.assertQuerysetEqual(
qs, [('Apple', 'APPL'.lower()),
('Django Software Foundation', 'No Tag'.lower()),
('Google', 'Do No Evil'.lower()),
('Yahoo', 'Internet Company'.lower())], lambda c:
(c.name, c.tagline_lower))
def test_basic(self):
authors = Author.objects.annotate(name_part=Ord('name'))
self.assertCountEqual(authors.filter(name_part__gt=Ord(Value('John'))),
[self.elena, self.rhonda])
self.assertCountEqual(
authors.exclude(name_part__gt=Ord(Value('John'))), [self.john])
def test_annotate_charfield(self):
Author.objects.create(name='George. R. R. Martin')
Author.objects.create(name='J. R. R. Tolkien')
Author.objects.create(name='Terry Pratchett')
authors = Author.objects.annotate(fullstop=StrIndex('name', Value('R.')))
self.assertQuerysetEqual(authors.order_by('name'), [9, 4, 0], lambda a: a.fullstop)
def test_unicode_values(self):
Author.objects.create(name='ツリー')
Author.objects.create(name='皇帝')
Author.objects.create(name='皇帝 ツリー')
authors = Author.objects.annotate(sb=StrIndex('name', Value('リ')))
self.assertQuerysetEqual(authors.order_by('name'), [2, 0, 5], lambda a: a.sb)