/tests/regressiontests/aggregation_regress/tests.py

  1import datetime
  2import pickle
  3from decimal import Decimal
  4from operator import attrgetter
  5
  6from django.core.exceptions import FieldError
  7from django.db.models import Count, Max, Avg, Sum, StdDev, Variance, F, Q
  8from django.test import TestCase, Approximate, skipUnlessDBFeature
  9
 10from models import Author, Book, Publisher, Clues, Entries, HardbackBook
 11
 12
 13class AggregationTests(TestCase):
 14    def assertObjectAttrs(self, obj, **kwargs):
 15        for attr, value in kwargs.iteritems():
 16            self.assertEqual(getattr(obj, attr), value)
 17
 18    def test_aggregates_in_where_clause(self):
 19        """
 20        Regression test for #12822: DatabaseError: aggregates not allowed in
 21        WHERE clause
 22
 23        Tests that the subselect works and returns results equivalent to a
 24        query with the IDs listed.
 25
 26        Before the corresponding fix for this bug, this test passed in 1.1 and
 27        failed in 1.2-beta (trunk).
 28        """
 29        qs = Book.objects.values('contact').annotate(Max('id'))
 30        qs = qs.order_by('contact').values_list('id__max', flat=True)
 31        # don't do anything with the queryset (qs) before including it as a
 32        # subquery
 33        books = Book.objects.order_by('id')
 34        qs1 = books.filter(id__in=qs)
 35        qs2 = books.filter(id__in=list(qs))
 36        self.assertEqual(list(qs1), list(qs2))
 37
 38    def test_aggregates_in_where_clause_pre_eval(self):
 39        """
 40        Regression test for #12822: DatabaseError: aggregates not allowed in
 41        WHERE clause
 42
 43        Same as the above test, but evaluates the queryset for the subquery
 44        before it's used as a subquery.
 45
 46        Before the corresponding fix for this bug, this test failed in both
 47        1.1 and 1.2-beta (trunk).
 48        """
 49        qs = Book.objects.values('contact').annotate(Max('id'))
 50        qs = qs.order_by('contact').values_list('id__max', flat=True)
 51        # force the queryset (qs) for the subquery to be evaluated in its
 52        # current state
 53        list(qs)
 54        books = Book.objects.order_by('id')
 55        qs1 = books.filter(id__in=qs)
 56        qs2 = books.filter(id__in=list(qs))
 57        self.assertEqual(list(qs1), list(qs2))
 58
 59    @skipUnlessDBFeature('supports_subqueries_in_group_by')
 60    def test_annotate_with_extra(self):
 61        """
 62        Regression test for #11916: Extra params + aggregation creates
 63        incorrect SQL.
 64        """
 65        #oracle doesn't support subqueries in group by clause
 66        shortest_book_sql = """
 67        SELECT name
 68        FROM aggregation_regress_book b
 69        WHERE b.publisher_id = aggregation_regress_publisher.id
 70        ORDER BY b.pages
 71        LIMIT 1
 72        """
 73        # tests that this query does not raise a DatabaseError due to the full
 74        # subselect being (erroneously) added to the GROUP BY parameters
 75        qs = Publisher.objects.extra(select={
 76            'name_of_shortest_book': shortest_book_sql,
 77        }).annotate(total_books=Count('book'))
 78        # force execution of the query
 79        list(qs)
 80
 81    def test_aggregate(self):
 82        # Ordering requests are ignored
 83        self.assertEqual(
 84            Author.objects.order_by("name").aggregate(Avg("age")),
 85            {"age__avg": Approximate(37.444, places=1)}
 86        )
 87
 88        # Implicit ordering is also ignored
 89        self.assertEqual(
 90            Book.objects.aggregate(Sum("pages")),
 91            {"pages__sum": 3703},
 92        )
 93
 94        # Baseline results
 95        self.assertEqual(
 96            Book.objects.aggregate(Sum('pages'), Avg('pages')),
 97            {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}
 98        )
 99
