PageRenderTime 274ms CodeModel.GetById 60ms app.highlight 145ms RepoModel.GetById 56ms app.codeStats 7ms

/tests/modeltests/aggregation/tests.py

https://code.google.com/p/mango-py/
Python | 565 lines | 505 code | 60 blank | 0 comment | 0 complexity | e321fb3df01e7ab4d3c16e7423d7883c MD5 | raw file
  1import datetime
  2from decimal import Decimal
  3
  4from django.db.models import Avg, Sum, Count, Max, Min
  5from django.test import TestCase, Approximate
  6
  7from models import Author, Publisher, Book, Store
  8
  9
 10class 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.assertEqual(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        )