4
from django.db import connection, models
5
from django.conf import settings
10
from django.utils.itercompat import sorted # For Python 2.3
12
class Author(models.Model):
13
name = models.CharField(max_length=100)
14
age = models.IntegerField()
15
friends = models.ManyToManyField('self', blank=True)
17
def __unicode__(self):
20
class Publisher(models.Model):
21
name = models.CharField(max_length=255)
22
num_awards = models.IntegerField()
24
def __unicode__(self):
27
class Book(models.Model):
28
isbn = models.CharField(max_length=9)
29
name = models.CharField(max_length=255)
30
pages = models.IntegerField()
31
rating = models.FloatField()
32
price = models.DecimalField(decimal_places=2, max_digits=6)
33
authors = models.ManyToManyField(Author)
34
contact = models.ForeignKey(Author, related_name='book_contact_set')
35
publisher = models.ForeignKey(Publisher)
36
pubdate = models.DateField()
41
def __unicode__(self):
44
class Store(models.Model):
45
name = models.CharField(max_length=255)
46
books = models.ManyToManyField(Book)
47
original_opening = models.DateTimeField()
48
friday_night_closing = models.TimeField()
50
def __unicode__(self):
53
class Entries(models.Model):
54
EntryID = models.AutoField(primary_key=True, db_column='Entry ID')
55
Entry = models.CharField(unique=True, max_length=50)
56
Exclude = models.BooleanField()
58
class Clues(models.Model):
59
ID = models.AutoField(primary_key=True)
60
EntryID = models.ForeignKey(Entries, verbose_name='Entry', db_column = 'Entry ID')
61
Clue = models.CharField(max_length=150)
63
class HardbackBook(Book):
64
weight = models.FloatField()
66
def __unicode__(self):
67
return "%s (hardback): %s" % (self.name, self.weight)
69
__test__ = {'API_TESTS': """
70
>>> from django.core import management
71
>>> from django.db.models import get_app, F
73
# Reset the database representation of this app.
74
# This will return the database to a clean initial state.
75
>>> management.call_command('flush', verbosity=0, interactive=False)
77
>>> from django.db.models import Avg, Sum, Count, Max, Min, StdDev, Variance
79
# Ordering requests are ignored
80
>>> Author.objects.all().order_by('name').aggregate(Avg('age'))
83
# Implicit ordering is also ignored
84
>>> Book.objects.all().aggregate(Sum('pages'))
88
>>> Book.objects.all().aggregate(Sum('pages'), Avg('pages'))
89
{'pages__sum': 3703, 'pages__avg': 617.1...}
91
# Empty values query doesn't affect grouping or results
92
>>> Book.objects.all().values().aggregate(Sum('pages'), Avg('pages'))
93
{'pages__sum': 3703, 'pages__avg': 617.1...}
95
# Aggregate overrides extra selected column
96
>>> Book.objects.all().extra(select={'price_per_page' : 'price / pages'}).aggregate(Sum('pages'))
99
# Annotations get combined with extra select clauses
100
>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).__dict__.items())
101
[('contact_id', 3), ('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
103
# Order of the annotate/extra in the query doesn't matter
104
>>> sorted(Book.objects.all().extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2).__dict__.items())
105
[('contact_id', 3), ('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
107
# Values queries can be combined with annotate and extra
108
>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2).items())
109
[('contact_id', 3), ('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
111
# The order of the (empty) values, annotate and extra clauses doesn't matter
112
>>> sorted(Book.objects.all().values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).items())
113
[('contact_id', 3), ('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)]
115
# If the annotation precedes the values clause, it won't be included
116
# unless it is explicitly named
117
>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name').get(pk=1).items())
118
[('name', u'The Definitive Guide to Django: Web Development Done Right')]
120
>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name','mean_auth_age').get(pk=1).items())
121
[('mean_auth_age', 34.5), ('name', u'The Definitive Guide to Django: Web Development Done Right')]
123
# If an annotation isn't included in the values, it can still be used in a filter
124
>>> Book.objects.annotate(n_authors=Count('authors')).values('name').filter(n_authors__gt=2)
125
[{'name': u'Python Web Development with Django'}]
127
# The annotations are added to values output if values() precedes annotate()
128
>>> sorted(Book.objects.all().values('name').annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).get(pk=1).items())
129
[('mean_auth_age', 34.5), ('name', u'The Definitive Guide to Django: Web Development Done Right')]
131
# Check that all of the objects are getting counted (allow_nulls) and that values respects the amount of objects
132
>>> len(Author.objects.all().annotate(Avg('friends__age')).values())
135
# Check that consecutive calls to annotate accumulate in the query
136
>>> Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards'))
137
[{'price': Decimal("30..."), 'oldest': 35, 'publisher__num_awards__max': 3}, {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7}, {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1}, {'price': Decimal("75..."), 'oldest': 57, 'publisher__num_awards__max': 9}, {'price': Decimal("82.8..."), 'oldest': 57, 'publisher__num_awards__max': 7}]
139
# Aggregates can be composed over annotations.
140
# The return type is derived from the composed aggregate
141
>>> Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('pages'), Max('price'), Sum('num_authors'), Avg('num_authors'))
142
{'num_authors__sum': 10, 'num_authors__avg': 1.66..., 'pages__max': 1132, 'price__max': Decimal("82.80")}
144
# Bad field requests in aggregates are caught and reported
145
>>> Book.objects.all().aggregate(num_authors=Count('foo'))
146
Traceback (most recent call last):
148
FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, contact, hardbackbook, id, isbn, name, pages, price, pubdate, publisher, rating, store
150
>>> Book.objects.all().annotate(num_authors=Count('foo'))
151
Traceback (most recent call last):
153
FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, contact, hardbackbook, id, isbn, name, pages, price, pubdate, publisher, rating, store
155
>>> Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('foo'))
156
Traceback (most recent call last):
158
FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, contact, hardbackbook, id, isbn, name, pages, price, pubdate, publisher, rating, store, num_authors
160
# Old-style count aggregations can be mixed with new-style
161
>>> Book.objects.annotate(num_authors=Count('authors')).count()
164
# Non-ordinal, non-computed Aggregates over annotations correctly inherit
165
# the annotation's internal type if the annotation is ordinal or computed
166
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Max('num_authors'))
167
{'num_authors__max': 3}
169
>>> Publisher.objects.annotate(avg_price=Avg('book__price')).aggregate(Max('avg_price'))
170
{'avg_price__max': 75.0...}
172
# Aliases are quoted to protected aliases that might be reserved names
173
>>> Book.objects.aggregate(number=Max('pages'), select=Max('pages'))
174
{'number': 1132, 'select': 1132}
176
# Regression for #10064: select_related() plays nice with aggregates
177
>>> Book.objects.select_related('publisher').annotate(num_authors=Count('authors')).values()[0]
178
{'rating': 4.0, 'isbn': u'013790395', 'name': u'Artificial Intelligence: A Modern Approach', 'pubdate': datetime.date(1995, 1, 15), 'price': Decimal("82.8..."), 'contact_id': 8, 'id': 5, 'num_authors': 2, 'publisher_id': 3, 'pages': 1132}
180
# Regression for #10010: exclude on an aggregate field is correctly negated
181
>>> len(Book.objects.annotate(num_authors=Count('authors')))
183
>>> len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=2))
185
>>> len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__gt=2))
188
>>> len(Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__lt=3).exclude(num_authors__lt=2))
190
>>> len(Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__lt=2).filter(num_authors__lt=3))
193
# Aggregates can be used with F() expressions
194
# ... where the F() is pushed into the HAVING clause
195
>>> Publisher.objects.annotate(num_books=Count('book')).filter(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
196
[{'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9}, {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}]
198
>>> Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
199
[{'num_books': 2, 'name': u'Apress', 'num_awards': 3}, {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0}, {'num_books': 1, 'name': u'Sams', 'num_awards': 1}]
201
# ... and where the F() references an aggregate
202
>>> Publisher.objects.annotate(num_books=Count('book')).filter(num_awards__gt=2*F('num_books')).order_by('name').values('name','num_books','num_awards')
203
[{'num_books': 1, 'name': u'Morgan Kaufmann', 'num_awards': 9}, {'num_books': 2, 'name': u'Prentice Hall', 'num_awards': 7}]
205
>>> Publisher.objects.annotate(num_books=Count('book')).exclude(num_books__lt=F('num_awards')/2).order_by('name').values('name','num_books','num_awards')
206
[{'num_books': 2, 'name': u'Apress', 'num_awards': 3}, {'num_books': 0, 'name': u"Jonno's House of Books", 'num_awards': 0}, {'num_books': 1, 'name': u'Sams', 'num_awards': 1}]
208
# Tests on fields with non-default table and column names.
209
>>> Clues.objects.values('EntryID__Entry').annotate(Appearances=Count('EntryID'), Distinct_Clues=Count('Clue', distinct=True))
212
>>> Entries.objects.annotate(clue_count=Count('clues__ID'))
215
# Regression for #10089: Check handling of empty result sets with aggregates
216
>>> Book.objects.filter(id__in=[]).count()
219
>>> 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'))
220
{'max_authors': None, 'max_rating': None, 'num_authors': 0, 'avg_authors': None, 'max_price': None}
222
>>> 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()
223
[{'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}]
225
# Regression for #10113 - Fields mentioned in order_by() must be included in the GROUP BY.
226
# This only becomes a problem when the order_by introduces a new join.
227
>>> Book.objects.annotate(num_authors=Count('authors')).order_by('publisher__name', 'name')
228
[<Book: Practical Django Projects>, <Book: The Definitive Guide to Django: Web Development Done Right>, <Book: Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp>, <Book: Artificial Intelligence: A Modern Approach>, <Book: Python Web Development with Django>, <Book: Sams Teach Yourself Django in 24 Hours>]
230
# Regression for #10127 - Empty select_related() works with annotate
231
>>> books = Book.objects.all().filter(rating__lt=4.5).select_related().annotate(Avg('authors__age'))
232
>>> sorted([(b.name, b.authors__age__avg, b.publisher.name, b.contact.name) for b in books])
233
[(u'Artificial Intelligence: A Modern Approach', 51.5, u'Prentice Hall', u'Peter Norvig'), (u'Practical Django Projects', 29.0, u'Apress', u'James Bennett'), (u'Python Web Development with Django', 30.3..., u'Prentice Hall', u'Jeffrey Forcier'), (u'Sams Teach Yourself Django in 24 Hours', 45.0, u'Sams', u'Brad Dayley')]
235
# Regression for #10132 - If the values() clause only mentioned extra(select=) columns, those columns are used for grouping
236
>>> Book.objects.extra(select={'pub':'publisher_id'}).values('pub').annotate(Count('id')).order_by('pub')
237
[{'pub': 1, 'id__count': 2}, {'pub': 2, 'id__count': 1}, {'pub': 3, 'id__count': 2}, {'pub': 4, 'id__count': 1}]
239
>>> Book.objects.extra(select={'pub':'publisher_id','foo':'pages'}).values('pub').annotate(Count('id')).order_by('pub')
240
[{'pub': 1, 'id__count': 2}, {'pub': 2, 'id__count': 1}, {'pub': 3, 'id__count': 2}, {'pub': 4, 'id__count': 1}]
242
# Regression for #10182 - Queries with aggregate calls are correctly realiased when used in a subquery
243
>>> ids = Book.objects.filter(pages__gt=100).annotate(n_authors=Count('authors')).filter(n_authors__gt=2).order_by('n_authors')
244
>>> Book.objects.filter(id__in=ids)
245
[<Book: Python Web Development with Django>]
247
# Regression for #10197 -- Queries with aggregates can be pickled.
248
# First check that pickling is possible at all. No crash = success
249
>>> qs = Book.objects.annotate(num_authors=Count('authors'))
250
>>> out = pickle.dumps(qs)
252
# Then check that the round trip works.
253
>>> query = qs.query.as_sql()[0]
254
>>> select_fields = qs.query.select_fields
255
>>> query2 = pickle.loads(pickle.dumps(qs))
256
>>> query2.query.as_sql()[0] == query
258
>>> query2.query.select_fields = select_fields
260
# Regression for #10199 - Aggregate calls clone the original query so the original query can still be used
261
>>> books = Book.objects.all()
262
>>> _ = books.aggregate(Avg('authors__age'))
264
[<Book: Artificial Intelligence: A Modern Approach>, <Book: Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp>, <Book: Practical Django Projects>, <Book: Python Web Development with Django>, <Book: Sams Teach Yourself Django in 24 Hours>, <Book: The Definitive Guide to Django: Web Development Done Right>]
266
# Regression for #10248 - Annotations work with DateQuerySets
267
>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors=2).dates('pubdate', 'day')
268
[datetime.datetime(1995, 1, 15, 0, 0), datetime.datetime(2007, 12, 6, 0, 0)]
270
# Regression for #10290 - extra selects with parameters can be used for
272
>>> qs = Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'sheets' : '(pages + %s) / %s'}, select_params=[1, 2]).order_by('sheets').values('sheets')
273
>>> [int(x['sheets']) for x in qs]
274
[150, 175, 224, 264, 473, 566]
276
# Regression for 10425 - annotations don't get in the way of a count() clause
277
>>> Book.objects.values('publisher').annotate(Count('publisher')).count()
280
>>> Book.objects.annotate(Count('publisher')).values('publisher').count()
283
>>> publishers = Publisher.objects.filter(id__in=(1,2))
285
[<Publisher: Apress>, <Publisher: Sams>]
287
>>> publishers = publishers.annotate(n_books=models.Count('book'))
288
>>> publishers[0].n_books
292
[<Publisher: Apress>, <Publisher: Sams>]
294
>>> books = Book.objects.filter(publisher__in=publishers)
296
[<Book: Practical Django Projects>, <Book: Sams Teach Yourself Django in 24 Hours>, <Book: The Definitive Guide to Django: Web Development Done Right>]
299
[<Publisher: Apress>, <Publisher: Sams>]
302
# Regression for 10666 - inherited fields work with annotations and aggregations
303
>>> HardbackBook.objects.aggregate(n_pages=Sum('book_ptr__pages'))
306
>>> HardbackBook.objects.aggregate(n_pages=Sum('pages'))
309
>>> HardbackBook.objects.annotate(n_authors=Count('book_ptr__authors')).values('name','n_authors')
310
[{'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'}, {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}]
312
>>> HardbackBook.objects.annotate(n_authors=Count('authors')).values('name','n_authors')
313
[{'n_authors': 2, 'name': u'Artificial Intelligence: A Modern Approach'}, {'n_authors': 1, 'name': u'Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp'}]
315
# Regression for #10766 - Shouldn't be able to reference an aggregate fields in an an aggregate() call.
316
>>> Book.objects.all().annotate(mean_age=Avg('authors__age')).annotate(Avg('mean_age'))
317
Traceback (most recent call last):
319
FieldError: Cannot compute Avg('mean_age'): 'mean_age' is an aggregate
324
def run_stddev_tests():
325
"""Check to see if StdDev/Variance tests should be run.
327
Stddev and Variance are not guaranteed to be available for SQLite, and
328
are not available for PostgreSQL before 8.2.
330
if settings.DATABASE_ENGINE == 'sqlite3':
333
class StdDevPop(object):
334
sql_function = 'STDDEV_POP'
337
connection.ops.check_aggregate_support(StdDevPop())
342
if run_stddev_tests():
343
__test__['API_TESTS'] += """
344
>>> Book.objects.aggregate(StdDev('pages'))
345
{'pages__stddev': 311.46...}
347
>>> Book.objects.aggregate(StdDev('rating'))
348
{'rating__stddev': 0.60...}
350
>>> Book.objects.aggregate(StdDev('price'))
351
{'price__stddev': 24.16...}
354
>>> Book.objects.aggregate(StdDev('pages', sample=True))
355
{'pages__stddev': 341.19...}
357
>>> Book.objects.aggregate(StdDev('rating', sample=True))
358
{'rating__stddev': 0.66...}
360
>>> Book.objects.aggregate(StdDev('price', sample=True))
361
{'price__stddev': 26.46...}
364
>>> Book.objects.aggregate(Variance('pages'))
365
{'pages__variance': 97010.80...}
367
>>> Book.objects.aggregate(Variance('rating'))
368
{'rating__variance': 0.36...}
370
>>> Book.objects.aggregate(Variance('price'))
371
{'price__variance': 583.77...}
374
>>> Book.objects.aggregate(Variance('pages', sample=True))
375
{'pages__variance': 116412.96...}
377
>>> Book.objects.aggregate(Variance('rating', sample=True))
378
{'rating__variance': 0.44...}
380
>>> Book.objects.aggregate(Variance('price', sample=True))
381
{'price__variance': 700.53...}
added
removed
tests/regressiontests/aggregation_regress/models.py