100        # Empty values query doesn't affect grouping or results
101        self.assertEqual(
102            Book.objects.values().aggregate(Sum('pages'), Avg('pages')),
103            {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}
104        )
105
106        # Aggregate overrides extra selected column
107        self.assertEqual(
108            Book.objects.extra(select={'price_per_page' : 'price / pages'}).aggregate(Sum('pages')),
109            {'pages__sum': 3703}
110        )
111
112    def test_annotation(self):
113        # Annotations get combined with extra select clauses
114        obj = Book.objects.annotate(mean_auth_age=Avg("authors__age")).extra(select={"manufacture_cost": "price * .5"}).get(pk=2)
115        self.assertObjectAttrs(obj,
116            contact_id=3,
117            id=2,
118            isbn=u'067232959',
119            mean_auth_age=45.0,
120            name='Sams Teach Yourself Django in 24 Hours',
121            pages=528,
122            price=Decimal("23.09"),
123            pubdate=datetime.date(2008, 3, 3),
124            publisher_id=2,
125            rating=3.0
126        )
127        # Different DB backends return different types for the extra select computation
128        self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))
129
130        # Order of the annotate/extra in the query doesn't matter
131        obj = Book.objects.extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2)
132        self.assertObjectAttrs(obj,
133            contact_id=3,
134            id=2,
135            isbn=u'067232959',
136            mean_auth_age=45.0,
137            name=u'Sams Teach Yourself Django in 24 Hours',
138            pages=528,
139            price=Decimal("23.09"),
140            pubdate=datetime.date(2008, 3, 3),
141            publisher_id=2,
142            rating=3.0
143        )
144        # Different DB backends return different types for the extra select computation
145        self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))
146
147        # Values queries can be combined with annotate and extra
148        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2)
149        manufacture_cost = obj['manufacture_cost']
150        self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))
151        del obj['manufacture_cost']
152        self.assertEqual(obj, {
153            "contact_id": 3,
154            "id": 2,
155            "isbn": u"067232959",
156            "mean_auth_age": 45.0,
157            "name": u"Sams Teach Yourself Django in 24 Hours",
158            "pages": 528,
159            "price": Decimal("23.09"),
160            "pubdate": datetime.date(2008, 3, 3),
161            "publisher_id": 2,
162            "rating": 3.0,
163        })
164
165        # The order of the (empty) values, annotate and extra clauses doesn't
166        # matter
167        obj = Book.objects.values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2)
168        manufacture_cost = obj['manufacture_cost']
169        self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))
170        del obj['manufacture_cost']
171        self.assertEqual(obj, {
172            'contact_id': 3,
173            'id': 2,
174            'isbn': u'067232959',
175            'mean_auth_age': 45.0,
176            'name': u'Sams Teach Yourself Django in 24 Hours',
177            'pages': 528,
178            'price': Decimal("23.09"),
179            'pubdate': datetime.date(2008, 3, 3),
180            'publisher_id': 2,
181            'rating': 3.0
182        })
183
184        # If the annotation precedes the values clause, it won't be included
185        # unless it is explicitly named
186        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name').get(pk=1)
187        self.assertEqual(obj, {
188            "name": u'The Definitive Guide to Django: Web Development Done Right',
189        })
190
191        obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name','mean_auth_age').get(pk=1)
192        self.assertEqual(obj, {
193            'mean_auth_age': 34.5,
194            'name': u'The Definitive Guide to Django: Web Development Done Right',
195        })
196
197        # If an annotation isn't included in the values, it can still be used
198        # in a filter
199        qs = Book.objects.annotate(n_authors=Count('authors')).values('name').filter(n_authors__gt=2)
200        self.assertQuerysetEqual(
201            qs, [
202                {"name": u'Python Web Development with Django'}
203            ],
204            lambda b: b,
205        )
206
207        # The annotations are added to values output if values() precedes
208        # annotate()
209        obj = Book.objects.values('name').annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).get(pk=1)
210        self.assertEqual(obj, {
211            'mean_auth_age': 34.5,
212            'name': u'The Definitive Guide to Django: Web Development Done Right',
213        })
214
215        # Check that all of the objects are getting counted (allow_nulls) and
216        # that values respects the amount of objects
217        self.assertEqual(
218            len(Author.objects.annotate(Avg('friends__age')).values()),
219            9
220        )
221
222        # Check that consecutive calls to annotate accumulate in the query
223        qs = Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards'))
224        self.assertQuerysetEqual(
225            qs, [
226                {'price': Decimal("30"), 'oldest': 35, 'publisher__num_awards__max': 3},
227                {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7},
228                {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1},
229                {'price': Decimal("75"), 'oldest': 57, 'publisher__num_awards__max': 9},
230                {'price': Decimal("82.8"), 'oldest': 57, 'publisher__num_awards__max': 7}
231            ],
232            lambda b: b,
233        )
234
235    def test_aggrate_annotation(self):
236        # Aggregates can be composed over annotations.
237        # The return type is derived from the composed aggregate
238        vals = Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('pages'), Max('price'), Sum('num_authors'), Avg('num_authors'))
239        self.assertEqual(vals, {
240            'num_authors__sum': 10,
241            'num_authors__avg': Approximate(1.666, places=2),
242            'pages__max': 1132,
243            'price__max': Decimal("82.80")
244        })
245
246    def test_field_error(self):
247        # Bad field requests in aggregates are caught and reported
248        self.assertRaises(
249            FieldError,
250            lambda: Book.objects.all().aggregate(num_authors=Count('foo'))
251        )
252
253        self.assertRaises(
254            FieldError,
255            lambda: Book.objects.all().annotate(num_authors=Count('foo'))
256        )
257
258        self.assertRaises(
259            FieldError,
260            lambda: Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('foo'))
261        )
262
263    def test_more(self):
264        # Old-style count aggregations can be mixed with new-style
265        self.assertEqual(
266            Book.objects.annotate(num_authors=Count('authors')).count(),
267            6
268        )
269
270        # Non-ordinal, non-computed Aggregates over annotations correctly
271        # inherit the annotation's internal type if the annotation is ordinal
272        # or computed
273        vals = Book.objects.annotate(num_authors=Count('authors')).aggregate(Max('num_authors'))
274        self.assertEqual(
275            vals,
276            {'num_authors__max': 3}
277        )
278
279        vals = Publisher.objects.annotate(avg_price=Avg('book__price')).aggregate(Max('avg_price'))
280        self.assertEqual(
281            vals,
282            {'avg_price__max': 75.0}
283        )
284
285        # Aliases are quoted to protected aliases that might be reserved names
286        vals = Book.objects.aggregate(number=Max('pages'), select=Max('pages'))
287        self.assertEqual(
288            vals,
289            {'number': 1132, 'select': 1132}
290        )
291
292        # Regression for #10064: select_related() plays nice with aggregates
293        obj = Book.objects.select_related('publisher').annotate(num_authors=Count('authors')).values()[0]
294        self.assertEqual(obj, {
295            'contact_id': 8,
296            'id': 5,
297            'isbn': u'013790395',
298            'name': u'Artificial Intelligence: A Modern Approach',
299            'num_authors': 2,
300            'pages': 1132,
301            'price': Decimal("82.8"),
302            'pubdate': datetime.date(1995, 1, 15),
303            'publisher_id': 3,
304            'rating': 4.0,
305        })
306
307        # Regression for #10010: exclude on an aggregate field is correctly
308        # negated
309        self.assertEqual(
310            len(Book.objects.annotate(num_authors=Count('authors'))),
311            6
312        )
313        self.assertEqual(
314            len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=2)),
315            1
316        )
317        self.assertEqual(
318            len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__gt=2)),
319            5
320        )
321
322        self.assertEqual(
323            len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__lt=3).exclude(num_authors__lt=2)),
324            2
325        )
326        self.assertEqual(
327            len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__lt=2).filter(num_authors__lt=3)),
328            2
329        )
330
331    def test_aggregate_fexpr(self):
332        # Aggregates can be used with F() expressions
333        # ... where the F() is pushed into the HAVING clause
334        qs = Publisher.objects.annotate(num_books=Count('book')).filter(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
335        self.assertQuerysetEqual(
336            qs, [
337                {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},
338                {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}
339            ],
340            lambda p: p,
341        )
342
343        qs = Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
344        self.assertQuerysetEqual(
345            qs, [
346                {'num_books': 2, 'name': u'Apress', 'num_awards': 3},
347                {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},
348                {'num_books': 1, 'name': u'Sams', 'num_awards': 1}
349            ],
350            lambda p: p,
351        )
352
353        # ... and where the F() references an aggregate
354        qs = Publisher.objects.annotate(num_books=Count('book')).filter(num_awards__gt=2*F('num_books')).order_by('name').values('name','num_books','num_awards')
355        self.assertQuerysetEqual(
356            qs, [
357                {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},
358                {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}
359            ],
360            lambda p: p,
361        )
362
363        qs = Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
364        self.assertQuerysetEqual(
365            qs, [
366                {'num_books': 2, 'name': u'Apress', 'num_awards': 3},
367                {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},
368                {'num_books': 1, 'name': u'Sams', 'num_awards': 1}
369            ],
370            lambda p: p,
371        )
372
373    def test_db_col_table(self):
374        # Tests on fields with non-default table and column names.
375        qs = Clues.objects.values('EntryID__Entry').annotate(Appearances=Count('EntryID'), Distinct_Clues=Count('Clue', distinct=True))
376        self.assertQuerysetEqual(qs, [])
377
378        qs = Entries.objects.annotate(clue_count=Count('clues__ID'))
379        self.assertQuerysetEqual(qs, [])
380
381    def test_empty(self):
382        # Regression for #10089: Check handling of empty result sets with
383        # aggregates
384        self.assertEqual(
385            Book.objects.filter(id__in=[]).count(),
386            0
387        )
388
389        vals = Book.objects.filter(id__in=[]).aggregate(num_authors=Count('authors'), avg_authors=Avg('authors'), max_authors=Max('authors'), max_price=Max('price'), max_rating=Max('rating'))
390        self.assertEqual(
391            vals,
392            {'max_authors': None, 'max_rating': None, 'num_authors': 0, 'avg_authors': None, 'max_price': None}
393        )
394
395        qs = Publisher.objects.filter(pk=5).annotate(num_authors=Count('book__authors'), avg_authors=Avg('book__authors'), max_authors=Max('book__authors'), max_price=Max('book__price'), max_rating=Max('book__rating')).values()
396        self.assertQuerysetEqual(
397            qs, [
398                {'max_authors': None, 'name': u"Jonno's House of Books", 'num_awards': 0, 'max_price': None, 'num_authors': 0, 'max_rating': None, 'id': 5, 'avg_authors': None}
399            ],
400            lambda p: p
401        )
402
403    def test_more_more(self):
404        # Regression for #10113 - Fields mentioned in order_by() must be
405        # included in the GROUP BY. This only becomes a problem when the
406        # order_by introduces a new join.
407        self.assertQuerysetEqual(
408            Book.objects.annotate(num_authors=Count('authors')).order_by('publisher__name', 'name'), [
409                "Practical Django Projects",
410                "The Definitive Guide to Django: Web Development Done Right",
411                "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp",
412                "Artificial Intelligence: A Modern Approach",
413                "Python Web Development with Django",
414                "Sams Teach Yourself Django in 24 Hours",
415            ],
416            lambda b: b.name
417        )
418
419        # Regression for #10127 - Empty select_related() works with annotate
420        qs = Book.objects.filter(rating__lt=4.5).select_related().annotate(Avg('authors__age'))
421        self.assertQuerysetEqual(
422            qs, [
423                (u'Artificial Intelligence: A Modern Approach', 51.5, u'Prentice Hall', u'Peter Norvig'),
424                (u'Practical Django Projects', 29.0, u'Apress', u'James Bennett'),
425                (u'Python Web Development with Django', Approximate(30.333, places=2), u'Prentice Hall', u'Jeffrey Forcier'),
426                (u'Sams Teach Yourself Django in 24 Hours', 45.0, u'Sams', u'Brad Dayley')
427            ],
428            lambda b: (b.name, b.authors__age__avg, b.publisher.name, b.contact.name)
429        )
430
431        # Regression for #10132 - If the values() clause only mentioned extra
432        # (select=) columns, those columns are used for grouping
433        qs = Book.objects.extra(select={'pub':'publisher_id'}).values('pub').annotate(Count('id')).order_by('pub')
434        self.assertQuerysetEqual(
435            qs, [
436                {'pub': 1, 'id__count': 2},
437                {'pub': 2, 'id__count': 1},
438                {'pub': 3, 'id__count': 2},
439                {'pub': 4, 'id__count': 1}
440            ],
441            lambda b: b
442        )
443
444        qs = Book.objects.extra(select={'pub':'publisher_id', 'foo':'pages'}).values('pub').annotate(Count('id')).order_by('pub')
445        self.assertQuerysetEqual(
446            qs, [
447                {'pub': 1, 'id__count': 2},
448                {'pub': 2, 'id__count': 1},
449                {'pub': 3, 'id__count': 2},
450                {'pub': 4, 'id__count': 1}
451            ],
452            lambda b: b
453        )
454
455        # Regression for #10182 - Queries with aggregate calls are correctly
456        # realiased when used in a subquery
457        ids = Book.objects.filter(pages__gt=100).annotate(n_authors=Count('authors')).filter(n_authors__gt=2).order_by('n_authors')
458        self.assertQuerysetEqual(
459            Book.objects.filter(id__in=ids), [
460                "Python Web Development with Django",
461            ],
462            lambda b: b.name
463        )
464
465    def test_duplicate_alias(self):
466        # Regression for #11256 - duplicating a default alias raises ValueError.
467        self.assertRaises(ValueError, Book.objects.all().annotate, Avg('authors__age'), authors__age__avg=Avg('authors__age'))
468
469    def test_field_name_conflict(self):
470        # Regression for #11256 - providing an aggregate name that conflicts with a field name on the model raises ValueError
471        self.assertRaises(ValueError, Author.objects.annotate, age=Avg('friends__age'))
472
473    def test_m2m_name_conflict(self):
474        # Regression for #11256 - providing an aggregate name that conflicts with an m2m name on the model raises ValueError
475        self.assertRaises(ValueError, Author.objects.annotate, friends=Count('friends'))
476
477    def test_values_queryset_non_conflict(self):
478        # Regression for #14707 -- If you're using a values query set, some potential conflicts are avoided.
479
480        # age is a field on Author, so it shouldn't be allowed as an aggregate.
481        # But age isn't included in the ValuesQuerySet, so it is.
482        results = Author.objects.values('name').annotate(age=Count('book_contact_set')).order_by('name')
483        self.assertEqual(len(results), 9)
484        self.assertEqual(results[0]['name'], u'Adrian Holovaty')
485        self.assertEqual(results[0]['age'], 1)
486
487        # Same problem, but aggregating over m2m fields
488        results = Author.objects.values('name').annotate(age=Avg('friends__age')).order_by('name')
489        self.assertEqual(len(results), 9)
490        self.assertEqual(results[0]['name'], u'Adrian Holovaty')
491        self.assertEqual(results[0]['age'], 32.0)
492
493        # Same problem, but colliding with an m2m field
494        results = Author.objects.values('name').annotate(friends=Count('friends')).order_by('name')
495        self.assertEqual(len(results), 9)
496        self.assertEqual(results[0]['name'], u'Adrian Holovaty')
497        self.assertEqual(results[0]['friends'], 2)
498
499    def test_reverse_relation_name_conflict(self):
500        # Regression for #11256 - providing an aggregate name that conflicts with a reverse-related name on the model raises ValueError
501        self.assertRaises(ValueError, Author.objects.annotate, book_contact_set=Avg('friends__age'))
502
503    def test_pickle(self):
504        # Regression for #10197 -- Queries with aggregates can be pickled.
505        # First check that pickling is possible at all. No crash = success
506        qs = Book.objects.annotate(num_authors=Count('authors'))
507        pickle.dumps(qs)
508
509        # Then check that the round trip works.
510        query = qs.query.get_compiler(qs.db).as_sql()[0]
511        qs2 = pickle.loads(pickle.dumps(qs))
512        self.assertEqual(
513            qs2.query.get_compiler(qs2.db).as_sql()[0],
514            query,
515        )
516
517    def test_more_more_more(self):
518        # Regression for #10199 - Aggregate calls clone the original query so
519        # the original query can still be used
520        books = Book.objects.all()
521        books.aggregate(Avg("authors__age"))
522        self.assertQuerysetEqual(
523            books.all(), [
524                u'Artificial Intelligence: A Modern Approach',
525                u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
526                u'Practical Django Projects',
527                u'Python Web Development with Django',
528                u'Sams Teach Yourself Django in 24 Hours',
529                u'The Definitive Guide to Django: Web Development Done Right'
530            ],
531            lambda b: b.name
532        )
533
534        # Regression for #10248 - Annotations work with DateQuerySets
535        qs = Book.objects.annotate(num_authors=Count('authors')).filter(num_authors=2).dates('pubdate', 'day')
536        self.assertQuerysetEqual(
537            qs, [
538                datetime.datetime(1995, 1, 15, 0, 0),
539                datetime.datetime(2007, 12, 6, 0, 0)
540            ],
541            lambda b: b
542        )
543
544        # Regression for #10290 - extra selects with parameters can be used for
545        # grouping.
546        qs = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'sheets' : '(pages + %s) / %s'}, select_params=[1, 2]).order_by('sheets').values('sheets')
547        self.assertQuerysetEqual(
548            qs, [
549                150,
550                175,
551                224,
552                264,
553                473,
554                566
555            ],
556            lambda b: int(b["sheets"])
557        )
558
559        # Regression for 10425 - annotations don't get in the way of a count()
560        # clause
561        self.assertEqual(
562            Book.objects.values('publisher').annotate(Count('publisher')).count(),
563            4
564        )
565        self.assertEqual(
566            Book.objects.annotate(Count('publisher')).values('publisher').count(),
567            6
568        )
569
570        publishers = Publisher.objects.filter(id__in=[1, 2])
571        self.assertEqual(
572            sorted(p.name for p in publishers),
573            [
574                "Apress",
575                "Sams"
576            ]
577        )
578
579        publishers = publishers.annotate(n_books=Count("book"))
580        self.assertEqual(
581            publishers[0].n_books,
582            2
583        )
584
585        self.assertEqual(
586            sorted(p.name for p in publishers),
587            [
588                "Apress",
589                "Sams"
590            ]
591        )
592
593        books = Book.objects.filter(publisher__in=publishers)
594        self.assertQuerysetEqual(
595            books, [
596                "Practical Django Projects",
597                "Sams Teach Yourself Django in 24 Hours",
598                "The Definitive Guide to Django: Web Development Done Right",
599            ],
600            lambda b: b.name
601        )
602        self.assertEqual(
603            sorted(p.name for p in publishers),
604            [
605                "Apress",
606                "Sams"
607            ]
608        )
609
610        # Regression for 10666 - inherited fields work with annotations and
611        # aggregations
612        self.assertEqual(
613            HardbackBook.objects.aggregate(n_pages=Sum('book_ptr__pages')),
614            {'n_pages': 2078}
615        )
616
617        self.assertEqual(
618            HardbackBook.objects.aggregate(n_pages=Sum('pages')),
619            {'n_pages': 2078},
620        )
621
622        qs = HardbackBook.objects.annotate(n_authors=Count('book_ptr__authors')).values('name', 'n_authors')
623        self.assertQuerysetEqual(
624            qs, [
625                {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},
626                {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}
627            ],
628            lambda h: h
629        )
630
631        qs = HardbackBook.objects.annotate(n_authors=Count('authors')).values('name', 'n_authors')
632        self.assertQuerysetEqual(
633            qs, [
634                {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},
635                {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}
636            ],
637            lambda h: h,
638        )
639
640        # Regression for #10766 - Shouldn't be able to reference an aggregate
641        # fields in an an aggregate() call.
642        self.assertRaises(
643            FieldError,
644            lambda: Book.objects.annotate(mean_age=Avg('authors__age')).annotate(Avg('mean_age'))
645        )
646
647    def test_empty_filter_count(self):
648        self.assertEqual(
649            Author.objects.filter(id__in=[]).annotate(Count("friends")).count(),
650            0
651        )
652
653    def test_empty_filter_aggregate(self):
654        self.assertEqual(
655            Author.objects.filter(id__in=[]).annotate(Count("friends")).aggregate(Count("pk")),
656            {"pk__count": None}
657        )
658
659    def test_annotate_and_join(self):
660        self.assertEqual(
661            Author.objects.annotate(c=Count("friends__name")).exclude(friends__name="Joe").count(),
662            Author.objects.count()
663        )
664
665    def test_f_expression_annotation(self):
666        # Books with less than 200 pages per author.
667        qs = Book.objects.values("name").annotate(
668            n_authors=Count("authors")
669        ).filter(
670            pages__lt=F("n_authors") * 200
671        ).values_list("pk")
672        self.assertQuerysetEqual(
673            Book.objects.filter(pk__in=qs), [
674                "Python Web Development with Django"
675            ],
676            attrgetter("name")
677        )
678
679    def test_values_annotate_values(self):
680        qs = Book.objects.values("name").annotate(
681            n_authors=Count("authors")
682        ).values_list("pk", flat=True)
683        self.assertEqual(list(qs), list(Book.objects.values_list("pk", flat=True)))
684
685    def test_having_group_by(self):
686        # Test that when a field occurs on the LHS of a HAVING clause that it
687        # appears correctly in the GROUP BY clause
688        qs = Book.objects.values_list("name").annotate(
689            n_authors=Count("authors")
690        ).filter(
691            pages__gt=F("n_authors")
692        ).values_list("name", flat=True)
693        # Results should be the same, all Books have more pages than authors
694        self.assertEqual(
695            list(qs), list(Book.objects.values_list("name", flat=True))
696        )
697
698    def test_annotation_disjunction(self):
699        qs = Book.objects.annotate(n_authors=Count("authors")).filter(
700            Q(n_authors=2) | Q(name="Python Web Development with Django")
701        )
702        self.assertQuerysetEqual(
703            qs, [
704                "Artificial Intelligence: A Modern Approach",
705                "Python Web Development with Django",
706                "The Definitive Guide to Django: Web Development Done Right",
707            ],
708            attrgetter("name")
709        )
710
711        qs = Book.objects.annotate(n_authors=Count("authors")).filter(
712            Q(name="The Definitive Guide to Django: Web Development Done Right") | (Q(name="Artificial Intelligence: A Modern Approach") & Q(n_authors=3))
713        )
714        self.assertQuerysetEqual(
715            qs, [
716                "The Definitive Guide to Django: Web Development Done Right",
717            ],
718            attrgetter("name")
719        )
720
721        qs = Publisher.objects.annotate(
722            rating_sum=Sum("book__rating"),
723            book_count=Count("book")
724        ).filter(
725            Q(rating_sum__gt=5.5) | Q(rating_sum__isnull=True)
726        ).order_by('pk')
727        self.assertQuerysetEqual(
728            qs, [
729                "Apress",
730                "Prentice Hall",
731                "Jonno's House of Books",
732            ],
733            attrgetter("name")
734        )
735
736        qs = Publisher.objects.annotate(
737            rating_sum=Sum("book__rating"),
738            book_count=Count("book")
739        ).filter(
740            Q(pk__lt=F("book_count")) | Q(rating_sum=None)
741        ).order_by("pk")
742        self.assertQuerysetEqual(
743            qs, [
744                "Apress",
745                "Jonno's House of Books",
746            ],
747            attrgetter("name")
748        )
749
750    def test_quoting_aggregate_order_by(self):
751        qs = Book.objects.filter(
752            name="Python Web Development with Django"
753        ).annotate(
754            authorCount=Count("authors")
755        ).order_by("authorCount")
756        self.assertQuerysetEqual(
757            qs, [
758                ("Python Web Development with Django", 3),
759            ],
760            lambda b: (b.name, b.authorCount)
761        )
762
763    @skipUnlessDBFeature('supports_stddev')
764    def test_stddev(self):
765        self.assertEqual(
766            Book.objects.aggregate(StdDev('pages')),
767            {'pages__stddev': Approximate(311.46, 1)}
768        )
769
770        self.assertEqual(
771            Book.objects.aggregate(StdDev('rating')),
772            {'rating__stddev': Approximate(0.60, 1)}
773        )
774
775        self.assertEqual(
776            Book.objects.aggregate(StdDev('price')),
777            {'price__stddev': Approximate(24.16, 2)}
778        )
779
780        self.assertEqual(
781            Book.objects.aggregate(StdDev('pages', sample=True)),
782            {'pages__stddev': Approximate(341.19, 2)}
783        )
784
785        self.assertEqual(
786            Book.objects.aggregate(StdDev('rating', sample=True)),
787            {'rating__stddev': Approximate(0.66, 2)}
788        )
789
790        self.assertEqual(
791            Book.objects.aggregate(StdDev('price', sample=True)),
792            {'price__stddev': Approximate(26.46, 1)}
793        )
794
795        self.assertEqual(
796            Book.objects.aggregate(Variance('pages')),
797            {'pages__variance': Approximate(97010.80, 1)}
798        )
799
800        self.assertEqual(
801            Book.objects.aggregate(Variance('rating')),
802            {'rating__variance': Approximate(0.36, 1)}
803        )
804
805        self.assertEqual(
806            Book.objects.aggregate(Variance('price')),
807            {'price__variance': Approximate(583.77, 1)}
808        )
809
810        self.assertEqual(
811            Book.objects.aggregate(Variance('pages', sample=True)),
812            {'pages__variance': Approximate(116412.96, 1)}
813        )
814
815        self.assertEqual(
816            Book.objects.aggregate(Variance('rating', sample=True)),
817            {'rating__variance': Approximate(0.44, 2)}
818        )
819
820        self.assertEqual(
821            Book.objects.aggregate(Variance('price', sample=True)),
822            {'price__variance': Approximate(700.53, 2)}
823        )