def test_transform(self):
try:
IntegerField.register_lookup(Chr)
authors = Author.objects.annotate(name_code_point=Ord('name'))
self.assertCountEqual(authors.filter(name_code_point__chr=Chr(ord('J'))), [self.john])
self.assertCountEqual(authors.exclude(name_code_point__chr=Chr(ord('J'))), [self.elena, self.rhonda])
finally:
IntegerField._unregister_lookup(Chr)
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_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_add_implementation(self):
class MySum(Sum):
pass
# test completely changing how the output is rendered
def lower_case_function_override(self, compiler, connection):
sql, params = compiler.compile(self.source_expressions[0])
substitutions = {
'function': self.function.lower(),
'expressions': sql
}
substitutions.update(self.extra)
return self.template % substitutions, params
setattr(MySum, 'as_' + connection.vendor, lower_case_function_override)
qs = Book.objects.annotate(sums=MySum(F('rating') + F('pages') +
F('price'),
output_field=IntegerField()))
self.assertEqual(str(qs.query).count('sum('), 1)
b1 = qs.get(pk=self.b4.pk)
self.assertEqual(b1.sums, 383)
# test changing the dict and delegating
def lower_case_function_super(self, compiler, connection):
self.extra['function'] = self.function.lower()
return super(MySum, self).as_sql(compiler, connection)
setattr(MySum, 'as_' + connection.vendor, lower_case_function_super)
qs = Book.objects.annotate(sums=MySum(F('rating') + F('pages') +
F('price'),
output_field=IntegerField()))
self.assertEqual(str(qs.query).count('sum('), 1)
b1 = qs.get(pk=self.b4.pk)
self.assertEqual(b1.sums, 383)
# test overriding all parts of the template
def be_evil(self, compiler, connection):
substitutions = {'function': 'MAX', 'expressions': '2'}
substitutions.update(self.extra)
return self.template % substitutions, ()
setattr(MySum, 'as_' + connection.vendor, be_evil)
qs = Book.objects.annotate(sums=MySum(F('rating') + F('pages') +
F('price'),
output_field=IntegerField()))
self.assertEqual(str(qs.query).count('MAX('), 1)
b1 = qs.get(pk=self.b4.pk)
self.assertEqual(b1.sums, 2)
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_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_mixed_type_annotation_numbers(self):
test = self.b1
b = Book.objects.annotate(combined=ExpressionWrapper(
F('pages') +
F('rating'), output_field=IntegerField())).get(isbn=test.isbn)
combined = int(test.pages + test.rating)
self.assertEqual(b.combined, combined)
class Ntile(Func):
function = 'NTILE'
output_field = IntegerField()
window_compatible = True
def __init__(self, num_buckets=1, **extra):
if num_buckets <= 0:
raise ValueError('num_buckets must be greater than 0.')
super().__init__(num_buckets, **extra)
class Extract(TimezoneMixin, Transform):
lookup_name = None
output_field = IntegerField()
def __init__(self, expression, lookup_name=None, tzinfo=None, **extra):
if self.lookup_name is None:
self.lookup_name = lookup_name
if self.lookup_name is None:
raise ValueError('lookup_name must be provided')
self.tzinfo = tzinfo
super().__init__(expression, **extra)
def as_sql(self, compiler, connection):
sql, params = compiler.compile(self.lhs)
lhs_output_field = self.lhs.output_field
if isinstance(lhs_output_field, DateTimeField):
tzname = self.get_tzname()
sql = connection.ops.datetime_extract_sql(self.lookup_name, sql,
tzname)
elif isinstance(lhs_output_field, DateField):
sql = connection.ops.date_extract_sql(self.lookup_name, sql)
elif isinstance(lhs_output_field, TimeField):
sql = connection.ops.time_extract_sql(self.lookup_name, sql)
elif isinstance(lhs_output_field, DurationField):
if not connection.features.has_native_duration_field:
raise ValueError(
'Extract requires native DurationField database support.')
sql = connection.ops.time_extract_sql(self.lookup_name, sql)
else:
# resolve_expression has already validated the output_field so this
# assert should never be hit.
assert False, "Tried to Extract from an invalid type."
return sql, params
def resolve_expression(self,
query=None,
allow_joins=True,
reuse=None,
summarize=False,
for_save=False):
copy = super().resolve_expression(query, allow_joins, reuse, summarize,
for_save)
field = copy.lhs.output_field
if not isinstance(
field, (DateField, DateTimeField, TimeField, DurationField)):
raise ValueError(
'Extract input expression must be DateField, DateTimeField, '
'TimeField, or DurationField.')
# Passing dates to functions expecting datetimes is most likely a mistake.
if type(field) == DateField and copy.lookup_name in ('hour', 'minute',
'second'):
raise ValueError(
"Cannot extract time component '%s' from DateField '%s'. " %
(copy.lookup_name, field.name))
return copy
class ArrayLenTransform(Transform):
lookup_name = 'len'
output_field = IntegerField()
def as_sql(self, compiler, connection):
lhs, params = compiler.compile(self.lhs)
# Distinguish NULL and empty arrays
return ('CASE WHEN %(lhs)s IS NULL THEN NULL ELSE '
'coalesce(array_length(%(lhs)s, 1), 0) END') % {
'lhs': lhs
}, params
def test_annotation_filter_with_subquery(self):
long_books_qs = Book.objects.filter(
publisher=OuterRef('pk'),
pages__gt=400,
).values('publisher').annotate(count=Count('pk')).values('count')
publisher_books_qs = Publisher.objects.annotate(
total_books=Count('book'), ).filter(total_books=Subquery(
long_books_qs, output_field=IntegerField()), ).values('name')
self.assertCountEqual(publisher_books_qs, [{
'name': 'Sams'
}, {
'name': 'Morgan Kaufmann'
}])
class Ord(Transform):
function = 'ASCII'
lookup_name = 'ord'
output_field = IntegerField()
def as_mysql(self, compiler, connection, **extra_context):
return super().as_sql(compiler,
connection,
function='ORD',
**extra_context)
def as_sqlite(self, compiler, connection, **extra_context):
return super().as_sql(compiler,
connection,
function='UNICODE',
**extra_context)
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_combine_different_types(self):
msg = 'Expression contains mixed types. You must set output_field.'
qs = Book.objects.annotate(sums=Sum('rating') + Sum('pages') +
Sum('price'))
with self.assertRaisesMessage(FieldError, msg):
qs.first()
with self.assertRaisesMessage(FieldError, msg):
qs.first()
b1 = Book.objects.annotate(sums=Sum(
F('rating') + F('pages') +
F('price'), output_field=IntegerField())).get(pk=self.b4.pk)
self.assertEqual(b1.sums, 383)
b2 = Book.objects.annotate(sums=Sum(
F('rating') + F('pages') +
F('price'), output_field=FloatField())).get(pk=self.b4.pk)
self.assertEqual(b2.sums, 383.69)
b3 = Book.objects.annotate(sums=Sum(
F('rating') + F('pages') +
F('price'), output_field=DecimalField())).get(pk=self.b4.pk)
self.assertEqual(b3.sums, Approximate(Decimal("383.69"), places=2))
def test_filter_annotation(self):
books = Book.objects.annotate(
is_book=Value(1, output_field=IntegerField())).filter(is_book=1)
for book in books:
self.assertEqual(book.is_book, 1)
class DenseRank(Func):
function = 'DENSE_RANK'
output_field = IntegerField()
window_compatible = True
class RowNumber(Func):
function = 'ROW_NUMBER'
output_field = IntegerField()
window_compatible = True
class Rank(Func):
function = 'RANK'
output_field = IntegerField()
window_compatible = True
class MemSize(GeoFunc):
output_field = IntegerField()
arity = 1
class NumGeometries(GeoFunc):
output_field = IntegerField()
arity = 1
class NumPoints(GeoFunc):
output_field = IntegerField()
arity = 1
class RegrCount(StatAggregate):
function = 'REGR_COUNT'
output_field = IntegerField()
def convert_value(self, value, expression, connection):
return 0 if value is None else value
