Debian Codegraph @

tests/aggregation/test_filter_argument.py

raw
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})