def test_annotate_values_list(self):
books = (Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values_list("pk", "isbn",
"mean_age"))
self.assertEqual(list(books), [(self.b1.id, '159059725', 34.5)])
books = Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values_list("isbn")
self.assertEqual(list(books), [('159059725', )])
books = Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values_list("mean_age")
self.assertEqual(list(books), [(34.5, )])
books = (Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values_list("mean_age", flat=True))
self.assertEqual(list(books), [34.5])
books = Book.objects.values_list("price").annotate(
count=Count("price")).order_by("-count", "price")
self.assertEqual(list(books), [
(Decimal("29.69"), 2),
(Decimal('23.09'), 1),
(Decimal('30'), 1),
(Decimal('75'), 1),
(Decimal('82.8'), 1),
])
def test_order_of_precedence(self):
p1 = Book.objects.filter(rating=4).aggregate(
avg_price=(Avg('price') + 2) * 3)
self.assertEqual(p1, {'avg_price': Approximate(148.18, places=2)})
p2 = Book.objects.filter(rating=4).aggregate(avg_price=Avg('price') +
2 * 3)
self.assertEqual(p2, {'avg_price': Approximate(53.39, places=2)})
def test_avg_duration_field(self):
# Explicit `output_field`.
self.assertEqual(
Publisher.objects.aggregate(
Avg('duration', output_field=DurationField())),
{'duration__avg': datetime.timedelta(days=1, hours=12)})
# Implicit `output_field`.
self.assertEqual(
Publisher.objects.aggregate(Avg('duration')),
{'duration__avg': datetime.timedelta(days=1, hours=12)})
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_expression_wrapped_with_parentheses_on_postgresql(self):
"""
The SQL for the Cast expression is wrapped with parentheses in case
it's a complex expression.
"""
list(Author.objects.annotate(cast_float=Cast(Avg('age'), models.FloatField())))
self.assertIn('(AVG("db_functions_author"."age"))::double precision', connection.queries[-1]['sql'])
def test_ticket12886(self):
"""
Aggregation over sliced queryset works correctly.
"""
qs = Book.objects.all().order_by('-rating')[0:3]
vals = qs.aggregate(
average_top3_rating=Avg('rating'))['average_top3_rating']
self.assertAlmostEqual(vals, 4.5, places=2)
def test_arguments_must_be_expressions(self):
msg = 'QuerySet.aggregate() received non-expression(s): %s.'
with self.assertRaisesMessage(TypeError, msg % FloatField()):
Book.objects.aggregate(FloatField())
with self.assertRaisesMessage(TypeError, msg % True):
Book.objects.aggregate(is_book=True)
with self.assertRaisesMessage(
TypeError, msg % ', '.join([str(FloatField()), 'True'])):
Book.objects.aggregate(FloatField(), Avg('price'), is_book=True)
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_expression_on_aggregation(self):
# Create a plain expression
class Greatest(Func):
function = 'GREATEST'
def as_sqlite(self, compiler, connection):
return super().as_sql(compiler, connection, function='MAX')
qs = Publisher.objects.annotate(price_or_median=Greatest(
Avg('book__rating'), Avg('book__price'))).filter(
price_or_median__gte=F('num_awards')).order_by('num_awards')
self.assertQuerysetEqual(qs, [1, 3, 7, 9], lambda v: v.num_awards)
qs2 = Publisher.objects.annotate(rating_or_num_awards=Greatest(
Avg('book__rating'), F('num_awards'), output_field=FloatField()
)).filter(
rating_or_num_awards__gt=F('num_awards')).order_by('num_awards')
self.assertQuerysetEqual(qs2, [1, 3], lambda v: v.num_awards)
def test_more_aggregation(self):
a = Author.objects.get(name__contains='Norvig')
b = Book.objects.get(name__contains='Done Right')
b.authors.add(a)
b.save()
vals = (Book.objects.annotate(num_authors=Count("authors__id")).filter(
authors__name__contains="Norvig",
num_authors__gt=1).aggregate(Avg("rating")))
self.assertEqual(vals, {"rating__avg": 4.25})
def test_annotate_basic(self):
self.assertQuerysetEqual(Book.objects.annotate().order_by('pk'), [
"The Definitive Guide to Django: Web Development Done Right",
"Sams Teach Yourself Django in 24 Hours",
"Practical Django Projects", "Python Web Development with Django",
"Artificial Intelligence: A Modern Approach",
"Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp"
], lambda b: b.name)
books = Book.objects.annotate(mean_age=Avg("authors__age"))
b = books.get(pk=self.b1.pk)
self.assertEqual(
b.name,
'The Definitive Guide to Django: Web Development Done Right')
self.assertEqual(b.mean_age, 34.5)
def test_ticket11881(self):
"""
Subqueries do not needlessly contain ORDER BY, SELECT FOR UPDATE or
select_related() stuff.
"""
qs = Book.objects.all().select_for_update().order_by(
'pk').select_related('publisher').annotate(max_pk=Max('pk'))
with CaptureQueriesContext(connection) as captured_queries:
qs.aggregate(avg_pk=Avg('max_pk'))
self.assertEqual(len(captured_queries), 1)
qstr = captured_queries[0]['sql'].lower()
self.assertNotIn('for update', qstr)
forced_ordering = connection.ops.force_no_ordering()
if forced_ordering:
# If the backend needs to force an ordering we make sure it's
# the only "ORDER BY" clause present in the query.
self.assertEqual(
re.findall(r'order by (\w+)', qstr),
[', '.join(f[1][0] for f in forced_ordering).lower()])
else:
self.assertNotIn('order by', qstr)
self.assertEqual(qstr.count(' join '), 0)
def test_annotate_m2m(self):
books = Book.objects.filter(rating__lt=4.5).annotate(
Avg("authors__age")).order_by("name")
self.assertQuerysetEqual(
books,
[('Artificial Intelligence: A Modern Approach', 51.5),
('Practical Django Projects', 29.0),
('Python Web Development with Django', Approximate(30.3,
places=1)),
('Sams Teach Yourself Django in 24 Hours', 45.0)],
lambda b: (b.name, b.authors__age__avg),
)
books = Book.objects.annotate(
num_authors=Count("authors")).order_by("name")
self.assertQuerysetEqual(books, [
('Artificial Intelligence: A Modern Approach', 2),
('Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
1), ('Practical Django Projects', 1),
('Python Web Development with Django', 3),
('Sams Teach Yourself Django in 24 Hours', 1),
('The Definitive Guide to Django: Web Development Done Right', 2)
], lambda b: (b.name, b.num_authors))
def test_backwards_m2m_annotate(self):
authors = Author.objects.filter(name__contains="a").annotate(
Avg("book__rating")).order_by("name")
self.assertQuerysetEqual(authors, [('Adrian Holovaty', 4.5),
('Brad Dayley', 3.0),
('Jacob Kaplan-Moss', 4.5),
('James Bennett', 4.0),
('Paul Bissex', 4.0),
('Stuart Russell', 4.0)], lambda a:
(a.name, a.book__rating__avg))
authors = Author.objects.annotate(
num_books=Count("book")).order_by("name")
self.assertQuerysetEqual(authors, [('Adrian Holovaty', 1),
('Brad Dayley', 1),
('Jacob Kaplan-Moss', 1),
('James Bennett', 1),
('Jeffrey Forcier', 1),
('Paul Bissex', 1),
('Peter Norvig', 2),
('Stuart Russell', 1),
('Wesley J. Chun', 1)], lambda a:
(a.name, a.num_books))
def test_annotate_values(self):
books = list(
Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values())
self.assertEqual(books, [{
"contact_id": self.a1.id,
"id": self.b1.id,
"isbn": "159059725",
"mean_age": 34.5,
"name":
"The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": self.p1.id,
"rating": 4.5,
}])
books = (Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg('authors__age')).values('pk', 'isbn', 'mean_age'))
self.assertEqual(list(books), [{
"pk": self.b1.pk,
"isbn": "159059725",
"mean_age": 34.5,
}])
books = Book.objects.filter(pk=self.b1.pk).annotate(
mean_age=Avg("authors__age")).values("name")
self.assertEqual(
list(books),
[{
'name':
'The Definitive Guide to Django: Web Development Done Right'
}],
)
books = Book.objects.filter(pk=self.b1.pk).values().annotate(
mean_age=Avg('authors__age'))
self.assertEqual(list(books), [{
"contact_id": self.a1.id,
"id": self.b1.id,
"isbn": "159059725",
"mean_age": 34.5,
"name":
"The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": self.p1.id,
"rating": 4.5,
}])
books = (Book.objects.values("rating").annotate(
n_authors=Count("authors__id"),
mean_age=Avg("authors__age")).order_by("rating"))
self.assertEqual(list(books), [{
"rating": 3.0,
"n_authors": 1,
"mean_age": 45.0,
}, {
"rating": 4.0,
"n_authors": 6,
"mean_age": Approximate(37.16, places=1)
}, {
"rating": 4.5,
"n_authors": 2,
"mean_age": 34.5,
}, {
"rating": 5.0,
"n_authors": 1,
"mean_age": 57.0,
}])
authors = Author.objects.annotate(Avg("friends__age")).order_by("name")
self.assertQuerysetEqual(
authors, [('Adrian Holovaty', 32.0), ('Brad Dayley', None),
('Jacob Kaplan-Moss', 29.5), ('James Bennett', 34.0),
('Jeffrey Forcier', 27.0), ('Paul Bissex', 31.0),
('Peter Norvig', 46.0), ('Stuart Russell', 57.0),
('Wesley J. Chun', Approximate(33.66, places=1))],
lambda a: (a.name, a.friends__age__avg))
def test_aggregate_in_order_by(self):
msg = ('Using an aggregate in order_by() without also including it in '
'annotate() is not allowed: Avg(F(book__rating)')
with self.assertRaisesMessage(FieldError, msg):
Author.objects.values('age').order_by(Avg('book__rating'))
def test_avg_decimal_field(self):
v = Book.objects.filter(rating=4).aggregate(
avg_price=(Avg('price')))['avg_price']
self.assertIsInstance(v, float)
self.assertEqual(v, Approximate(47.39, places=2))
def test_single_aggregate(self):
vals = Author.objects.aggregate(Avg("age"))
self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)})
def test_multiple_aggregates(self):
vals = Author.objects.aggregate(Sum("age"), Avg("age"))
self.assertEqual(vals, {
"age__sum": 337,
"age__avg": Approximate(37.4, places=1)
})
def test_aggregate_alias(self):
vals = Store.objects.filter(name="Amazon.com").aggregate(
amazon_mean=Avg("books__rating"))
self.assertEqual(vals, {'amazon_mean': Approximate(4.08, places=2)})
def test_aggregate_annotation(self):
vals = Book.objects.annotate(
num_authors=Count("authors__id")).aggregate(Avg("num_authors"))
self.assertEqual(vals,
{"num_authors__avg": Approximate(1.66, places=1)})