/tests/regressiontests/aggregation_regress/tests.py
Python | 823 lines | 733 code | 49 blank | 41 comment | 14 complexity | 11799208d6aac9069fbb5f44c343b4d4 MD5 | raw file
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 )