def test_aggregate_over_complex_annotation(self):
qs = Author.objects.annotate(
combined_ages=Sum(F('age') + F('friends__age')))
age = qs.aggregate(max_combined_age=Max('combined_ages'))
self.assertEqual(age['max_combined_age'], 176)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') * 2)
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'),
sum_combined_age=Sum('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
self.assertEqual(age['sum_combined_age'], 954)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'),
sum_combined_age_doubled=Sum('combined_ages') +
Sum('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
self.assertEqual(age['sum_combined_age_doubled'], 954 * 2)
def test_aggregation_expressions(self):
a1 = Author.objects.aggregate(av_age=Sum('age') / Count('*'))
a2 = Author.objects.aggregate(av_age=Sum('age') / Count('age'))
a3 = Author.objects.aggregate(av_age=Avg('age'))
self.assertEqual(a1, {'av_age': 37})
self.assertEqual(a2, {'av_age': 37})
self.assertEqual(a3, {'av_age': Approximate(37.4, places=1)})
def test_annotate_values_aggregate(self):
alias_age = Author.objects.annotate(age_alias=F('age')).values(
'age_alias', ).aggregate(sum_age=Sum('age_alias'))
age = Author.objects.values('age').aggregate(sum_age=Sum('age'))
self.assertEqual(alias_age['sum_age'], age['sum_age'])
def test_annotate_over_annotate(self):
author = Author.objects.annotate(age_alias=F('age')).annotate(
sum_age=Sum('age_alias')).get(name="Adrian Holovaty")
other_author = Author.objects.annotate(sum_age=Sum('age')).get(
name="Adrian Holovaty")
self.assertEqual(author.sum_age, other_author.sum_age)
def test_complex_aggregations_require_kwarg(self):
with self.assertRaisesMessage(TypeError,
'Complex annotations require an alias'):
Author.objects.annotate(Sum(F('age') + F('friends__age')))
with self.assertRaisesMessage(TypeError,
'Complex aggregates require an alias'):
Author.objects.aggregate(Sum('age') / Count('age'))
with self.assertRaisesMessage(TypeError,
'Complex aggregates require an alias'):
Author.objects.aggregate(Sum(1))
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(self):
hs1 = HasLinkThing.objects.create()
hs2 = HasLinkThing.objects.create()
HasLinkThing.objects.create()
b = Board.objects.create(name=str(hs1.pk))
Link.objects.create(content_object=hs2)
link = Link.objects.create(content_object=hs1)
Link.objects.create(content_object=b)
qs = HasLinkThing.objects.annotate(Sum('links')).filter(pk=hs1.pk)
# If content_type restriction isn't in the query's join condition,
# then wrong results are produced here as the link to b will also match
# (b and hs1 have equal pks).
self.assertEqual(qs.count(), 1)
self.assertEqual(qs[0].links__sum, link.id)
link.delete()
# Now if we don't have proper left join, we will not produce any
# results at all here.
# clear cached results
qs = qs.all()
self.assertEqual(qs.count(), 1)
# Note - 0 here would be a nicer result...
self.assertIs(qs[0].links__sum, None)
# Finally test that filtering works.
self.assertEqual(qs.filter(links__sum__isnull=True).count(), 1)
self.assertEqual(qs.filter(links__sum__isnull=False).count(), 0)
def test_related_aggregates_m2m_and_fk(self):
q = Q(friends__book__publisher__name='Apress') & ~Q(
friends__name='test3')
agg = Sum('friends__book__pages', filter=q)
self.assertEqual(
Author.objects.filter(name='test').aggregate(pages=agg)['pages'],
528)
def test_annotation_expressions(self):
authors = Author.objects.annotate(
combined_ages=Sum(F('age') + F('friends__age'))).order_by('name')
authors2 = Author.objects.annotate(
combined_ages=Sum('age') + Sum('friends__age')).order_by('name')
for qs in (authors, authors2):
self.assertQuerysetEqual(qs, [('Adrian Holovaty', 132),
('Brad Dayley', None),
('Jacob Kaplan-Moss', 129),
('James Bennett', 63),
('Jeffrey Forcier', 128),
('Paul Bissex', 120),
('Peter Norvig', 103),
('Stuart Russell', 103),
('Wesley J. Chun', 176)], lambda a:
(a.name, a.combined_ages))
def test_annotate_defer(self):
qs = Book.objects.annotate(page_sum=Sum("pages")).defer('name').filter(
pk=self.b1.pk)
rows = [(self.b1.id, "159059725", 447,
"The Definitive Guide to Django: Web Development Done Right")]
self.assertQuerysetEqual(qs.order_by('pk'), rows, lambda r:
(r.id, r.isbn, r.page_sum, r.name))
def test_related_aggregate(self):
vals = Author.objects.aggregate(Avg("friends__age"))
self.assertEqual(vals,
{'friends__age__avg': Approximate(34.07, places=2)})
vals = Book.objects.filter(rating__lt=4.5).aggregate(
Avg("authors__age"))
self.assertEqual(vals,
{'authors__age__avg': Approximate(38.2857, places=2)})
vals = Author.objects.all().filter(name__contains="a").aggregate(
Avg("book__rating"))
self.assertEqual(vals, {'book__rating__avg': 4.0})
vals = Book.objects.aggregate(Sum("publisher__num_awards"))
self.assertEqual(vals, {'publisher__num_awards__sum': 30})
vals = Publisher.objects.aggregate(Sum("book__price"))
self.assertEqual(vals, {'book__price__sum': Decimal('270.27')})
def test_reverse_fkey_annotate(self):
books = Book.objects.annotate(
Sum("publisher__num_awards")).order_by("name")
self.assertQuerysetEqual(
books,
[('Artificial Intelligence: A Modern Approach', 7),
('Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
9), ('Practical Django Projects', 3),
('Python Web Development with Django', 7),
('Sams Teach Yourself Django in 24 Hours', 1),
('The Definitive Guide to Django: Web Development Done Right', 3)
], lambda b: (b.name, b.publisher__num_awards__sum))
publishers = Publisher.objects.annotate(
Sum("book__price")).order_by("name")
self.assertQuerysetEqual(publishers,
[('Apress', Decimal("59.69")),
("Jonno's House of Books", None),
('Morgan Kaufmann', Decimal("75.00")),
('Prentice Hall', Decimal("112.49")),
('Sams', Decimal("23.09"))], lambda p:
(p.name, p.book__price__sum))
def test_sum_distinct_aggregate(self):
"""
Sum on a distinct() QuerySet should aggregate only the distinct items.
"""
authors = Author.objects.filter(book__in=[self.b5, self.b6])
self.assertEqual(authors.count(), 3)
distinct_authors = authors.distinct()
self.assertEqual(distinct_authors.count(), 2)
# Selected author ages are 57 and 46
age_sum = distinct_authors.aggregate(Sum('age'))
self.assertEqual(age_sum['age__sum'], 103)
def test_area_with_regular_aggregate(self):
# Create projected country objects, for this test to work on all backends.
for c in Country.objects.all():
CountryWebMercator.objects.create(name=c.name, mpoly=c.mpoly.transform(3857, clone=True))
# Test in projected coordinate system
qs = CountryWebMercator.objects.annotate(area_sum=Sum(functions.Area('mpoly')))
# Some backends (e.g. Oracle) cannot group by multipolygon values, so
# defer such fields in the aggregation query.
for c in qs.defer('mpoly'):
result = c.area_sum
# If the result is a measure object, get value.
if isinstance(result, Area):
result = result.sq_m
self.assertAlmostEqual((result - c.mpoly.area) / c.mpoly.area, 0)
def test_values_annotation_with_expression(self):
# ensure the F() is promoted to the group by clause
qs = Author.objects.values('name').annotate(another_age=Sum('age') +
F('age'))
a = qs.get(name="Adrian Holovaty")
self.assertEqual(a['another_age'], 68)
qs = qs.annotate(friend_count=Count('friends'))
a = qs.get(name="Adrian Holovaty")
self.assertEqual(a['friend_count'], 2)
qs = qs.annotate(combined_age=Sum('age') + F('friends__age')).filter(
name="Adrian Holovaty").order_by('-combined_age')
self.assertEqual(list(qs), [{
"name": 'Adrian Holovaty',
"another_age": 68,
"friend_count": 1,
"combined_age": 69
}, {
"name": 'Adrian Holovaty',
"another_age": 68,
"friend_count": 1,
"combined_age": 63
}])
vals = qs.values('name', 'combined_age')
self.assertEqual(list(vals), [
{
'name': 'Adrian Holovaty',
'combined_age': 69
},
{
'name': 'Adrian Holovaty',
'combined_age': 63
},
])
def test_annotated_aggregate_over_annotated_aggregate(self):
with self.assertRaisesMessage(
FieldError,
"Cannot compute Sum('id__max'): 'id__max' is an aggregate"):
Book.objects.annotate(Max('id')).annotate(Sum('id__max'))
class MyMax(Max):
def as_sql(self, compiler, connection):
self.set_source_expressions(self.get_source_expressions()[0:1])
return super().as_sql(compiler, connection)
with self.assertRaisesMessage(
FieldError,
"Cannot compute Max('id__max'): 'id__max' is an aggregate"):
Book.objects.annotate(
Max('id')).annotate(my_max=MyMax('id__max', 'price'))
def test_decimal_max_digits_has_no_effect(self):
Book.objects.all().delete()
a1 = Author.objects.first()
p1 = Publisher.objects.first()
thedate = timezone.now()
for i in range(10):
Book.objects.create(isbn="abcde{}".format(i),
name="none",
pages=10,
rating=4.0,
price=9999.98,
contact=a1,
publisher=p1,
pubdate=thedate)
book = Book.objects.aggregate(price_sum=Sum('price'))
self.assertEqual(book['price_sum'], Decimal("99999.80"))
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_double_filtered_aggregates(self):
agg = Sum('age', filter=Q(Q(name='test2') & ~Q(name='test')))
self.assertEqual(Author.objects.aggregate(age=agg)['age'], 60)
def test_values_with_pk_annotation(self):
# annotate references a field in values() with pk
publishers = Publisher.objects.values(
'id', 'book__rating').annotate(total=Sum('book__rating'))
for publisher in publishers.filter(pk=self.p1.pk):
self.assertEqual(publisher['book__rating'], publisher['total'])
def test_filter_wrong_annotation(self):
with self.assertRaisesMessage(
FieldError, "Cannot resolve keyword 'nope' into field."):
list(
Book.objects.annotate(sum_rating=Sum('rating')).filter(
sum_rating=F('nope')))
def test_filter_agg_with_double_f(self):
books = Book.objects.annotate(sum_rating=Sum('rating')).filter(
sum_rating=F('sum_rating'))
for book in books:
self.assertEqual(book.sum_rating, book.rating)
def test_aggregate_over_annotation(self):
agg = Author.objects.annotate(other_age=F('age')).aggregate(
otherage_sum=Sum('other_age'))
other_agg = Author.objects.aggregate(age_sum=Sum('age'))
self.assertEqual(agg['otherage_sum'], other_agg['age_sum'])
def test_case_aggregate(self):
agg = Sum(
Case(When(friends__age=40, then=F('friends__age'))),
filter=Q(friends__name__startswith='test'),
)
self.assertEqual(Author.objects.aggregate(age=agg)['age'], 80)
def test_filtered_aggregate_on_annotate(self):
pages_annotate = Sum('book__pages', filter=Q(book__rating__gt=3))
age_agg = Sum('age', filter=Q(total_pages__gte=400))
aggregated = Author.objects.annotate(
total_pages=pages_annotate).aggregate(summed_age=age_agg)
self.assertEqual(aggregated, {'summed_age': 140})
def test_plain_annotate(self):
agg = Sum('book__pages', filter=Q(book__rating__gt=3))
qs = Author.objects.annotate(pages=agg).order_by('pk')
self.assertSequenceEqual([a.pages for a in qs], [447, None, 1047])
def test_sum_duration_field(self):
self.assertEqual(
Publisher.objects.aggregate(
Sum('duration', output_field=DurationField())),
{'duration__sum': datetime.timedelta(days=3)})
def test_related_aggregates_m2m(self):
agg = Sum('friends__age', filter=~Q(friends__name='test'))
self.assertEqual(
Author.objects.filter(name='test').aggregate(age=agg)['age'], 160)
def test_excluded_aggregates(self):
agg = Sum('age', filter=~Q(name='test2'))
self.assertEqual(Author.objects.aggregate(age=agg)['age'], 140)
def test_filtered_aggregates(self):
agg = Sum('age', filter=Q(name__startswith='test'))
self.assertEqual(Author.objects.aggregate(age=agg)['age'], 200)