PageRenderTime 159ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 1ms

/tests/regressiontests/aggregation_regress/tests.py

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