1 import datetime

     2 import pickle

     3 from decimal import Decimal

     4 from operator import attrgetter

     5 

     6 from django.conf import settings

     7 from django.core.exceptions import FieldError

     8 from django.db import DEFAULT_DB_ALIAS

     9 from django.db.models import Count, Max, Avg, Sum, StdDev, Variance, F

    10 from django.test import TestCase, Approximate

    11 

    12 from models import Author, Book, Publisher, Clues, Entries, HardbackBook

    13 

    14 

    15 def run_stddev_tests():

    16     """Check to see if StdDev/Variance tests should be run.

    17 

    18     Stddev and Variance are not guaranteed to be available for SQLite, and

    19     are not available for PostgreSQL before 8.2.

    20     """

    21     if settings.DATABASES[DEFAULT_DB_ALIAS]['ENGINE'] == 'django.db.backends.sqlite3':

    22         return False

    23 

    24     class StdDevPop(object):

    25         sql_function = 'STDDEV_POP'

    26 

    27     try:

    28         connection.ops.check_aggregate_support(StdDevPop())

    29     except:

    30         return False

    31     return True

    32 

    33 

    34 class AggregationTests(TestCase):

    35     def assertObjectAttrs(self, obj, **kwargs):

    36         for attr, value in kwargs.iteritems():

    37             self.assertEqual(getattr(obj, attr), value)

    38 

    39     def test_aggregates_in_where_clause(self):

    40         """

    41         Regression test for #12822: DatabaseError: aggregates not allowed in

    42         WHERE clause

    43 

    44         Tests that the subselect works and returns results equivalent to a

    45         query with the IDs listed.

    46 

    47         Before the corresponding fix for this bug, this test passed in 1.1 and

    48         failed in 1.2-beta (trunk).

    49         """

    50         qs = Book.objects.values('contact').annotate(Max('id'))

    51         qs = qs.order_by('contact').values_list('id__max', flat=True)

    52         # don't do anything with the queryset (qs) before including it as a

    53         # subquery

    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     def test_aggregates_in_where_clause_pre_eval(self):

    60         """

    61         Regression test for #12822: DatabaseError: aggregates not allowed in

    62         WHERE clause

    63 

    64         Same as the above test, but evaluates the queryset for the subquery

    65         before it's used as a subquery.

    66 

    67         Before the corresponding fix for this bug, this test failed in both

    68         1.1 and 1.2-beta (trunk).

    69         """

    70         qs = Book.objects.values('contact').annotate(Max('id'))

    71         qs = qs.order_by('contact').values_list('id__max', flat=True)

    72         # force the queryset (qs) for the subquery to be evaluated in its

    73         # current state

    74         list(qs)

    75         books = Book.objects.order_by('id')

    76         qs1 = books.filter(id__in=qs)

    77         qs2 = books.filter(id__in=list(qs))

    78         self.assertEqual(list(qs1), list(qs2))

    79 

    80     if settings.DATABASES[DEFAULT_DB_ALIAS]['ENGINE'] != 'django.db.backends.oracle':

    81         def test_annotate_with_extra(self):

    82             """

    83             Regression test for #11916: Extra params + aggregation creates

    84             incorrect SQL.

    85             """

    86             #oracle doesn't support subqueries in group by clause

    87             shortest_book_sql = """

    88             SELECT name

    89             FROM aggregation_regress_book b

    90             WHERE b.publisher_id = aggregation_regress_publisher.id

    91             ORDER BY b.pages

    92             LIMIT 1

    93             """

    94             # tests that this query does not raise a DatabaseError due to the full

    95             # subselect being (erroneously) added to the GROUP BY parameters

    96             qs = Publisher.objects.extra(select={

    97                 'name_of_shortest_book': shortest_book_sql,

    98             }).annotate(total_books=Count('book'))

    99             # force execution of the query

   100             list(qs)

   101 

   102     def test_aggregate(self):

   103         # Ordering requests are ignored

   104         self.assertEqual(

   105             Author.objects.order_by("name").aggregate(Avg("age")),

   106             {"age__avg": Approximate(37.444, places=1)}

   107         )

   108 

   109         # Implicit ordering is also ignored

   110         self.assertEqual(

   111             Book.objects.aggregate(Sum("pages")),

   112             {"pages__sum": 3703},

   113         )

   114 

   115         # Baseline results

   116         self.assertEqual(

   117             Book.objects.aggregate(Sum('pages'), Avg('pages')),

   118             {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}

   119         )

   120 

   121         # Empty values query doesn't affect grouping or results

   122         self.assertEqual(

   123             Book.objects.values().aggregate(Sum('pages'), Avg('pages')),

   124             {'pages__sum': 3703, 'pages__avg': Approximate(617.166, places=2)}

   125         )

   126 

   127         # Aggregate overrides extra selected column

   128         self.assertEqual(

   129             Book.objects.extra(select={'price_per_page' : 'price / pages'}).aggregate(Sum('pages')),

   130             {'pages__sum': 3703}

   131         )

   132 

   133     def test_annotation(self):

   134         # Annotations get combined with extra select clauses

   135         obj = Book.objects.annotate(mean_auth_age=Avg("authors__age")).extra(select={"manufacture_cost": "price * .5"}).get(pk=2)

   136         self.assertObjectAttrs(obj,

   137             contact_id=3,

   138             id=2,

   139             isbn=u'067232959',

   140             mean_auth_age=45.0,

   141             name='Sams Teach Yourself Django in 24 Hours',

   142             pages=528,

   143             price=Decimal("23.09"),

   144             pubdate=datetime.date(2008, 3, 3),

   145             publisher_id=2,

   146             rating=3.0

   147         )

   148         # Different DB backends return different types for the extra select computation

   149         self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))

   150 

   151         # Order of the annotate/extra in the query doesn't matter

   152         obj = Book.objects.extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2)

   153         self.assertObjectAttrs(obj,

   154             contact_id=3,

   155             id=2,

   156             isbn=u'067232959',

   157             mean_auth_age=45.0,

   158             name=u'Sams Teach Yourself Django in 24 Hours',

   159             pages=528,

   160             price=Decimal("23.09"),

   161             pubdate=datetime.date(2008, 3, 3),

   162             publisher_id=2,

   163             rating=3.0

   164         )

   165         # Different DB backends return different types for the extra select computation

   166         self.assertTrue(obj.manufacture_cost == 11.545 or obj.manufacture_cost == Decimal('11.545'))

   167 

   168         # Values queries can be combined with annotate and extra

   169         obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2)

   170         manufacture_cost = obj['manufacture_cost']

   171         self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))

   172         del obj['manufacture_cost']

   173         self.assertEqual(obj, {

   174             "contact_id": 3,

   175             "id": 2,

   176             "isbn": u"067232959",

   177             "mean_auth_age": 45.0,

   178             "name": u"Sams Teach Yourself Django in 24 Hours",

   179             "pages": 528,

   180             "price": Decimal("23.09"),

   181             "pubdate": datetime.date(2008, 3, 3),

   182             "publisher_id": 2,

   183             "rating": 3.0,

   184         })

   185 

   186         # The order of the (empty) values, annotate and extra clauses doesn't

   187         # matter

   188         obj = Book.objects.values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2)

   189         manufacture_cost = obj['manufacture_cost']

   190         self.assertTrue(manufacture_cost == 11.545 or manufacture_cost == Decimal('11.545'))

   191         del obj['manufacture_cost']

   192         self.assertEqual(obj, {

   193             'contact_id': 3,

   194             'id': 2,

   195             'isbn': u'067232959',

   196             'mean_auth_age': 45.0,

   197             'name': u'Sams Teach Yourself Django in 24 Hours',

   198             'pages': 528,

   199             'price': Decimal("23.09"),

   200             'pubdate': datetime.date(2008, 3, 3),

   201             'publisher_id': 2,

   202             'rating': 3.0

   203         })

   204 

   205         # If the annotation precedes the values clause, it won't be included

   206         # unless it is explicitly named

   207         obj = Book.objects.annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name').get(pk=1)

   208         self.assertEqual(obj, {

   209             "name": u'The Definitive Guide to Django: Web Development Done Right',

   210         })

   211 

   212         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)

   213         self.assertEqual(obj, {

   214             'mean_auth_age': 34.5,

   215             'name': u'The Definitive Guide to Django: Web Development Done Right',

   216         })

   217 

   218         # If an annotation isn't included in the values, it can still be used

   219         # in a filter

   220         qs = Book.objects.annotate(n_authors=Count('authors')).values('name').filter(n_authors__gt=2)

   221         self.assertQuerysetEqual(

   222             qs, [

   223                 {"name": u'Python Web Development with Django'}

   224             ],

   225             lambda b: b,

   226         )

   227 

   228         # The annotations are added to values output if values() precedes

   229         # annotate()

   230         obj = Book.objects.values('name').annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).get(pk=1)

   231         self.assertEqual(obj, {

   232             'mean_auth_age': 34.5,

   233             'name': u'The Definitive Guide to Django: Web Development Done Right',

   234         })

   235 

   236         # Check that all of the objects are getting counted (allow_nulls) and

   237         # that values respects the amount of objects

   238         self.assertEqual(

   239             len(Author.objects.annotate(Avg('friends__age')).values()),

   240             9

   241         )

   242 

   243         # Check that consecutive calls to annotate accumulate in the query

   244         qs = Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards'))

   245         self.assertQuerysetEqual(

   246             qs, [

   247                 {'price': Decimal("30"), 'oldest': 35, 'publisher__num_awards__max': 3},

   248                 {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7},

   249                 {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1},

   250                 {'price': Decimal("75"), 'oldest': 57, 'publisher__num_awards__max': 9},

   251                 {'price': Decimal("82.8"), 'oldest': 57, 'publisher__num_awards__max': 7}

   252             ],

   253             lambda b: b,

   254         )

   255 

   256     def test_aggrate_annotation(self):

   257         # Aggregates can be composed over annotations.

   258         # The return type is derived from the composed aggregate

   259         vals = Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('pages'), Max('price'), Sum('num_authors'), Avg('num_authors'))

   260         self.assertEqual(vals, {

   261             'num_authors__sum': 10,

   262             'num_authors__avg': Approximate(1.666, places=2),

   263             'pages__max': 1132,

   264             'price__max': Decimal("82.80")

   265         })

   266 

   267     def test_field_error(self):

   268         # Bad field requests in aggregates are caught and reported

   269         self.assertRaises(

   270             FieldError,

   271             lambda: Book.objects.all().aggregate(num_authors=Count('foo'))

   272         )

   273 

   274         self.assertRaises(

   275             FieldError,

   276             lambda: Book.objects.all().annotate(num_authors=Count('foo'))

   277         )

   278 

   279         self.assertRaises(

   280             FieldError,

   281             lambda: Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('foo'))

   282         )

   283 

   284     def test_more(self):

   285         # Old-style count aggregations can be mixed with new-style

   286         self.assertEqual(

   287             Book.objects.annotate(num_authors=Count('authors')).count(),

   288             6

   289         )

   290 

   291         # Non-ordinal, non-computed Aggregates over annotations correctly

   292         # inherit the annotation's internal type if the annotation is ordinal

   293         # or computed

   294         vals = Book.objects.annotate(num_authors=Count('authors')).aggregate(Max('num_authors'))

   295         self.assertEqual(

   296             vals,

   297             {'num_authors__max': 3}

   298         )

   299 

   300         vals = Publisher.objects.annotate(avg_price=Avg('book__price')).aggregate(Max('avg_price'))

   301         self.assertEqual(

   302             vals,

   303             {'avg_price__max': 75.0}

   304         )

   305 

   306         # Aliases are quoted to protected aliases that might be reserved names

   307         vals = Book.objects.aggregate(number=Max('pages'), select=Max('pages'))

   308         self.assertEqual(

   309             vals,

   310             {'number': 1132, 'select': 1132}

   311         )

   312 

   313         # Regression for #10064: select_related() plays nice with aggregates

   314         obj = Book.objects.select_related('publisher').annotate(num_authors=Count('authors')).values()[0]

   315         self.assertEqual(obj, {

   316             'contact_id': 8,

   317             'id': 5,

   318             'isbn': u'013790395',

   319             'name': u'Artificial Intelligence: A Modern Approach',

   320             'num_authors': 2,

   321             'pages': 1132,

   322             'price': Decimal("82.8"),

   323             'pubdate': datetime.date(1995, 1, 15),

   324             'publisher_id': 3,

   325             'rating': 4.0,

   326         })

   327 

   328         # Regression for #10010: exclude on an aggregate field is correctly

   329         # negated

   330         self.assertEqual(

   331             len(Book.objects.annotate(num_authors=Count('authors'))),

   332             6

   333         )

   334         self.assertEqual(

   335             len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=2)),

   336             1

   337         )

   338         self.assertEqual(

   339             len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__gt=2)),

   340             5

   341         )

   342 

   343         self.assertEqual(

   344             len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__lt=3).exclude(num_authors__lt=2)),

   345             2

   346         )

   347         self.assertEqual(

   348             len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__lt=2).filter(num_authors__lt=3)),

   349             2

   350         )

   351 

   352     def test_aggregate_fexpr(self):

   353         # Aggregates can be used with F() expressions

   354         # ... where the F() is pushed into the HAVING clause

   355         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')

   356         self.assertQuerysetEqual(

   357             qs, [

   358                 {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},

   359                 {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}

   360             ],

   361             lambda p: p,

   362         )

   363 

   364         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')

   365         self.assertQuerysetEqual(

   366             qs, [

   367                 {'num_books': 2, 'name': u'Apress', 'num_awards': 3},

   368                 {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},

   369                 {'num_books': 1, 'name': u'Sams', 'num_awards': 1}

   370             ],

   371             lambda p: p,

   372         )

   373 

   374         # ... and where the F() references an aggregate

   375         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')

   376         self.assertQuerysetEqual(

   377             qs, [

   378                 {'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9},

   379                 {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}

   380             ],

   381             lambda p: p,

   382         )

   383 

   384         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')

   385         self.assertQuerysetEqual(

   386             qs, [

   387                 {'num_books': 2, 'name': u'Apress', 'num_awards': 3},

   388                 {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0},

   389                 {'num_books': 1, 'name': u'Sams', 'num_awards': 1}

   390             ],

   391             lambda p: p,

   392         )

   393 

   394     def test_db_col_table(self):

   395         # Tests on fields with non-default table and column names.

   396         qs = Clues.objects.values('EntryID__Entry').annotate(Appearances=Count('EntryID'), Distinct_Clues=Count('Clue', distinct=True))

   397         self.assertQuerysetEqual(qs, [])

   398 

   399         qs = Entries.objects.annotate(clue_count=Count('clues__ID'))

   400         self.assertQuerysetEqual(qs, [])

   401 

   402     def test_empty(self):

   403         # Regression for #10089: Check handling of empty result sets with

   404         # aggregates

   405         self.assertEqual(

   406             Book.objects.filter(id__in=[]).count(),

   407             0

   408         )

   409 

   410         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'))

   411         self.assertEqual(

   412             vals,

   413             {'max_authors': None, 'max_rating': None, 'num_authors': 0, 'avg_authors': None, 'max_price': None}

   414         )

   415 

   416         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()

   417         self.assertQuerysetEqual(

   418             qs, [

   419                 {'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}

   420             ],

   421             lambda p: p

   422         )

   423 

   424     def test_more_more(self):

   425         # Regression for #10113 - Fields mentioned in order_by() must be

   426         # included in the GROUP BY. This only becomes a problem when the

   427         # order_by introduces a new join.

   428         self.assertQuerysetEqual(

   429             Book.objects.annotate(num_authors=Count('authors')).order_by('publisher__name', 'name'), [

   430                 "Practical Django Projects",

   431                 "The Definitive Guide to Django: Web Development Done Right",

   432                 "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp",

   433                 "Artificial Intelligence: A Modern Approach",

   434                 "Python Web Development with Django",

   435                 "Sams Teach Yourself Django in 24 Hours",

   436             ],

   437             lambda b: b.name

   438         )

   439 

   440         # Regression for #10127 - Empty select_related() works with annotate

   441         qs = Book.objects.filter(rating__lt=4.5).select_related().annotate(Avg('authors__age'))

   442         self.assertQuerysetEqual(

   443             qs, [

   444                 (u'Artificial Intelligence: A Modern Approach', 51.5, u'Prentice Hall', u'Peter Norvig'),

   445                 (u'Practical Django Projects', 29.0, u'Apress', u'James Bennett'),

   446                 (u'Python Web Development with Django', Approximate(30.333, places=2), u'Prentice Hall', u'Jeffrey Forcier'),

   447                 (u'Sams Teach Yourself Django in 24 Hours', 45.0, u'Sams', u'Brad Dayley')

   448             ],

   449             lambda b: (b.name, b.authors__age__avg, b.publisher.name, b.contact.name)

   450         )

   451 

   452         # Regression for #10132 - If the values() clause only mentioned extra

   453         # (select=) columns, those columns are used for grouping

   454         qs = Book.objects.extra(select={'pub':'publisher_id'}).values('pub').annotate(Count('id')).order_by('pub')

   455         self.assertQuerysetEqual(

   456             qs, [

   457                 {'pub': 1, 'id__count': 2},

   458                 {'pub': 2, 'id__count': 1},

   459                 {'pub': 3, 'id__count': 2},

   460                 {'pub': 4, 'id__count': 1}

   461             ],

   462             lambda b: b

   463         )

   464 

   465         qs = Book.objects.extra(select={'pub':'publisher_id', 'foo':'pages'}).values('pub').annotate(Count('id')).order_by('pub')

   466         self.assertQuerysetEqual(

   467             qs, [

   468                 {'pub': 1, 'id__count': 2},

   469                 {'pub': 2, 'id__count': 1},

   470                 {'pub': 3, 'id__count': 2},

   471                 {'pub': 4, 'id__count': 1}

   472             ],

   473             lambda b: b

   474         )

   475 

   476         # Regression for #10182 - Queries with aggregate calls are correctly

   477         # realiased when used in a subquery

   478         ids = Book.objects.filter(pages__gt=100).annotate(n_authors=Count('authors')).filter(n_authors__gt=2).order_by('n_authors')

   479         self.assertQuerysetEqual(

   480             Book.objects.filter(id__in=ids), [

   481                 "Python Web Development with Django",

   482             ],

   483             lambda b: b.name

   484         )

   485 

   486     def test_duplicate_alias(self):

   487         # Regression for #11256 - duplicating a default alias raises ValueError.

   488         self.assertRaises(ValueError, Book.objects.all().annotate, Avg('authors__age'), authors__age__avg=Avg('authors__age'))

   489 

   490     def test_field_name_conflict(self):

   491         # Regression for #11256 - providing an aggregate name that conflicts with a field name on the model raises ValueError

   492         self.assertRaises(ValueError, Author.objects.annotate, age=Avg('friends__age'))

   493 

   494     def test_m2m_name_conflict(self):

   495         # Regression for #11256 - providing an aggregate name that conflicts with an m2m name on the model raises ValueError

   496         self.assertRaises(ValueError, Author.objects.annotate, friends=Count('friends'))

   497 

   498     def test_reverse_relation_name_conflict(self):

   499         # Regression for #11256 - providing an aggregate name that conflicts with a reverse-related name on the model raises ValueError

   500         self.assertRaises(ValueError, Author.objects.annotate, book_contact_set=Avg('friends__age'))

   501 

   502     def test_pickle(self):

   503         # Regression for #10197 -- Queries with aggregates can be pickled.

   504         # First check that pickling is possible at all. No crash = success

   505         qs = Book.objects.annotate(num_authors=Count('authors'))

   506         pickle.dumps(qs)

   507 

   508         # Then check that the round trip works.

   509         query = qs.query.get_compiler(qs.db).as_sql()[0]

   510         qs2 = pickle.loads(pickle.dumps(qs))

   511         self.assertEqual(

   512             qs2.query.get_compiler(qs2.db).as_sql()[0],

   513             query,

   514         )

   515 

   516     def test_more_more_more(self):

   517         # Regression for #10199 - Aggregate calls clone the original query so

   518         # the original query can still be used

   519         books = Book.objects.all()

   520         books.aggregate(Avg("authors__age"))

   521         self.assertQuerysetEqual(

   522             books.all(), [

   523                 u'Artificial Intelligence: A Modern Approach',

   524                 u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',

   525                 u'Practical Django Projects',

   526                 u'Python Web Development with Django',

   527                 u'Sams Teach Yourself Django in 24 Hours',

   528                 u'The Definitive Guide to Django: Web Development Done Right'

   529             ],

   530             lambda b: b.name

   531         )

   532 

   533         # Regression for #10248 - Annotations work with DateQuerySets

   534         qs = Book.objects.annotate(num_authors=Count('authors')).filter(num_authors=2).dates('pubdate', 'day')

   535         self.assertQuerysetEqual(

   536             qs, [

   537                 datetime.datetime(1995, 1, 15, 0, 0),

   538                 datetime.datetime(2007, 12, 6, 0, 0)

   539             ],

   540             lambda b: b

   541         )

   542 

   543         # Regression for #10290 - extra selects with parameters can be used for

   544         # grouping.

   545         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')

   546         self.assertQuerysetEqual(

   547             qs, [

   548                 150,

   549                 175,

   550                 224,

   551                 264,

   552                 473,

   553                 566

   554             ],

   555             lambda b: int(b["sheets"])

   556         )

   557 

   558         # Regression for 10425 - annotations don't get in the way of a count()

   559         # clause

   560         self.assertEqual(

   561             Book.objects.values('publisher').annotate(Count('publisher')).count(),

   562             4

   563         )

   564         self.assertEqual(

   565             Book.objects.annotate(Count('publisher')).values('publisher').count(),

   566             6

   567         )

   568 

   569         publishers = Publisher.objects.filter(id__in=[1, 2])

   570         self.assertQuerysetEqual(

   571             publishers, [

   572                 "Apress",

   573                 "Sams"

   574             ],

   575             lambda p: p.name

   576         )

   577 

   578         publishers = publishers.annotate(n_books=Count("book"))

   579         self.assertEqual(

   580             publishers[0].n_books,

   581             2

   582         )

   583 

   584         self.assertQuerysetEqual(

   585             publishers, [

   586                 "Apress",

   587                 "Sams",

   588             ],

   589             lambda p: p.name

   590         )

   591 

   592         books = Book.objects.filter(publisher__in=publishers)

   593         self.assertQuerysetEqual(

   594             books, [

   595                 "Practical Django Projects",

   596                 "Sams Teach Yourself Django in 24 Hours",

   597                 "The Definitive Guide to Django: Web Development Done Right",

   598             ],

   599             lambda b: b.name

   600         )

   601         self.assertQuerysetEqual(

   602             publishers, [

   603                 "Apress",

   604                 "Sams",

   605             ],

   606             lambda p: p.name

   607         )

   608 

   609         # Regression for 10666 - inherited fields work with annotations and

   610         # aggregations

   611         self.assertEqual(

   612             HardbackBook.objects.aggregate(n_pages=Sum('book_ptr__pages')),

   613             {'n_pages': 2078}

   614         )

   615 

   616         self.assertEqual(

   617             HardbackBook.objects.aggregate(n_pages=Sum('pages')),

   618             {'n_pages': 2078},

   619         )

   620 

   621         qs = HardbackBook.objects.annotate(n_authors=Count('book_ptr__authors')).values('name', 'n_authors')

   622         self.assertQuerysetEqual(

   623             qs, [

   624                 {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},

   625                 {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}

   626             ],

   627             lambda h: h

   628         )

   629 

   630         qs = HardbackBook.objects.annotate(n_authors=Count('authors')).values('name', 'n_authors')

   631         self.assertQuerysetEqual(

   632             qs, [

   633                 {'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'},

   634                 {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}

   635             ],

   636             lambda h: h,

   637         )

   638 

   639         # Regression for #10766 - Shouldn't be able to reference an aggregate

   640         # fields in an an aggregate() call.

   641         self.assertRaises(

   642             FieldError,

   643             lambda: Book.objects.annotate(mean_age=Avg('authors__age')).annotate(Avg('mean_age'))

   644         )

   645 

   646     def test_empty_filter_count(self):

   647         self.assertEqual(

   648             Author.objects.filter(id__in=[]).annotate(Count("friends")).count(),

   649             0

   650         )

   651 

   652     def test_empty_filter_aggregate(self):

   653         self.assertEqual(

   654             Author.objects.filter(id__in=[]).annotate(Count("friends")).aggregate(Count("pk")),

   655             {"pk__count": None}

   656         )

   657 

   658     def test_annotate_and_join(self):

   659         self.assertEqual(

   660             Author.objects.annotate(c=Count("friends__name")).exclude(friends__name="Joe").count(),

   661             Author.objects.count()

   662         )

   663 

   664     def test_f_expression_annotation(self):

   665         # Books with less than 200 pages per author.

   666         qs = Book.objects.values("name").annotate(

   667             n_authors=Count("authors")

   668         ).filter(

   669             pages__lt=F("n_authors") * 200

   670         ).values_list("pk")

   671         self.assertQuerysetEqual(

   672             Book.objects.filter(pk__in=qs), [

   673                 "Python Web Development with Django"

   674             ],

   675             attrgetter("name")

   676         )

   677 

   678     def test_values_annotate_values(self):

   679         qs = Book.objects.values("name").annotate(

   680             n_authors=Count("authors")

   681         ).values_list("pk", flat=True)

   682         self.assertEqual(list(qs), list(Book.objects.values_list("pk", flat=True)))

   683 

   684     def test_having_group_by(self):

   685         # Test that when a field occurs on the LHS of a HAVING clause that it

   686         # appears correctly in the GROUP BY clause

   687         qs = Book.objects.values_list("name").annotate(

   688             n_authors=Count("authors")

   689         ).filter(

   690             pages__gt=F("n_authors")

   691         ).values_list("name", flat=True)

   692         # Results should be the same, all Books have more pages than authors

   693         self.assertEqual(

   694             list(qs), list(Book.objects.values_list("name", flat=True))

   695         )

   696 

   697     if run_stddev_tests():

   698         def test_stddev(self):

   699             self.assertEqual(

   700                 Book.objects.aggregate(StdDev('pages')),

   701                 {'pages__stddev': Approximate(311.46, 1)}

   702             )

   703 

   704             self.assertEqual(

   705                 Book.objects.aggregate(StdDev('rating')),

   706                 {'rating__stddev': Approximate(0.60, 1)}

   707             )

   708 

   709             self.assertEqual(

   710                 Book.objects.aggregate(StdDev('price')),

   711                 {'price__stddev': Approximate(24.16, 2)}

   712             )

   713 

   714             self.assertEqual(

   715                 Book.objects.aggregate(StdDev('pages', sample=True)),

   716                 {'pages__stddev': Approximate(341.19, 2)}

   717             )

   718 

   719             self.assertEqual(

   720                 Book.objects.aggregate(StdDev('rating', sample=True)),

   721                 {'rating__stddev': Approximate(0.66, 2)}

   722             )

   723 

   724             self.assertEqual(

   725                 Book.objects.aggregate(StdDev('price', sample=True)),

   726                 {'price__stddev': Approximate(26.46, 1)}

   727             )

   728 

   729             self.assertEqual(

   730                 Book.objects.aggregate(Variance('pages')),

   731                 {'pages__variance': Approximate(97010.80, 1)}

   732             )

   733 

   734             self.assertEqual(

   735                 Book.objects.aggregate(Variance('rating')),

   736                 {'rating__variance': Approximate(0.36, 1)}

   737             )

   738 

   739             self.assertEqual(

   740                 Book.objects.aggregate(Variance('price')),

   741                 {'price__variance': Approximate(583.77, 1)}

   742             )

   743 

   744             self.assertEqual(

   745                 Book.objects.aggregate(Variance('pages', sample=True)),

   746                 {'pages__variance': Approximate(116412.96, 1)}

   747             )

   748 

   749             self.assertEqual(

   750                 Book.objects.aggregate(Variance('rating', sample=True)),

   751                 {'rating__variance': Approximate(0.44, 2)}

   752             )

   753 

   754             self.assertEqual(

   755                 Book.objects.aggregate(Variance('price', sample=True)),

   756                 {'price__variance': Approximate(700.53, 2)}

   757             )