def test_function(self):
if settings.BACKEND == 'mysql':
# Explicit cast for MySQL with Coalesce and Datetime
# https://docs.djangoproject.com/en/2.1/ref/models/database-functions/#coalesce
annotation = {
'oldest_child_with_other':
Cast(
SubqueryMin(Coalesce('child__other_timestamp',
'child__timestamp'),
output_field=DateTimeField()), DateTimeField())
}
else:
annotation = {
'oldest_child_with_other':
SubqueryMin(Coalesce('child__other_timestamp',
'child__timestamp'),
output_field=DateTimeField())
}
parents = Parent.objects.filter(name='John').annotate(**annotation)
oldest_child = Child.objects.filter(parent__name='John').order_by(
Coalesce('other_timestamp', 'timestamp').asc())[0]
self.assertEqual(
parents[0].oldest_child_with_other, oldest_child.other_timestamp
or oldest_child.timestamp)
def test_aggregate_foreign_key(self):
bookauthors = BookAuthor.objects.annotate(
min_publisher_id=SubqueryMin('book__publisher_id'))
bookauthors = {
bookauthor.id: bookauthor.min_publisher_id
for bookauthor in bookauthors
}
self.assertEqual(bookauthors, {1: 1, 2: 1, 3: 1, 4: 2, 5: 2, 6: 2})
def test_reverse_foreign_key(self):
annotations = {
'max_price': SubqueryMax('package__purchase__price'),
'min_price': SubqueryMin('package__purchase__price')
}
catalogs = Catalog.objects.annotate(**annotations)
prices = {catalog.number: (catalog.max_price, catalog.min_price) for catalog in catalogs}
self.assertEqual(prices, {'A': (6, 4),
'B': (12, 11)})
def test_subquery_min(self):
annotation = {
'oldest_child_timestamp': SubqueryMin('child__timestamp',
output_field=DateTimeField())
}
parents = Parent.objects.filter(name='John').annotate(**annotation)
oldest_child = Child.objects.filter(parent__name='John').order_by('timestamp')[0]
self.assertEqual(parents[0].oldest_child_timestamp, oldest_child.timestamp)
def test_forward_and_reverse_foreign_keys(self):
annotations = {
'max_price': SubqueryMax('catalog__package__purchase__price'),
'min_price': SubqueryMin('catalog__package__purchase__price')
}
catalog_infos = CatalogInfo.objects.annotate(**annotations)
extremes = {info.info: (info.max_price, info.min_price) for info in catalog_infos}
self.assertEqual(extremes, {'cat A info': (6, 4),
'cat B info': (12, 11)})
def test_aggregate_foreign_key(self):
bookauthors = BookAuthor.objects.annotate(min_publisher_id=SubqueryMin('book__publisher_id'))
bookauthors = {bookauthor.id: bookauthor.min_publisher_id for bookauthor in bookauthors}
publisher1_id = Publisher.objects.get(name='Publisher 1').id
publisher2_id = Publisher.objects.get(name='Publisher 2').id
self.assertEqual(bookauthors, {1: publisher1_id,
2: publisher1_id,
3: publisher1_id,
4: publisher2_id,
5: publisher2_id,
6: publisher2_id})