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_ticket_16409(self):
# Regression for #16409 - make sure defer() and only() work with annotate()
self.assertIsInstance(
list(SimpleItem.objects.annotate(Count('feature')).defer('name')),
list)
self.assertIsInstance(
list(SimpleItem.objects.annotate(Count('feature')).only('name')),
list)
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'
}])
def test_basic(self):
querysets = [
Tag.objects.filter(name='test'),
Tag.objects.filter(name='test').select_related('parent'),
Tag.objects.filter(name='test').prefetch_related('children'),
Tag.objects.filter(name='test').annotate(Count('children')),
Tag.objects.filter(name='test').values_list('name'),
Tag.objects.order_by().union(
Tag.objects.order_by().filter(name='test')),
Tag.objects.all().select_for_update().filter(name='test'),
]
supported_formats = connection.features.supported_explain_formats
all_formats = (None, ) + tuple(supported_formats) + tuple(
f.lower() for f in supported_formats)
for idx, queryset in enumerate(querysets):
for format in all_formats:
with self.subTest(format=format, queryset=idx):
if connection.vendor == 'mysql':
# This does a query and caches the result.
connection.features.needs_explain_extended
with self.assertNumQueries(1), CaptureQueriesContext(
connection) as captured_queries:
result = queryset.explain(format=format)
self.assertTrue(captured_queries[0]['sql'].startswith(
connection.ops.explain_prefix))
self.assertIsInstance(result, str)
self.assertTrue(result)
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_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_aggregate(self):
"""
filtered_relation() not only improves performance but also creates
correct results when aggregating with multiple LEFT JOINs.
Books can be reserved then rented by a borrower. Each reservation and
rental session are recorded with Reservation and RentalSession models.
Every time a reservation or a rental session is over, their state is
changed to 'stopped'.
Goal: Count number of books that are either currently reserved or
rented by borrower1 or available.
"""
qs = Book.objects.annotate(
is_reserved_or_rented_by=Case(
When(reservation__state=Reservation.NEW, then=F('reservation__borrower__pk')),
When(rental_session__state=RentalSession.NEW, then=F('rental_session__borrower__pk')),
default=None,
)
).filter(
Q(is_reserved_or_rented_by=self.borrower1.pk) | Q(state=Book.AVAILABLE)
).distinct()
self.assertEqual(qs.count(), 1)
# If count is equal to 1, the same aggregation should return in the
# same result but it returns 4.
self.assertSequenceEqual(qs.annotate(total=Count('pk')).values('total'), [{'total': 4}])
# With FilteredRelation, the result is as expected (1).
qs = Book.objects.annotate(
active_reservations=FilteredRelation(
'reservation', condition=Q(
reservation__state=Reservation.NEW,
reservation__borrower=self.borrower1,
)
),
).annotate(
active_rental_sessions=FilteredRelation(
'rental_session', condition=Q(
rental_session__state=RentalSession.NEW,
rental_session__borrower=self.borrower1,
)
),
).filter(
(Q(active_reservations__isnull=False) | Q(active_rental_sessions__isnull=False)) |
Q(state=Book.AVAILABLE)
).distinct()
self.assertEqual(qs.count(), 1)
self.assertSequenceEqual(qs.annotate(total=Count('pk')).values('total'), [{'total': 1}])
def test_values_aggregation(self):
# Refs #20782
max_rating = Book.objects.values('rating').aggregate(
max_rating=Max('rating'))
self.assertEqual(max_rating['max_rating'], 5)
max_books_per_rating = Book.objects.values('rating').annotate(
books_per_rating=Count('id')).aggregate(Max('books_per_rating'))
self.assertEqual(max_books_per_rating, {'books_per_rating__max': 3})
def test_rawsql_group_by_collapse(self):
raw = RawSQL('SELECT MIN(id) FROM annotations_book', [])
qs = Author.objects.values('id').annotate(
min_book_id=raw,
count_friends=Count('friends'),
).order_by()
_, _, group_by = qs.query.get_compiler(using='default').pre_sql_setup()
self.assertEqual(len(group_by), 1)
self.assertNotEqual(raw, group_by[0])
def test_defer_annotate_select_related(self):
location = Location.objects.create()
Request.objects.create(location=location)
self.assertIsInstance(
list(
Request.objects.annotate(Count('items')).select_related(
'profile', 'location').only('profile', 'location')), list)
self.assertIsInstance(
list(
Request.objects.annotate(Count('items')).select_related(
'profile', 'location').only('profile__profile1',
'location__location1')), list)
self.assertIsInstance(
list(
Request.objects.annotate(Count('items')).select_related(
'profile', 'location').defer('request1', 'request2',
'request3', 'request4')),
list)
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_non_grouped_annotation_not_in_group_by(self):
"""
An annotation not included in values() before an aggregate should be
excluded from the group by clause.
"""
qs = (Book.objects.annotate(xprice=F('price')).filter(
rating=4.0).values('rating').annotate(
count=Count('publisher_id', distinct=True)).values(
'count', 'rating').order_by('count'))
self.assertEqual(list(qs), [{'rating': 4.0, 'count': 2}])
def test_complex_values_aggregation(self):
max_rating = Book.objects.values('rating').aggregate(
double_max_rating=Max('rating') + Max('rating'))
self.assertEqual(max_rating['double_max_rating'], 5 * 2)
max_books_per_rating = Book.objects.values('rating').annotate(
books_per_rating=Count('id') + 5).aggregate(
Max('books_per_rating'))
self.assertEqual(max_books_per_rating,
{'books_per_rating__max': 3 + 5})
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_multiple_annotation(self):
multi_field = MultiFields.objects.create(
point=Point(1, 1),
city=City.objects.get(name='Houston'),
poly=Polygon(((1, 1), (1, 2), (2, 2), (2, 1), (1, 1))),
)
qs = City.objects.values('name').annotate(distance=Min(
functions.Distance('multifields__point',
multi_field.city.point)), ).annotate(
count=Count('multifields'))
self.assertTrue(qs.first())
def test_update_annotated_multi_table_queryset(self):
"""
Update of a queryset that's been annotated and involves multiple tables.
"""
# Trivial annotated update
qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
self.assertEqual(qs.update(value='Foo'), 3)
# Update where annotation is used for filtering
qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
self.assertEqual(qs.filter(related_count=1).update(value='Foo'), 1)
# Update where annotation is used in update parameters
# #26539 - This isn't forbidden but also doesn't generate proper SQL
# qs = RelatedPoint.objects.annotate(data_name=F('data__name'))
# updated = qs.update(name=F('data_name'))
# self.assertEqual(updated, 1)
# Update where aggregation annotation is used in update parameters
qs = RelatedPoint.objects.annotate(max=Max('data__value'))
with self.assertRaisesMessage(
FieldError,
'Aggregate functions are not allowed in this query'):
qs.update(name=F('max'))
def test_annotation(self):
vals = Author.objects.filter(pk=self.a1.pk).aggregate(
Count("friends__id"))
self.assertEqual(vals, {"friends__id__count": 2})
books = Book.objects.annotate(
num_authors=Count("authors__name")).filter(
num_authors__exact=2).order_by("pk")
self.assertQuerysetEqual(books, [
"The Definitive Guide to Django: Web Development Done Right",
"Artificial Intelligence: A Modern Approach",
], lambda b: b.name)
authors = (Author.objects.annotate(
num_friends=Count("friends__id", distinct=True)).filter(
num_friends=0).order_by("pk"))
self.assertQuerysetEqual(authors, ['Brad Dayley'], lambda a: a.name)
publishers = Publisher.objects.annotate(
num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
self.assertQuerysetEqual(publishers, ['Apress', 'Prentice Hall'],
lambda p: p.name)
publishers = (Publisher.objects.filter(
book__price__lt=Decimal("40.0")).annotate(
num_books=Count("book__id")).filter(num_books__gt=1))
self.assertQuerysetEqual(publishers, ['Apress'], lambda p: p.name)
books = (Book.objects.annotate(
num_authors=Count("authors__id")).filter(
authors__name__contains="Norvig", num_authors__gt=1))
self.assertQuerysetEqual(
books, ['Artificial Intelligence: A Modern Approach'],
lambda b: b.name)
def test_order_by_aggregate(self):
authors = Author.objects.values('age').annotate(
age_count=Count('age')).order_by('age_count', 'age')
self.assertQuerysetEqual(authors, [
(25, 1),
(34, 1),
(35, 1),
(37, 1),
(45, 1),
(46, 1),
(57, 1),
(29, 2),
], lambda a: (a['age'], a['age_count']))
def test_dates_with_aggregation(self):
"""
.dates() returns a distinct set of dates when applied to a
QuerySet with aggregation.
Refs #18056. Previously, .dates() would return distinct (date_kind,
aggregation) sets, in this case (year, num_authors), so 2008 would be
returned twice because there are books from 2008 with a different
number of authors.
"""
dates = Book.objects.annotate(num_authors=Count("authors")).dates(
'pubdate', 'year')
self.assertQuerysetEqual(dates, [
"datetime.date(1991, 1, 1)", "datetime.date(1995, 1, 1)",
"datetime.date(2007, 1, 1)", "datetime.date(2008, 1, 1)"
])
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_ticket17424(self):
"""
Doing exclude() on a foreign model after annotate() doesn't crash.
"""
all_books = list(
Book.objects.values_list('pk', flat=True).order_by('pk'))
annotated_books = Book.objects.order_by('pk').annotate(one=Count("id"))
# The value doesn't matter, we just need any negative
# constraint on a related model that's a noop.
excluded_books = annotated_books.exclude(
publisher__name="__UNLIKELY_VALUE__")
# Try to generate query tree
str(excluded_books.query)
self.assertQuerysetEqual(excluded_books, all_books, lambda x: x.pk)
# Check internal state
self.assertIsNone(
annotated_books.query.alias_map["aggregation_book"].join_type)
self.assertIsNone(
excluded_books.query.alias_map["aggregation_book"].join_type)
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_annotate_exists(self):
authors = Author.objects.annotate(c=Count('id')).filter(c__gt=1)
self.assertFalse(authors.exists())
def test_count_star(self):
with self.assertNumQueries(1) as ctx:
Book.objects.aggregate(n=Count("*"))
sql = ctx.captured_queries[0]['sql']
self.assertIn('SELECT COUNT(*) ', sql)
def test_defer_or_only_with_annotate(self):
"Regression for #16409. Make sure defer() and only() work with annotate()"
self.assertIsInstance(list(City.objects.annotate(Count('point')).defer('name')), list)
self.assertIsInstance(list(City.objects.annotate(Count('point')).only('name')), list)
def test_sum_star_exception(self):
msg = 'Star cannot be used with filter. Please specify a field.'
with self.assertRaisesMessage(ValueError, msg):
Count('*', filter=Q(age=40))
def test_fkey_aggregate(self):
explicit = list(Author.objects.annotate(Count('book__id')))
implicit = list(Author.objects.annotate(Count('book')))
self.assertEqual(explicit, implicit)
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)})
def test_filtering(self):
p = Publisher.objects.create(name='Expensive Publisher', num_awards=0)
Book.objects.create(name='ExpensiveBook1',
pages=1,
isbn='111',
rating=3.5,
price=Decimal("1000"),
publisher=p,
contact_id=self.a1.id,
pubdate=datetime.date(2008, 12, 1))
Book.objects.create(name='ExpensiveBook2',
pages=1,
isbn='222',
rating=4.0,
price=Decimal("1000"),
publisher=p,
contact_id=self.a1.id,
pubdate=datetime.date(2008, 12, 2))
Book.objects.create(name='ExpensiveBook3',
pages=1,
isbn='333',
rating=4.5,
price=Decimal("35"),
publisher=p,
contact_id=self.a1.id,
pubdate=datetime.date(2008, 12, 3))
publishers = Publisher.objects.annotate(
num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
self.assertQuerysetEqual(
publishers,
['Apress', 'Prentice Hall', 'Expensive Publisher'],
lambda p: p.name,
)
publishers = Publisher.objects.filter(
book__price__lt=Decimal("40.0")).order_by("pk")
self.assertQuerysetEqual(publishers, [
"Apress",
"Apress",
"Sams",
"Prentice Hall",
"Expensive Publisher",
], lambda p: p.name)
publishers = (Publisher.objects.annotate(
num_books=Count("book__id")).filter(
num_books__gt=1,
book__price__lt=Decimal("40.0")).order_by("pk"))
self.assertQuerysetEqual(
publishers,
['Apress', 'Prentice Hall', 'Expensive Publisher'],
lambda p: p.name,
)
publishers = (Publisher.objects.filter(
book__price__lt=Decimal("40.0")).annotate(
num_books=Count("book__id")).filter(
num_books__gt=1).order_by("pk"))
self.assertQuerysetEqual(publishers, ['Apress'], lambda p: p.name)
publishers = Publisher.objects.annotate(
num_books=Count("book")).filter(
num_books__range=[1, 3]).order_by("pk")
self.assertQuerysetEqual(publishers, [
"Apress",
"Sams",
"Prentice Hall",
"Morgan Kaufmann",
"Expensive Publisher",
], lambda p: p.name)
publishers = Publisher.objects.annotate(
num_books=Count("book")).filter(
num_books__range=[1, 2]).order_by("pk")
self.assertQuerysetEqual(
publishers, ['Apress', 'Sams', 'Prentice Hall', 'Morgan Kaufmann'],
lambda p: p.name)
publishers = Publisher.objects.annotate(
num_books=Count("book")).filter(
num_books__in=[1, 3]).order_by("pk")
self.assertQuerysetEqual(
publishers,
['Sams', 'Morgan Kaufmann', 'Expensive Publisher'],
lambda p: p.name,
)
publishers = Publisher.objects.annotate(
num_books=Count("book")).filter(num_books__isnull=True)
self.assertEqual(len(publishers), 0)