| 1 |
import datetime |
| 2 |
from decimal import Decimal |
| 3 |
|
| 4 |
from django.db.models import ( |
| 5 |
Avg, |
| 6 |
Case, |
| 7 |
Count, |
| 8 |
Exists, |
| 9 |
F, |
| 10 |
Max, |
| 11 |
OuterRef, |
| 12 |
Q, |
| 13 |
StdDev, |
| 14 |
Subquery, |
| 15 |
Sum, |
| 16 |
Variance, |
| 17 |
When, |
| 18 |
) |
| 19 |
from django.test import TestCase |
| 20 |
from django.test.utils import Approximate |
| 21 |
|
| 22 |
from .models import Author, Book, Publisher |
| 23 |
|
| 24 |
|
| 25 |
class FilteredAggregateTests(TestCase): |
| 26 |
@classmethod |
| 27 |
def setUpTestData(cls): |
| 28 |
cls.a1 = Author.objects.create(name="test", age=40) |
| 29 |
cls.a2 = Author.objects.create(name="test2", age=60) |
| 30 |
cls.a3 = Author.objects.create(name="test3", age=100) |
| 31 |
cls.p1 = Publisher.objects.create( |
| 32 |
name="Apress", num_awards=3, duration=datetime.timedelta(days=1) |
| 33 |
) |
| 34 |
cls.b1 = Book.objects.create( |
| 35 |
isbn="159059725", |
| 36 |
name="The Definitive Guide to Django: Web Development Done Right", |
| 37 |
pages=447, |
| 38 |
rating=4.5, |
| 39 |
price=Decimal("30.00"), |
| 40 |
contact=cls.a1, |
| 41 |
publisher=cls.p1, |
| 42 |
pubdate=datetime.date(2007, 12, 6), |
| 43 |
) |
| 44 |
cls.b2 = Book.objects.create( |
| 45 |
isbn="067232959", |
| 46 |
name="Sams Teach Yourself Django in 24 Hours", |
| 47 |
pages=528, |
| 48 |
rating=3.0, |
| 49 |
price=Decimal("23.09"), |
| 50 |
contact=cls.a2, |
| 51 |
publisher=cls.p1, |
| 52 |
pubdate=datetime.date(2008, 3, 3), |
| 53 |
) |
| 54 |
cls.b3 = Book.objects.create( |
| 55 |
isbn="159059996", |
| 56 |
name="Practical Django Projects", |
| 57 |
pages=600, |
| 58 |
rating=4.5, |
| 59 |
price=Decimal("29.69"), |
| 60 |
contact=cls.a3, |
| 61 |
publisher=cls.p1, |
| 62 |
pubdate=datetime.date(2008, 6, 23), |
| 63 |
) |
| 64 |
cls.a1.friends.add(cls.a2) |
| 65 |
cls.a1.friends.add(cls.a3) |
| 66 |
cls.b1.authors.add(cls.a1) |
| 67 |
cls.b1.authors.add(cls.a3) |
| 68 |
cls.b2.authors.add(cls.a2) |
| 69 |
cls.b3.authors.add(cls.a3) |
| 70 |
|
| 71 |
def test_filtered_aggregates(self): |
| 72 |
agg = Sum("age", filter=Q(name__startswith="test")) |
| 73 |
self.assertEqual(Author.objects.aggregate(age=agg)["age"], 200) |
| 74 |
|
| 75 |
def test_filtered_numerical_aggregates(self): |
| 76 |
for aggregate, expected_result in ( |
| 77 |
(Avg, Approximate(66.7, 1)), |
| 78 |
(StdDev, Approximate(24.9, 1)), |
| 79 |
(Variance, Approximate(622.2, 1)), |
| 80 |
): |
| 81 |
with self.subTest(aggregate=aggregate.__name__): |
| 82 |
agg = aggregate("age", filter=Q(name__startswith="test")) |
| 83 |
self.assertEqual( |
| 84 |
Author.objects.aggregate(age=agg)["age"], expected_result |
| 85 |
) |
| 86 |
|
| 87 |
def test_empty_filtered_aggregates(self): |
| 88 |
agg = Count("pk", filter=Q()) |
| 89 |
self.assertEqual(Author.objects.aggregate(count=agg)["count"], 3) |
| 90 |
|
| 91 |
def test_empty_filtered_aggregates_with_annotation(self): |
| 92 |
agg = Count("pk", filter=Q()) |
| 93 |
self.assertEqual( |
| 94 |
Author.objects.annotate( |
| 95 |
age_annotation=F("age"), |
| 96 |
).aggregate( |
| 97 |
count=agg |
| 98 |
)["count"], |
| 99 |
3, |
| 100 |
) |
| 101 |
|
| 102 |
def test_double_filtered_aggregates(self): |
| 103 |
agg = Sum("age", filter=Q(Q(name="test2") & ~Q(name="test"))) |
| 104 |
self.assertEqual(Author.objects.aggregate(age=agg)["age"], 60) |
| 105 |
|
| 106 |
def test_excluded_aggregates(self): |
| 107 |
agg = Sum("age", filter=~Q(name="test2")) |
| 108 |
self.assertEqual(Author.objects.aggregate(age=agg)["age"], 140) |
| 109 |
|
| 110 |
def test_related_aggregates_m2m(self): |
| 111 |
agg = Sum("friends__age", filter=~Q(friends__name="test")) |
| 112 |
self.assertEqual( |
| 113 |
Author.objects.filter(name="test").aggregate(age=agg)["age"], 160 |
| 114 |
) |
| 115 |
|
| 116 |
def test_related_aggregates_m2m_and_fk(self): |
| 117 |
q = Q(friends__book__publisher__name="Apress") & ~Q(friends__name="test3") |
| 118 |
agg = Sum("friends__book__pages", filter=q) |
| 119 |
self.assertEqual( |
| 120 |
Author.objects.filter(name="test").aggregate(pages=agg)["pages"], 528 |
| 121 |
) |
| 122 |
|
| 123 |
def test_plain_annotate(self): |
| 124 |
agg = Sum("book__pages", filter=Q(book__rating__gt=3)) |
| 125 |
qs = Author.objects.annotate(pages=agg).order_by("pk") |
| 126 |
self.assertSequenceEqual([a.pages for a in qs], [447, None, 1047]) |
| 127 |
|
| 128 |
def test_filtered_aggregate_on_annotate(self): |
| 129 |
pages_annotate = Sum("book__pages", filter=Q(book__rating__gt=3)) |
| 130 |
age_agg = Sum("age", filter=Q(total_pages__gte=400)) |
| 131 |
aggregated = Author.objects.annotate(total_pages=pages_annotate).aggregate( |
| 132 |
summed_age=age_agg |
| 133 |
) |
| 134 |
self.assertEqual(aggregated, {"summed_age": 140}) |
| 135 |
|
| 136 |
def test_case_aggregate(self): |
| 137 |
agg = Sum( |
| 138 |
Case(When(friends__age=40, then=F("friends__age"))), |
| 139 |
filter=Q(friends__name__startswith="test"), |
| 140 |
) |
| 141 |
self.assertEqual(Author.objects.aggregate(age=agg)["age"], 80) |
| 142 |
|
| 143 |
def test_sum_star_exception(self): |
| 144 |
msg = "Star cannot be used with filter. Please specify a field." |
| 145 |
with self.assertRaisesMessage(ValueError, msg): |
| 146 |
Count("*", filter=Q(age=40)) |
| 147 |
|
| 148 |
def test_filtered_reused_subquery(self): |
| 149 |
qs = Author.objects.annotate( |
| 150 |
older_friends_count=Count("friends", filter=Q(friends__age__gt=F("age"))), |
| 151 |
).filter( |
| 152 |
older_friends_count__gte=2, |
| 153 |
) |
| 154 |
self.assertEqual(qs.get(pk__in=qs.values("pk")), self.a1) |
| 155 |
|
| 156 |
def test_filtered_aggregate_ref_annotation(self): |
| 157 |
aggs = Author.objects.annotate(double_age=F("age") * 2).aggregate( |
| 158 |
cnt=Count("pk", filter=Q(double_age__gt=100)), |
| 159 |
) |
| 160 |
self.assertEqual(aggs["cnt"], 2) |
| 161 |
|
| 162 |
def test_filtered_aggregate_ref_subquery_annotation(self): |
| 163 |
aggs = Author.objects.annotate( |
| 164 |
earliest_book_year=Subquery( |
| 165 |
Book.objects.filter( |
| 166 |
contact__pk=OuterRef("pk"), |
| 167 |
) |
| 168 |
.order_by("pubdate") |
| 169 |
.values("pubdate__year")[:1] |
| 170 |
), |
| 171 |
).aggregate( |
| 172 |
cnt=Count("pk", filter=Q(earliest_book_year=2008)), |
| 173 |
) |
| 174 |
self.assertEqual(aggs["cnt"], 2) |
| 175 |
|
| 176 |
def test_filtered_aggregate_ref_multiple_subquery_annotation(self): |
| 177 |
aggregate = ( |
| 178 |
Book.objects.values("publisher") |
| 179 |
.annotate( |
| 180 |
has_authors=Exists( |
| 181 |
Book.authors.through.objects.filter(book=OuterRef("pk")), |
| 182 |
), |
| 183 |
authors_have_other_books=Exists( |
| 184 |
Book.objects.filter( |
| 185 |
authors__in=Author.objects.filter( |
| 186 |
book_contact_set=OuterRef(OuterRef("pk")), |
| 187 |
) |
| 188 |
).exclude(pk=OuterRef("pk")), |
| 189 |
), |
| 190 |
) |
| 191 |
.aggregate( |
| 192 |
max_rating=Max( |
| 193 |
"rating", |
| 194 |
filter=Q(has_authors=True, authors_have_other_books=False), |
| 195 |
) |
| 196 |
) |
| 197 |
) |
| 198 |
self.assertEqual(aggregate, {"max_rating": 4.5}) |
| 199 |
|
| 200 |
def test_filtered_aggregrate_ref_in_subquery_annotation(self): |
| 201 |
aggs = ( |
| 202 |
Author.objects.annotate( |
| 203 |
count=Subquery( |
| 204 |
Book.objects.annotate( |
| 205 |
weird_count=Count( |
| 206 |
"pk", |
| 207 |
filter=Q(pages=OuterRef("age")), |
| 208 |
) |
| 209 |
).values("weird_count")[:1] |
| 210 |
), |
| 211 |
) |
| 212 |
.order_by("pk") |
| 213 |
.aggregate(sum=Sum("count")) |
| 214 |
) |
| 215 |
self.assertEqual(aggs["sum"], 0) |
| 216 |
|
| 217 |
def test_filtered_aggregate_on_exists(self): |
| 218 |
aggregate = Book.objects.values("publisher").aggregate( |
| 219 |
max_rating=Max( |
| 220 |
"rating", |
| 221 |
filter=Exists( |
| 222 |
Book.authors.through.objects.filter(book=OuterRef("pk")), |
| 223 |
), |
| 224 |
), |
| 225 |
) |
| 226 |
self.assertEqual(aggregate, {"max_rating": 4.5}) |
| 227 |
|
| 228 |
def test_filtered_aggregate_empty_condition(self): |
| 229 |
book = Book.objects.annotate( |
| 230 |
authors_count=Count( |
| 231 |
"authors", |
| 232 |
filter=Q(authors__in=[]), |
| 233 |
), |
| 234 |
).get(pk=self.b1.pk) |
| 235 |
self.assertEqual(book.authors_count, 0) |
| 236 |
aggregate = Book.objects.aggregate( |
| 237 |
max_rating=Max("rating", filter=Q(rating__in=[])) |
| 238 |
) |
| 239 |
self.assertEqual(aggregate, {"max_rating": None}) |
| 240 |
|
| 241 |
def test_filtered_aggregate_full_condition(self): |
| 242 |
book = Book.objects.annotate( |
| 243 |
authors_count=Count( |
| 244 |
"authors", |
| 245 |
filter=~Q(authors__in=[]), |
| 246 |
), |
| 247 |
).get(pk=self.b1.pk) |
| 248 |
self.assertEqual(book.authors_count, 2) |
| 249 |
aggregate = Book.objects.aggregate( |
| 250 |
max_rating=Max("rating", filter=~Q(rating__in=[])) |
| 251 |
) |
| 252 |
self.assertEqual(aggregate, {"max_rating": 4.5}) |