PageRenderTime 43ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

/tests/modeltests/aggregation/tests.py

https://code.google.com/p/mango-py/
Python | 565 lines | 505 code | 60 blank | 0 comment | 0 complexity | e321fb3df01e7ab4d3c16e7423d7883c MD5 | raw file
Possible License(s): BSD-3-Clause
  1. import datetime
  2. from decimal import Decimal
  3. from django.db.models import Avg, Sum, Count, Max, Min
  4. from django.test import TestCase, Approximate
  5. from models import Author, Publisher, Book, Store
  6. class BaseAggregateTestCase(TestCase):
  7. fixtures = ["initial_data.json"]
  8. def test_empty_aggregate(self):
  9. self.assertEqual(Author.objects.all().aggregate(), {})
  10. def test_single_aggregate(self):
  11. vals = Author.objects.aggregate(Avg("age"))
  12. self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)})
  13. def test_multiple_aggregates(self):
  14. vals = Author.objects.aggregate(Sum("age"), Avg("age"))
  15. self.assertEqual(vals, {"age__sum": 337, "age__avg": Approximate(37.4, places=1)})
  16. def test_filter_aggregate(self):
  17. vals = Author.objects.filter(age__gt=29).aggregate(Sum("age"))
  18. self.assertEqual(len(vals), 1)
  19. self.assertEqual(vals["age__sum"], 254)
  20. def test_related_aggregate(self):
  21. vals = Author.objects.aggregate(Avg("friends__age"))
  22. self.assertEqual(len(vals), 1)
  23. self.assertAlmostEqual(vals["friends__age__avg"], 34.07, places=2)
  24. vals = Book.objects.filter(rating__lt=4.5).aggregate(Avg("authors__age"))
  25. self.assertEqual(len(vals), 1)
  26. self.assertAlmostEqual(vals["authors__age__avg"], 38.2857, places=2)
  27. vals = Author.objects.all().filter(name__contains="a").aggregate(Avg("book__rating"))
  28. self.assertEqual(len(vals), 1)
  29. self.assertEqual(vals["book__rating__avg"], 4.0)
  30. vals = Book.objects.aggregate(Sum("publisher__num_awards"))
  31. self.assertEqual(len(vals), 1)
  32. self.assertEqual(vals["publisher__num_awards__sum"], 30)
  33. vals = Publisher.objects.aggregate(Sum("book__price"))
  34. self.assertEqual(len(vals), 1)
  35. self.assertEqual(vals["book__price__sum"], Decimal("270.27"))
  36. def test_aggregate_multi_join(self):
  37. vals = Store.objects.aggregate(Max("books__authors__age"))
  38. self.assertEqual(len(vals), 1)
  39. self.assertEqual(vals["books__authors__age__max"], 57)
  40. vals = Author.objects.aggregate(Min("book__publisher__num_awards"))
  41. self.assertEqual(len(vals), 1)
  42. self.assertEqual(vals["book__publisher__num_awards__min"], 1)
  43. def test_aggregate_alias(self):
  44. vals = Store.objects.filter(name="Amazon.com").aggregate(amazon_mean=Avg("books__rating"))
  45. self.assertEqual(len(vals), 1)
  46. self.assertAlmostEqual(vals["amazon_mean"], 4.08, places=2)
  47. def test_annotate_basic(self):
  48. self.assertQuerysetEqual(
  49. Book.objects.annotate().order_by('pk'), [
  50. "The Definitive Guide to Django: Web Development Done Right",
  51. "Sams Teach Yourself Django in 24 Hours",
  52. "Practical Django Projects",
  53. "Python Web Development with Django",
  54. "Artificial Intelligence: A Modern Approach",
  55. "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp"
  56. ],
  57. lambda b: b.name
  58. )
  59. books = Book.objects.annotate(mean_age=Avg("authors__age"))
  60. b = books.get(pk=1)
  61. self.assertEqual(
  62. b.name,
  63. u'The Definitive Guide to Django: Web Development Done Right'
  64. )
  65. self.assertEqual(b.mean_age, 34.5)
  66. def test_annotate_m2m(self):
  67. books = Book.objects.filter(rating__lt=4.5).annotate(Avg("authors__age")).order_by("name")
  68. self.assertQuerysetEqual(
  69. books, [
  70. (u'Artificial Intelligence: A Modern Approach', 51.5),
  71. (u'Practical Django Projects', 29.0),
  72. (u'Python Web Development with Django', Approximate(30.3, places=1)),
  73. (u'Sams Teach Yourself Django in 24 Hours', 45.0)
  74. ],
  75. lambda b: (b.name, b.authors__age__avg),
  76. )
  77. books = Book.objects.annotate(num_authors=Count("authors")).order_by("name")
  78. self.assertQuerysetEqual(
  79. books, [
  80. (u'Artificial Intelligence: A Modern Approach', 2),
  81. (u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp', 1),
  82. (u'Practical Django Projects', 1),
  83. (u'Python Web Development with Django', 3),
  84. (u'Sams Teach Yourself Django in 24 Hours', 1),
  85. (u'The Definitive Guide to Django: Web Development Done Right', 2)
  86. ],
  87. lambda b: (b.name, b.num_authors)
  88. )
  89. def test_backwards_m2m_annotate(self):
  90. authors = Author.objects.filter(name__contains="a").annotate(Avg("book__rating")).order_by("name")
  91. self.assertQuerysetEqual(
  92. authors, [
  93. (u'Adrian Holovaty', 4.5),
  94. (u'Brad Dayley', 3.0),
  95. (u'Jacob Kaplan-Moss', 4.5),
  96. (u'James Bennett', 4.0),
  97. (u'Paul Bissex', 4.0),
  98. (u'Stuart Russell', 4.0)
  99. ],
  100. lambda a: (a.name, a.book__rating__avg)
  101. )
  102. authors = Author.objects.annotate(num_books=Count("book")).order_by("name")
  103. self.assertQuerysetEqual(
  104. authors, [
  105. (u'Adrian Holovaty', 1),
  106. (u'Brad Dayley', 1),
  107. (u'Jacob Kaplan-Moss', 1),
  108. (u'James Bennett', 1),
  109. (u'Jeffrey Forcier', 1),
  110. (u'Paul Bissex', 1),
  111. (u'Peter Norvig', 2),
  112. (u'Stuart Russell', 1),
  113. (u'Wesley J. Chun', 1)
  114. ],
  115. lambda a: (a.name, a.num_books)
  116. )
  117. def test_reverse_fkey_annotate(self):
  118. books = Book.objects.annotate(Sum("publisher__num_awards")).order_by("name")
  119. self.assertQuerysetEqual(
  120. books, [
  121. (u'Artificial Intelligence: A Modern Approach', 7),
  122. (u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp', 9),
  123. (u'Practical Django Projects', 3),
  124. (u'Python Web Development with Django', 7),
  125. (u'Sams Teach Yourself Django in 24 Hours', 1),
  126. (u'The Definitive Guide to Django: Web Development Done Right', 3)
  127. ],
  128. lambda b: (b.name, b.publisher__num_awards__sum)
  129. )
  130. publishers = Publisher.objects.annotate(Sum("book__price")).order_by("name")
  131. self.assertQuerysetEqual(
  132. publishers, [
  133. (u'Apress', Decimal("59.69")),
  134. (u"Jonno's House of Books", None),
  135. (u'Morgan Kaufmann', Decimal("75.00")),
  136. (u'Prentice Hall', Decimal("112.49")),
  137. (u'Sams', Decimal("23.09"))
  138. ],
  139. lambda p: (p.name, p.book__price__sum)
  140. )
  141. def test_annotate_values(self):
  142. books = list(Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values())
  143. self.assertEqual(
  144. books, [
  145. {
  146. "contact_id": 1,
  147. "id": 1,
  148. "isbn": "159059725",
  149. "mean_age": 34.5,
  150. "name": "The Definitive Guide to Django: Web Development Done Right",
  151. "pages": 447,
  152. "price": Approximate(Decimal("30")),
  153. "pubdate": datetime.date(2007, 12, 6),
  154. "publisher_id": 1,
  155. "rating": 4.5,
  156. }
  157. ]
  158. )
  159. books = Book.objects.filter(pk=1).annotate(mean_age=Avg('authors__age')).values('pk', 'isbn', 'mean_age')
  160. self.assertEqual(
  161. list(books), [
  162. {
  163. "pk": 1,
  164. "isbn": "159059725",
  165. "mean_age": 34.5,
  166. }
  167. ]
  168. )
  169. books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values("name")
  170. self.assertEqual(
  171. list(books), [
  172. {
  173. "name": "The Definitive Guide to Django: Web Development Done Right"
  174. }
  175. ]
  176. )
  177. books = Book.objects.filter(pk=1).values().annotate(mean_age=Avg('authors__age'))
  178. self.assertEqual(
  179. list(books), [
  180. {
  181. "contact_id": 1,
  182. "id": 1,
  183. "isbn": "159059725",
  184. "mean_age": 34.5,
  185. "name": "The Definitive Guide to Django: Web Development Done Right",
  186. "pages": 447,
  187. "price": Approximate(Decimal("30")),
  188. "pubdate": datetime.date(2007, 12, 6),
  189. "publisher_id": 1,
  190. "rating": 4.5,
  191. }
  192. ]
  193. )
  194. books = Book.objects.values("rating").annotate(n_authors=Count("authors__id"), mean_age=Avg("authors__age")).order_by("rating")
  195. self.assertEqual(
  196. list(books), [
  197. {
  198. "rating": 3.0,
  199. "n_authors": 1,
  200. "mean_age": 45.0,
  201. },
  202. {
  203. "rating": 4.0,
  204. "n_authors": 6,
  205. "mean_age": Approximate(37.16, places=1)
  206. },
  207. {
  208. "rating": 4.5,
  209. "n_authors": 2,
  210. "mean_age": 34.5,
  211. },
  212. {
  213. "rating": 5.0,
  214. "n_authors": 1,
  215. "mean_age": 57.0,
  216. }
  217. ]
  218. )
  219. authors = Author.objects.annotate(Avg("friends__age")).order_by("name")
  220. self.assertEqual(len(authors), 9)
  221. self.assertQuerysetEqual(
  222. authors, [
  223. (u'Adrian Holovaty', 32.0),
  224. (u'Brad Dayley', None),
  225. (u'Jacob Kaplan-Moss', 29.5),
  226. (u'James Bennett', 34.0),
  227. (u'Jeffrey Forcier', 27.0),
  228. (u'Paul Bissex', 31.0),
  229. (u'Peter Norvig', 46.0),
  230. (u'Stuart Russell', 57.0),
  231. (u'Wesley J. Chun', Approximate(33.66, places=1))
  232. ],
  233. lambda a: (a.name, a.friends__age__avg)
  234. )
  235. def test_count(self):
  236. vals = Book.objects.aggregate(Count("rating"))
  237. self.assertEqual(vals, {"rating__count": 6})
  238. vals = Book.objects.aggregate(Count("rating", distinct=True))
  239. self.assertEqual(vals, {"rating__count": 4})
  240. def test_fkey_aggregate(self):
  241. explicit = list(Author.objects.annotate(Count('book__id')))
  242. implicit = list(Author.objects.annotate(Count('book')))
  243. self.assertEqual(explicit, implicit)
  244. def test_annotate_ordering(self):
  245. books = Book.objects.values('rating').annotate(oldest=Max('authors__age')).order_by('oldest', 'rating')
  246. self.assertEqual(
  247. list(books), [
  248. {
  249. "rating": 4.5,
  250. "oldest": 35,
  251. },
  252. {
  253. "rating": 3.0,
  254. "oldest": 45
  255. },
  256. {
  257. "rating": 4.0,
  258. "oldest": 57,
  259. },
  260. {
  261. "rating": 5.0,
  262. "oldest": 57,
  263. }
  264. ]
  265. )
  266. books = Book.objects.values("rating").annotate(oldest=Max("authors__age")).order_by("-oldest", "-rating")
  267. self.assertEqual(
  268. list(books), [
  269. {
  270. "rating": 5.0,
  271. "oldest": 57,
  272. },
  273. {
  274. "rating": 4.0,
  275. "oldest": 57,
  276. },
  277. {
  278. "rating": 3.0,
  279. "oldest": 45,
  280. },
  281. {
  282. "rating": 4.5,
  283. "oldest": 35,
  284. }
  285. ]
  286. )
  287. def test_aggregate_annotation(self):
  288. vals = Book.objects.annotate(num_authors=Count("authors__id")).aggregate(Avg("num_authors"))
  289. self.assertEqual(vals, {"num_authors__avg": Approximate(1.66, places=1)})
  290. def test_filtering(self):
  291. p = Publisher.objects.create(name='Expensive Publisher', num_awards=0)
  292. Book.objects.create(
  293. name='ExpensiveBook1',
  294. pages=1,
  295. isbn='111',
  296. rating=3.5,
  297. price=Decimal("1000"),
  298. publisher=p,
  299. contact_id=1,
  300. pubdate=datetime.date(2008,12,1)
  301. )
  302. Book.objects.create(
  303. name='ExpensiveBook2',
  304. pages=1,
  305. isbn='222',
  306. rating=4.0,
  307. price=Decimal("1000"),
  308. publisher=p,
  309. contact_id=1,
  310. pubdate=datetime.date(2008,12,2)
  311. )
  312. Book.objects.create(
  313. name='ExpensiveBook3',
  314. pages=1,
  315. isbn='333',
  316. rating=4.5,
  317. price=Decimal("35"),
  318. publisher=p,
  319. contact_id=1,
  320. pubdate=datetime.date(2008,12,3)
  321. )
  322. publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
  323. self.assertQuerysetEqual(
  324. publishers, [
  325. "Apress",
  326. "Prentice Hall",
  327. "Expensive Publisher",
  328. ],
  329. lambda p: p.name,
  330. )
  331. publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).order_by("pk")
  332. self.assertQuerysetEqual(
  333. publishers, [
  334. "Apress",
  335. "Apress",
  336. "Sams",
  337. "Prentice Hall",
  338. "Expensive Publisher",
  339. ],
  340. lambda p: p.name
  341. )
  342. publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1, book__price__lt=Decimal("40.0")).order_by("pk")
  343. self.assertQuerysetEqual(
  344. publishers, [
  345. "Apress",
  346. "Prentice Hall",
  347. "Expensive Publisher",
  348. ],
  349. lambda p: p.name,
  350. )
  351. publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
  352. self.assertQuerysetEqual(
  353. publishers, [
  354. "Apress",
  355. ],
  356. lambda p: p.name
  357. )
  358. publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__range=[1, 3]).order_by("pk")
  359. self.assertQuerysetEqual(
  360. publishers, [
  361. "Apress",
  362. "Sams",
  363. "Prentice Hall",
  364. "Morgan Kaufmann",
  365. "Expensive Publisher",
  366. ],
  367. lambda p: p.name
  368. )
  369. publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__range=[1, 2]).order_by("pk")
  370. self.assertQuerysetEqual(
  371. publishers, [
  372. "Apress",
  373. "Sams",
  374. "Prentice Hall",
  375. "Morgan Kaufmann",
  376. ],
  377. lambda p: p.name
  378. )
  379. publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__in=[1, 3]).order_by("pk")
  380. self.assertQuerysetEqual(
  381. publishers, [
  382. "Sams",
  383. "Morgan Kaufmann",
  384. "Expensive Publisher",
  385. ],
  386. lambda p: p.name,
  387. )
  388. publishers = Publisher.objects.annotate(num_books=Count("book")).filter(num_books__isnull=True)
  389. self.assertEqual(len(publishers), 0)
  390. def test_annotation(self):
  391. vals = Author.objects.filter(pk=1).aggregate(Count("friends__id"))
  392. self.assertEqual(vals, {"friends__id__count": 2})
  393. books = Book.objects.annotate(num_authors=Count("authors__name")).filter(num_authors__ge=2).order_by("pk")
  394. self.assertQuerysetEqual(
  395. books, [
  396. "The Definitive Guide to Django: Web Development Done Right",
  397. "Artificial Intelligence: A Modern Approach",
  398. ],
  399. lambda b: b.name
  400. )
  401. authors = Author.objects.annotate(num_friends=Count("friends__id", distinct=True)).filter(num_friends=0).order_by("pk")
  402. self.assertQuerysetEqual(
  403. authors, [
  404. "Brad Dayley",
  405. ],
  406. lambda a: a.name
  407. )
  408. publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
  409. self.assertQuerysetEqual(
  410. publishers, [
  411. "Apress",
  412. "Prentice Hall",
  413. ],
  414. lambda p: p.name
  415. )
  416. publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).annotate(num_books=Count("book__id")).filter(num_books__gt=1)
  417. self.assertQuerysetEqual(
  418. publishers, [
  419. "Apress",
  420. ],
  421. lambda p: p.name
  422. )
  423. books = Book.objects.annotate(num_authors=Count("authors__id")).filter(authors__name__contains="Norvig", num_authors__gt=1)
  424. self.assertQuerysetEqual(
  425. books, [
  426. "Artificial Intelligence: A Modern Approach",
  427. ],
  428. lambda b: b.name
  429. )
  430. def test_more_aggregation(self):
  431. a = Author.objects.get(name__contains='Norvig')
  432. b = Book.objects.get(name__contains='Done Right')
  433. b.authors.add(a)
  434. b.save()
  435. vals = Book.objects.annotate(num_authors=Count("authors__id")).filter(authors__name__contains="Norvig", num_authors__gt=1).aggregate(Avg("rating"))
  436. self.assertEqual(vals, {"rating__avg": 4.25})
  437. def test_even_more_aggregate(self):
  438. publishers = Publisher.objects.annotate(earliest_book=Min("book__pubdate")).exclude(earliest_book=None).order_by("earliest_book").values()
  439. self.assertEqual(
  440. list(publishers), [
  441. {
  442. 'earliest_book': datetime.date(1991, 10, 15),
  443. 'num_awards': 9,
  444. 'id': 4,
  445. 'name': u'Morgan Kaufmann'
  446. },
  447. {
  448. 'earliest_book': datetime.date(1995, 1, 15),
  449. 'num_awards': 7,
  450. 'id': 3,
  451. 'name': u'Prentice Hall'
  452. },
  453. {
  454. 'earliest_book': datetime.date(2007, 12, 6),
  455. 'num_awards': 3,
  456. 'id': 1,
  457. 'name': u'Apress'
  458. },
  459. {
  460. 'earliest_book': datetime.date(2008, 3, 3),
  461. 'num_awards': 1,
  462. 'id': 2,
  463. 'name': u'Sams'
  464. }
  465. ]
  466. )
  467. vals = Store.objects.aggregate(Max("friday_night_closing"), Min("original_opening"))
  468. self.assertEqual(
  469. vals,
  470. {
  471. "friday_night_closing__max": datetime.time(23, 59, 59),
  472. "original_opening__min": datetime.datetime(1945, 4, 25, 16, 24, 14),
  473. }
  474. )
  475. def test_annotate_values_list(self):
  476. books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("pk", "isbn", "mean_age")
  477. self.assertEqual(
  478. list(books), [
  479. (1, "159059725", 34.5),
  480. ]
  481. )
  482. books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("isbn")
  483. self.assertEqual(
  484. list(books), [
  485. ('159059725',)
  486. ]
  487. )
  488. books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("mean_age")
  489. self.assertEqual(
  490. list(books), [
  491. (34.5,)
  492. ]
  493. )
  494. books = Book.objects.filter(pk=1).annotate(mean_age=Avg("authors__age")).values_list("mean_age", flat=True)
  495. self.assertEqual(list(books), [34.5])
  496. books = Book.objects.values_list("price").annotate(count=Count("price")).order_by("-count", "price")
  497. self.assertEqual(
  498. list(books), [
  499. (Decimal("29.69"), 2),
  500. (Decimal('23.09'), 1),
  501. (Decimal('30'), 1),
  502. (Decimal('75'), 1),
  503. (Decimal('82.8'), 1),
  504. ]
  505. )