|
1 import datetime |
|
2 from decimal import Decimal |
|
3 |
|
4 from django.db.models import Avg, Sum, Count, Max, Min |
|
5 from django.test import TestCase, Approximate |
|
6 |
|
7 from models import Author, Publisher, Book, Store |
|
8 |
|
9 |
|
10 class BaseAggregateTestCase(TestCase): |
|
11 fixtures = ["initial_data.json"] |
|
12 |
|
13 def test_empty_aggregate(self): |
|
14 self.assertEqual(Author.objects.all().aggregate(), {}) |
|
15 |
|
16 def test_single_aggregate(self): |
|
17 vals = Author.objects.aggregate(Avg("age")) |
|
18 self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)}) |
|
19 |
|
20 def test_multiple_aggregates(self): |
|
21 vals = Author.objects.aggregate(Sum("age"), Avg("age")) |
|
22 self.assertEqual(vals, {"age__sum": 337, "age__avg": Approximate(37.4, places=1)}) |
|
23 |
|
24 def test_filter_aggregate(self): |
|
25 vals = Author.objects.filter(age__gt=29).aggregate(Sum("age")) |
|
26 self.assertEqual(len(vals), 1) |
|
27 self.assertEqual(vals["age__sum"], 254) |
|
28 |
|
29 def test_related_aggregate(self): |
|
30 vals = Author.objects.aggregate(Avg("friends__age")) |
|
31 self.assertEqual(len(vals), 1) |
|
32 self.assertAlmostEqual(vals["friends__age__avg"], 34.07, places=2) |
|
33 |
|
34 vals = Book.objects.filter(rating__lt=4.5).aggregate(Avg("authors__age")) |
|
35 self.assertEqual(len(vals), 1) |
|
36 self.assertAlmostEqual(vals["authors__age__avg"], 38.2857, places=2) |
|
37 |
|
38 vals = Author.objects.all().filter(name__contains="a").aggregate(Avg("book__rating")) |
|
39 self.assertEqual(len(vals), 1) |
|
40 self.assertEqual(vals["book__rating__avg"], 4.0) |
|
41 |
|
42 vals = Book.objects.aggregate(Sum("publisher__num_awards")) |
|
43 self.assertEqual(len(vals), 1) |
|
44 self.assertEquals(vals["publisher__num_awards__sum"], 30) |
|
45 |
|
46 vals = Publisher.objects.aggregate(Sum("book__price")) |
|
47 self.assertEqual(len(vals), 1) |
|
48 self.assertEqual(vals["book__price__sum"], Decimal("270.27")) |
|
49 |
|
50 def test_aggregate_multi_join(self): |
|
51 vals = Store.objects.aggregate(Max("books__authors__age")) |
|
52 self.assertEqual(len(vals), 1) |
|
53 self.assertEqual(vals["books__authors__age__max"], 57) |
|
54 |
|
55 vals = Author.objects.aggregate(Min("book__publisher__num_awards")) |
|
56 self.assertEqual(len(vals), 1) |
|
57 self.assertEqual(vals["book__publisher__num_awards__min"], 1) |
|
58 |
|
59 def test_aggregate_alias(self): |
|
60 vals = Store.objects.filter(name="Amazon.com").aggregate(amazon_mean=Avg("books__rating")) |
|
61 self.assertEqual(len(vals), 1) |
|
62 self.assertAlmostEqual(vals["amazon_mean"], 4.08, places=2) |
|
63 |
|
64 def test_annotate_basic(self): |
|
65 self.assertQuerysetEqual( |
|
66 Book.objects.annotate().order_by('pk'), [ |
|
67 "The Definitive Guide to Django: Web Development Done Right", |
|
68 "Sams Teach Yourself Django in 24 Hours", |
|
69 "Practical Django Projects", |
|
70 "Python Web Development with Django", |
|
71 "Artificial Intelligence: A Modern Approach", |
|
72 "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp" |
|
73 ], |
|
74 lambda b: b.name |
|
75 ) |
|
76 |
|
77 books = Book.objects.annotate(mean_age=Avg("authors__age")) |
|
78 b = books.get(pk=1) |
|
79 self.assertEqual( |
|
80 b.name, |
|
81 u'The Definitive Guide to Django: Web Development Done Right' |
|
82 ) |
|
83 self.assertEqual(b.mean_age, 34.5) |
|
84 |
|
85 def test_annotate_m2m(self): |
|
86 books = Book.objects.filter(rating__lt=4.5).annotate(Avg("authors__age")).order_by("name") |
|
87 self.assertQuerysetEqual( |
|
88 books, [ |
|
89 (u'Artificial Intelligence: A Modern Approach', 51.5), |
|
90 (u'Practical Django Projects', 29.0), |
|
91 (u'Python Web Development with Django', Approximate(30.3, places=1)), |
|
92 (u'Sams Teach Yourself Django in 24 Hours', 45.0) |
|
93 ], |
|
94 lambda b: (b.name, b.authors__age__avg), |
|
95 ) |
|
96 |
|
97 books = Book.objects.annotate(num_authors=Count("authors")).order_by("name") |
|
98 self.assertQuerysetEqual( |
|
99 books, [ |
|
100 (u'Artificial Intelligence: A Modern Approach', 2), |
|
101 (u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp', 1), |
|
102 (u'Practical Django Projects', 1), |
|
103 (u'Python Web Development with Django', 3), |
|
104 (u'Sams Teach Yourself Django in 24 Hours', 1), |
|
105 (u'The Definitive Guide to Django: Web Development Done Right', 2) |
|
106 ], |
|
107 lambda b: (b.name, b.num_authors) |
|
108 ) |
|
109 |
|
110 def test_backwards_m2m_annotate(self): |
|
111 authors = Author.objects.filter(name__contains="a").annotate(Avg("book__rating")).order_by("name") |
|
112 self.assertQuerysetEqual( |
|
113 authors, [ |
|
114 (u'Adrian Holovaty', 4.5), |
|
115 (u'Brad Dayley', 3.0), |
|
116 (u'Jacob Kaplan-Moss', 4.5), |
|
117 (u'James Bennett', 4.0), |
|
118 (u'Paul Bissex', 4.0), |
|
119 (u'Stuart Russell', 4.0) |
|
120 ], |
|
121 lambda a: (a.name, a.book__rating__avg) |
|
122 ) |
|
123 |
|
124 authors = Author.objects.annotate(num_books=Count("book")).order_by("name") |
|
125 self.assertQuerysetEqual( |
|
126 authors, [ |
|
127 (u'Adrian Holovaty', 1), |
|
128 (u'Brad Dayley', 1), |
|
129 (u'Jacob Kaplan-Moss', 1), |
|
130 (u'James Bennett', 1), |
|
131 (u'Jeffrey Forcier', 1), |
|
132 (u'Paul Bissex', 1), |
|
133 (u'Peter Norvig', 2), |
|
134 (u'Stuart Russell', 1), |
|
135 (u'Wesley J. Chun', 1) |
|
136 ], |
|
137 lambda a: (a.name, a.num_books) |
|
138 ) |
|
139 |
|
140 def test_reverse_fkey_annotate(self): |
|
141 books = Book.objects.annotate(Sum("publisher__num_awards")).order_by("name") |
|
142 self.assertQuerysetEqual( |
|
143 books, [ |
|
144 (u'Artificial Intelligence: A Modern Approach', 7), |
|
145 (u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp', 9), |
|
146 (u'Practical Django Projects', 3), |
|
147 (u'Python Web Development with Django', 7), |
|
148 (u'Sams Teach Yourself Django in 24 Hours', 1), |
|
149 (u'The Definitive Guide to Django: Web Development Done Right', 3) |
|
150 ], |
|
151 lambda b: (b.name, b.publisher__num_awards__sum) |
|
152 ) |
|
153 |
|
154 publishers = Publisher.objects.annotate(Sum("book__price")).order_by("name") |
|
155 self.assertQuerysetEqual( |
|
156 publishers, [ |
|
157 (u'Apress', Decimal("59.69")), |
|
158 (u"Jonno's House of Books", None), |
|
159 (u'Morgan Kaufmann', Decimal("75.00")), |
|
160 (u'Prentice Hall', Decimal("112.49")), |
|
161 (u'Sams', Decimal("23.09")) |
|
162 ], |
|
163 lambda p: (p.name, p.book__price__sum) |
|
164 ) |
|
165 |
|
166 def test_annotate_values(self): |
|
167 books = list(Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values()) |
|
168 self.assertEqual( |
|
169 books, [ |
|
170 { |
|
171 "contact_id": 1, |
|
172 "id": 1, |
|
173 "isbn": "159059725", |
|
174 "mean_age": 34.5, |
|
175 "name": "The Definitive Guide to Django: Web Development Done Right", |
|
176 "pages": 447, |
|
177 "price": Approximate(Decimal("30")), |
|
178 "pubdate": datetime.date(2007, 12, 6), |
|
179 "publisher_id": 1, |
|
180 "rating": 4.5, |
|
181 } |
|
182 ] |
|
183 ) |
|
184 |
|
185 books = Book.objects.filter(pk=1).annotate(mean_age=Avg('authors__age')).values('pk', 'isbn', 'mean_age') |
|
186 self.assertEqual( |
|
187 list(books), [ |
|
188 { |
|
189 "pk": 1, |
|
190 "isbn": "159059725", |
|
191 "mean_age": 34.5, |
|
192 } |
|
193 ] |
|
194 ) |
|
195 |
|
196 books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values("name") |
|
197 self.assertEqual( |
|
198 list(books), [ |
|
199 { |
|
200 "name": "The Definitive Guide to Django: Web Development Done Right" |
|
201 } |
|
202 ] |
|
203 ) |
|
204 |
|
205 books = Book.objects.filter(pk=1).values().annotate(mean_age=Avg('authors__age')) |
|
206 self.assertEqual( |
|
207 list(books), [ |
|
208 { |
|
209 "contact_id": 1, |
|
210 "id": 1, |
|
211 "isbn": "159059725", |
|
212 "mean_age": 34.5, |
|
213 "name": "The Definitive Guide to Django: Web Development Done Right", |
|
214 "pages": 447, |
|
215 "price": Approximate(Decimal("30")), |
|
216 "pubdate": datetime.date(2007, 12, 6), |
|
217 "publisher_id": 1, |
|
218 "rating": 4.5, |
|
219 } |
|
220 ] |
|
221 ) |
|
222 |
|
223 books = Book.objects.values("rating").annotate(n_authors=Count("authors__id"), mean_age=Avg("authors__age")).order_by("rating") |
|
224 self.assertEqual( |
|
225 list(books), [ |
|
226 { |
|
227 "rating": 3.0, |
|
228 "n_authors": 1, |
|
229 "mean_age": 45.0, |
|
230 }, |
|
231 { |
|
232 "rating": 4.0, |
|
233 "n_authors": 6, |
|
234 "mean_age": Approximate(37.16, places=1) |
|
235 }, |
|
236 { |
|
237 "rating": 4.5, |
|
238 "n_authors": 2, |
|
239 "mean_age": 34.5, |
|
240 }, |
|
241 { |
|
242 "rating": 5.0, |
|
243 "n_authors": 1, |
|
244 "mean_age": 57.0, |
|
245 } |
|
246 ] |
|
247 ) |
|
248 |
|
249 authors = Author.objects.annotate(Avg("friends__age")).order_by("name") |
|
250 self.assertEqual(len(authors), 9) |
|
251 self.assertQuerysetEqual( |
|
252 authors, [ |
|
253 (u'Adrian Holovaty', 32.0), |
|
254 (u'Brad Dayley', None), |
|
255 (u'Jacob Kaplan-Moss', 29.5), |
|
256 (u'James Bennett', 34.0), |
|
257 (u'Jeffrey Forcier', 27.0), |
|
258 (u'Paul Bissex', 31.0), |
|
259 (u'Peter Norvig', 46.0), |
|
260 (u'Stuart Russell', 57.0), |
|
261 (u'Wesley J. Chun', Approximate(33.66, places=1)) |
|
262 ], |
|
263 lambda a: (a.name, a.friends__age__avg) |
|
264 ) |
|
265 |
|
266 def test_count(self): |
|
267 vals = Book.objects.aggregate(Count("rating")) |
|
268 self.assertEqual(vals, {"rating__count": 6}) |
|
269 |
|
270 vals = Book.objects.aggregate(Count("rating", distinct=True)) |
|
271 self.assertEqual(vals, {"rating__count": 4}) |
|
272 |
|
273 def test_fkey_aggregate(self): |
|
274 explicit = list(Author.objects.annotate(Count('book__id'))) |
|
275 implicit = list(Author.objects.annotate(Count('book'))) |
|
276 self.assertEqual(explicit, implicit) |
|
277 |
|
278 def test_annotate_ordering(self): |
|
279 books = Book.objects.values('rating').annotate(oldest=Max('authors__age')).order_by('oldest', 'rating') |
|
280 self.assertEqual( |
|
281 list(books), [ |
|
282 { |
|
283 "rating": 4.5, |
|
284 "oldest": 35, |
|
285 }, |
|
286 { |
|
287 "rating": 3.0, |
|
288 "oldest": 45 |
|
289 }, |
|
290 { |
|
291 "rating": 4.0, |
|
292 "oldest": 57, |
|
293 }, |
|
294 { |
|
295 "rating": 5.0, |
|
296 "oldest": 57, |
|
297 } |
|
298 ] |
|
299 ) |
|
300 |
|
301 books = Book.objects.values("rating").annotate(oldest=Max("authors__age")).order_by("-oldest", "-rating") |
|
302 self.assertEqual( |
|
303 list(books), [ |
|
304 { |
|
305 "rating": 5.0, |
|
306 "oldest": 57, |
|
307 }, |
|
308 { |
|
309 "rating": 4.0, |
|
310 "oldest": 57, |
|
311 }, |
|
312 { |
|
313 "rating": 3.0, |
|
314 "oldest": 45, |
|
315 }, |
|
316 { |
|
317 "rating": 4.5, |
|
318 "oldest": 35, |
|
319 } |
|
320 ] |
|
321 ) |
|
322 |
|
323 def test_aggregate_annotation(self): |
|
324 vals = Book.objects.annotate(num_authors=Count("authors__id")).aggregate(Avg("num_authors")) |
|
325 self.assertEqual(vals, {"num_authors__avg": Approximate(1.66, places=1)}) |
|
326 |
|
327 def test_filtering(self): |
|
328 p = Publisher.objects.create(name='Expensive Publisher', num_awards=0) |
|
329 Book.objects.create( |
|
330 name='ExpensiveBook1', |
|
331 pages=1, |
|
332 isbn='111', |
|
333 rating=3.5, |
|
334 price=Decimal("1000"), |
|
335 publisher=p, |
|
336 contact_id=1, |
|
337 pubdate=datetime.date(2008,12,1) |
|
338 ) |
|
339 Book.objects.create( |
|
340 name='ExpensiveBook2', |
|
341 pages=1, |
|
342 isbn='222', |
|
343 rating=4.0, |
|
344 price=Decimal("1000"), |
|
345 publisher=p, |
|
346 contact_id=1, |
|
347 pubdate=datetime.date(2008,12,2) |
|
348 ) |
|
349 Book.objects.create( |
|
350 name='ExpensiveBook3', |
|
351 pages=1, |
|
352 isbn='333', |
|
353 rating=4.5, |
|
354 price=Decimal("35"), |
|
355 publisher=p, |
|
356 contact_id=1, |
|
357 pubdate=datetime.date(2008,12,3) |
|
358 ) |
|
359 |
|
360 publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk") |
|
361 self.assertQuerysetEqual( |
|
362 publishers, [ |
|
363 "Apress", |
|
364 "Prentice Hall", |
|
365 "Expensive Publisher", |
|
366 ], |
|
367 lambda p: p.name, |
|
368 ) |
|
369 |
|
370 publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).order_by("pk") |
|
371 self.assertQuerysetEqual( |
|
372 publishers, [ |
|
373 "Apress", |
|
374 "Apress", |
|
375 "Sams", |
|
376 "Prentice Hall", |
|
377 "Expensive Publisher", |
|
378 ], |
|
379 lambda p: p.name |
|
380 ) |
|
381 |
|
382 publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1, book__price__lt=Decimal("40.0")).order_by("pk") |
|
383 self.assertQuerysetEqual( |
|
384 publishers, [ |
|
385 "Apress", |
|
386 "Prentice Hall", |
|
387 "Expensive Publisher", |
|
388 ], |
|
389 lambda p: p.name, |
|
390 ) |
|
391 |
|
392 publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk") |
|
393 self.assertQuerysetEqual( |
|
394 publishers, [ |
|
395 "Apress", |
|
396 ], |
|
397 lambda p: p.name |
|
398 ) |
|
399 |
|
400 publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__range=[1, 3]).order_by("pk") |
|
401 self.assertQuerysetEqual( |
|
402 publishers, [ |
|
403 "Apress", |
|
404 "Sams", |
|
405 "Prentice Hall", |
|
406 "Morgan Kaufmann", |
|
407 "Expensive Publisher", |
|
408 ], |
|
409 lambda p: p.name |
|
410 ) |
|
411 |
|
412 publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__range=[1, 2]).order_by("pk") |
|
413 self.assertQuerysetEqual( |
|
414 publishers, [ |
|
415 "Apress", |
|
416 "Sams", |
|
417 "Prentice Hall", |
|
418 "Morgan Kaufmann", |
|
419 ], |
|
420 lambda p: p.name |
|
421 ) |
|
422 |
|
423 publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__in=[1, 3]).order_by("pk") |
|
424 self.assertQuerysetEqual( |
|
425 publishers, [ |
|
426 "Sams", |
|
427 "Morgan Kaufmann", |
|
428 "Expensive Publisher", |
|
429 ], |
|
430 lambda p: p.name, |
|
431 ) |
|
432 |
|
433 publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__isnull=True) |
|
434 self.assertEqual(len(publishers), 0) |
|
435 |
|
436 def test_annotation(self): |
|
437 vals = Author.objects.filter(pk=1).aggregate(Count("friends__id")) |
|
438 self.assertEqual(vals, {"friends__id__count": 2}) |
|
439 |
|
440 books = Book.objects.annotate(num_authors=Count("authors__name")).filter(num_authors__ge=2).order_by("pk") |
|
441 self.assertQuerysetEqual( |
|
442 books, [ |
|
443 "The Definitive Guide to Django: Web Development Done Right", |
|
444 "Artificial Intelligence: A Modern Approach", |
|
445 ], |
|
446 lambda b: b.name |
|
447 ) |
|
448 |
|
449 authors = Author.objects.annotate(num_friends=Count("friends__id", distinct=True)).filter(num_friends=0).order_by("pk") |
|
450 self.assertQuerysetEqual( |
|
451 authors, [ |
|
452 "Brad Dayley", |
|
453 ], |
|
454 lambda a: a.name |
|
455 ) |
|
456 |
|
457 publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk") |
|
458 self.assertQuerysetEqual( |
|
459 publishers, [ |
|
460 "Apress", |
|
461 "Prentice Hall", |
|
462 ], |
|
463 lambda p: p.name |
|
464 ) |
|
465 |
|
466 publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).annotate(num_books=Count("book__id")).filter(num_books__gt=1) |
|
467 self.assertQuerysetEqual( |
|
468 publishers, [ |
|
469 "Apress", |
|
470 ], |
|
471 lambda p: p.name |
|
472 ) |
|
473 |
|
474 books = Book.objects.annotate(num_authors=Count("authors__id")).filter(authors__name__contains="Norvig", num_authors__gt=1) |
|
475 self.assertQuerysetEqual( |
|
476 books, [ |
|
477 "Artificial Intelligence: A Modern Approach", |
|
478 ], |
|
479 lambda b: b.name |
|
480 ) |
|
481 |
|
482 def test_more_aggregation(self): |
|
483 a = Author.objects.get(name__contains='Norvig') |
|
484 b = Book.objects.get(name__contains='Done Right') |
|
485 b.authors.add(a) |
|
486 b.save() |
|
487 |
|
488 vals = Book.objects.annotate(num_authors=Count("authors__id")).filter(authors__name__contains="Norvig", num_authors__gt=1).aggregate(Avg("rating")) |
|
489 self.assertEqual(vals, {"rating__avg": 4.25}) |
|
490 |
|
491 def test_even_more_aggregate(self): |
|
492 publishers = Publisher.objects.annotate(earliest_book=Min("book__pubdate")).exclude(earliest_book=None).order_by("earliest_book").values() |
|
493 self.assertEqual( |
|
494 list(publishers), [ |
|
495 { |
|
496 'earliest_book': datetime.date(1991, 10, 15), |
|
497 'num_awards': 9, |
|
498 'id': 4, |
|
499 'name': u'Morgan Kaufmann' |
|
500 }, |
|
501 { |
|
502 'earliest_book': datetime.date(1995, 1, 15), |
|
503 'num_awards': 7, |
|
504 'id': 3, |
|
505 'name': u'Prentice Hall' |
|
506 }, |
|
507 { |
|
508 'earliest_book': datetime.date(2007, 12, 6), |
|
509 'num_awards': 3, |
|
510 'id': 1, |
|
511 'name': u'Apress' |
|
512 }, |
|
513 { |
|
514 'earliest_book': datetime.date(2008, 3, 3), |
|
515 'num_awards': 1, |
|
516 'id': 2, |
|
517 'name': u'Sams' |
|
518 } |
|
519 ] |
|
520 ) |
|
521 |
|
522 vals = Store.objects.aggregate(Max("friday_night_closing"), Min("original_opening")) |
|
523 self.assertEqual( |
|
524 vals, |
|
525 { |
|
526 "friday_night_closing__max": datetime.time(23, 59, 59), |
|
527 "original_opening__min": datetime.datetime(1945, 4, 25, 16, 24, 14), |
|
528 } |
|
529 ) |
|
530 |
|
531 def test_annotate_values_list(self): |
|
532 books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("pk", "isbn", "mean_age") |
|
533 self.assertEqual( |
|
534 list(books), [ |
|
535 (1, "159059725", 34.5), |
|
536 ] |
|
537 ) |
|
538 |
|
539 books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("isbn") |
|
540 self.assertEqual( |
|
541 list(books), [ |
|
542 ('159059725',) |
|
543 ] |
|
544 ) |
|
545 |
|
546 books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("mean_age") |
|
547 self.assertEqual( |
|
548 list(books), [ |
|
549 (34.5,) |
|
550 ] |
|
551 ) |
|
552 |
|
553 books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("mean_age", flat=True) |
|
554 self.assertEqual(list(books), [34.5]) |
|
555 |
|
556 books = Book.objects.values_list("price").annotate(count=Count("price")).order_by("-count", "price") |
|
557 self.assertEqual( |
|
558 list(books), [ |
|
559 (Decimal("29.69"), 2), |
|
560 (Decimal('23.09'), 1), |
|
561 (Decimal('30'), 1), |
|
562 (Decimal('75'), 1), |
|
563 (Decimal('82.8'), 1), |
|
564 ] |
|
565 ) |