diff options
Diffstat (limited to 'tests')
| -rw-r--r-- | tests/aggregation/tests.py | 279 | ||||
| -rw-r--r-- | tests/annotations/__init__.py | 0 | ||||
| -rw-r--r-- | tests/annotations/fixtures/annotations.json | 243 | ||||
| -rw-r--r-- | tests/annotations/models.py | 86 | ||||
| -rw-r--r-- | tests/annotations/tests.py | 288 | ||||
| -rw-r--r-- | tests/expressions/tests.py | 23 |
6 files changed, 916 insertions, 3 deletions
diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py index b1b6199ffa..851ce69db0 100644 --- a/tests/aggregation/tests.py +++ b/tests/aggregation/tests.py @@ -3,12 +3,21 @@ from __future__ import unicode_literals import datetime from decimal import Decimal import re +import warnings +from django.core.exceptions import FieldError from django.db import connection -from django.db.models import Avg, Sum, Count, Max, Min +from django.db.models import ( + Avg, Sum, Count, Max, Min, + Aggregate, F, Value, Func, + IntegerField, FloatField, DecimalField) +with warnings.catch_warnings(record=True) as w: + warnings.simplefilter("always") + from django.db.models.sql import aggregates as sql_aggregates from django.test import TestCase from django.test.utils import Approximate from django.test.utils import CaptureQueriesContext +from django.utils.deprecation import RemovedInDjango20Warning from .models import Author, Publisher, Book, Store @@ -678,3 +687,271 @@ class BaseAggregateTestCase(TestCase): else: self.assertNotIn('order by', qstr) self.assertEqual(qstr.count(' join '), 0) + + +class ComplexAggregateTestCase(TestCase): + fixtures = ["aggregation.json"] + + def test_nonaggregate_aggregation_throws(self): + with self.assertRaisesRegexp(TypeError, 'fail is not an aggregate expression'): + Book.objects.aggregate(fail=F('price')) + + def test_nonfield_annotation(self): + book = Book.objects.annotate(val=Max(Value(2, output_field=IntegerField())))[0] + self.assertEqual(book.val, 2) + book = Book.objects.annotate(val=Max(Value(2), output_field=IntegerField()))[0] + self.assertEqual(book.val, 2) + + def test_missing_output_field_raises_error(self): + with self.assertRaisesRegexp(FieldError, 'Cannot resolve expression type, unknown output_field'): + Book.objects.annotate(val=Max(Value(2)))[0] + + def test_annotation_expressions(self): + authors = Author.objects.annotate(combined_ages=Sum(F('age') + F('friends__age'))).order_by('name') + authors2 = Author.objects.annotate(combined_ages=Sum('age') + Sum('friends__age')).order_by('name') + for qs in (authors, authors2): + self.assertEqual(len(qs), 9) + self.assertQuerysetEqual( + qs, [ + ('Adrian Holovaty', 132), + ('Brad Dayley', None), + ('Jacob Kaplan-Moss', 129), + ('James Bennett', 63), + ('Jeffrey Forcier', 128), + ('Paul Bissex', 120), + ('Peter Norvig', 103), + ('Stuart Russell', 103), + ('Wesley J. Chun', 176) + ], + lambda a: (a.name, a.combined_ages) + ) + + def test_aggregation_expressions(self): + a1 = Author.objects.aggregate(av_age=Sum('age') / Count('*')) + a2 = Author.objects.aggregate(av_age=Sum('age') / Count('age')) + a3 = Author.objects.aggregate(av_age=Avg('age')) + self.assertEqual(a1, {'av_age': 37}) + self.assertEqual(a2, {'av_age': 37}) + self.assertEqual(a3, {'av_age': Approximate(37.4, places=1)}) + + def test_order_of_precedence(self): + p1 = Book.objects.filter(rating=4).aggregate(avg_price=(Avg('price') + 2) * 3) + self.assertEqual(p1, {'avg_price': Approximate(148.18, places=2)}) + + p2 = Book.objects.filter(rating=4).aggregate(avg_price=Avg('price') + 2 * 3) + self.assertEqual(p2, {'avg_price': Approximate(53.39, places=2)}) + + def test_combine_different_types(self): + with self.assertRaisesRegexp(FieldError, 'Expression contains mixed types. You must set output_field'): + Book.objects.annotate(sums=Sum('rating') + Sum('pages') + Sum('price')).get(pk=4) + + b1 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=IntegerField())).get(pk=4) + self.assertEqual(b1.sums, 383) + + b2 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=FloatField())).get(pk=4) + self.assertEqual(b2.sums, 383.69) + + b3 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=DecimalField(max_digits=6, decimal_places=2))).get(pk=4) + self.assertEqual(b3.sums, Decimal("383.69")) + + def test_complex_aggregations_require_kwarg(self): + with self.assertRaisesRegexp(TypeError, 'Complex expressions require an alias'): + Author.objects.annotate(Sum(F('age') + F('friends__age'))) + with self.assertRaisesRegexp(TypeError, 'Complex aggregates require an alias'): + Author.objects.aggregate(Sum('age') / Count('age')) + + def test_aggregate_over_complex_annotation(self): + qs = Author.objects.annotate( + combined_ages=Sum(F('age') + F('friends__age'))) + + age = qs.aggregate(max_combined_age=Max('combined_ages')) + self.assertEqual(age['max_combined_age'], 176) + + age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') * 2) + self.assertEqual(age['max_combined_age_doubled'], 176 * 2) + + age = qs.aggregate( + max_combined_age_doubled=Max('combined_ages') + Max('combined_ages')) + self.assertEqual(age['max_combined_age_doubled'], 176 * 2) + + age = qs.aggregate( + max_combined_age_doubled=Max('combined_ages') + Max('combined_ages'), + sum_combined_age=Sum('combined_ages')) + self.assertEqual(age['max_combined_age_doubled'], 176 * 2) + self.assertEqual(age['sum_combined_age'], 954) + + age = qs.aggregate( + max_combined_age_doubled=Max('combined_ages') + Max('combined_ages'), + sum_combined_age_doubled=Sum('combined_ages') + Sum('combined_ages')) + self.assertEqual(age['max_combined_age_doubled'], 176 * 2) + self.assertEqual(age['sum_combined_age_doubled'], 954 * 2) + + def test_values_annotation_with_expression(self): + # ensure the F() is promoted to the group by clause + qs = Author.objects.values('name').annotate(another_age=Sum('age') + F('age')) + a = qs.get(pk=1) + self.assertEqual(a['another_age'], 68) + + qs = qs.annotate(friend_count=Count('friends')) + a = qs.get(pk=1) + self.assertEqual(a['friend_count'], 2) + + qs = qs.annotate(combined_age=Sum('age') + F('friends__age')).filter(pk=1).order_by('-combined_age') + self.assertEqual( + list(qs), [ + { + "name": 'Adrian Holovaty', + "another_age": 68, + "friend_count": 1, + "combined_age": 69 + }, + { + "name": 'Adrian Holovaty', + "another_age": 68, + "friend_count": 1, + "combined_age": 63 + } + ] + ) + + vals = qs.values('name', 'combined_age') + self.assertEqual( + list(vals), [ + { + "name": 'Adrian Holovaty', + "combined_age": 69 + }, + { + "name": 'Adrian Holovaty', + "combined_age": 63 + } + ] + ) + + def test_annotate_values_aggregate(self): + alias_age = Author.objects.annotate( + age_alias=F('age') + ).values( + 'age_alias', + ).aggregate(sum_age=Sum('age_alias')) + + age = Author.objects.values('age').aggregate(sum_age=Sum('age')) + + self.assertEqual(alias_age['sum_age'], age['sum_age']) + + def test_annotate_over_annotate(self): + author = Author.objects.annotate( + age_alias=F('age') + ).annotate( + sum_age=Sum('age_alias') + ).get(pk=1) + + other_author = Author.objects.annotate( + sum_age=Sum('age') + ).get(pk=1) + + self.assertEqual(author.sum_age, other_author.sum_age) + + def test_annotated_aggregate_over_annotated_aggregate(self): + with self.assertRaisesRegexp(FieldError, "Cannot compute Sum\('id__max'\): 'id__max' is an aggregate"): + Book.objects.annotate(Max('id')).annotate(Sum('id__max')) + + def test_add_implementation(self): + try: + # test completely changing how the output is rendered + def lower_case_function_override(self, qn, connection): + sql, params = qn.compile(self.source_expressions[0]) + substitutions = dict(function=self.function.lower(), expressions=sql) + substitutions.update(self.extra) + return self.template % substitutions, params + setattr(Sum, 'as_' + connection.vendor, lower_case_function_override) + + qs = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=IntegerField())) + self.assertEqual(str(qs.query).count('sum('), 1) + b1 = qs.get(pk=4) + self.assertEqual(b1.sums, 383) + + # test changing the dict and delegating + def lower_case_function_super(self, qn, connection): + self.extra['function'] = self.function.lower() + return super(Sum, self).as_sql(qn, connection) + setattr(Sum, 'as_' + connection.vendor, lower_case_function_super) + + qs = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=IntegerField())) + self.assertEqual(str(qs.query).count('sum('), 1) + b1 = qs.get(pk=4) + self.assertEqual(b1.sums, 383) + + # test overriding all parts of the template + def be_evil(self, qn, connection): + substitutions = dict(function='MAX', expressions='2') + substitutions.update(self.extra) + return self.template % substitutions, () + setattr(Sum, 'as_' + connection.vendor, be_evil) + + qs = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'), + output_field=IntegerField())) + self.assertEqual(str(qs.query).count('MAX('), 1) + b1 = qs.get(pk=4) + self.assertEqual(b1.sums, 2) + finally: + delattr(Sum, 'as_' + connection.vendor) + + def test_complex_values_aggregation(self): + max_rating = Book.objects.values('rating').aggregate( + double_max_rating=Max('rating') + Max('rating')) + self.assertEqual(max_rating['double_max_rating'], 5 * 2) + + max_books_per_rating = Book.objects.values('rating').annotate( + books_per_rating=Count('id') + 5 + ).aggregate(Max('books_per_rating')) + self.assertEqual( + max_books_per_rating, + {'books_per_rating__max': 3 + 5}) + + def test_expression_on_aggregation(self): + + # Create a plain expression + class Greatest(Func): + function = 'GREATEST' + + def as_sqlite(self, qn, connection): + return super(Greatest, self).as_sql(qn, connection, function='MAX') + + qs = Publisher.objects.annotate( + price_or_median=Greatest(Avg('book__rating'), Avg('book__price')) + ).filter(price_or_median__gte=F('num_awards')).order_by('pk') + self.assertQuerysetEqual( + qs, [1, 2, 3, 4], lambda v: v.pk) + + qs2 = Publisher.objects.annotate( + rating_or_num_awards=Greatest(Avg('book__rating'), F('num_awards'), + output_field=FloatField()) + ).filter(rating_or_num_awards__gt=F('num_awards')).order_by('pk') + self.assertQuerysetEqual( + qs2, [1, 2], lambda v: v.pk) + + def test_backwards_compatibility(self): + + class SqlNewSum(sql_aggregates.Aggregate): + sql_function = 'SUM' + + class NewSum(Aggregate): + name = 'Sum' + + def add_to_query(self, query, alias, col, source, is_summary): + klass = SqlNewSum + aggregate = klass( + col, source=source, is_summary=is_summary, **self.extra) + query.annotations[alias] = aggregate + + with warnings.catch_warnings(): + warnings.simplefilter("ignore", RemovedInDjango20Warning) + qs = Author.objects.values('name').annotate(another_age=NewSum('age') + F('age')) + a = qs.get(pk=1) + self.assertEqual(a['another_age'], 68) diff --git a/tests/annotations/__init__.py b/tests/annotations/__init__.py new file mode 100644 index 0000000000..e69de29bb2 --- /dev/null +++ b/tests/annotations/__init__.py diff --git a/tests/annotations/fixtures/annotations.json b/tests/annotations/fixtures/annotations.json new file mode 100644 index 0000000000..09c9b8346b --- /dev/null +++ b/tests/annotations/fixtures/annotations.json @@ -0,0 +1,243 @@ +[ + { + "pk": 1, + "model": "annotations.publisher", + "fields": { + "name": "Apress", + "num_awards": 3 + } + }, + { + "pk": 2, + "model": "annotations.publisher", + "fields": { + "name": "Sams", + "num_awards": 1 + } + }, + { + "pk": 3, + "model": "annotations.publisher", + "fields": { + "name": "Prentice Hall", + "num_awards": 7 + } + }, + { + "pk": 4, + "model": "annotations.publisher", + "fields": { + "name": "Morgan Kaufmann", + "num_awards": 9 + } + }, + { + "pk": 5, + "model": "annotations.publisher", + "fields": { + "name": "Jonno's House of Books", + "num_awards": 0 + } + }, + { + "pk": 1, + "model": "annotations.book", + "fields": { + "publisher": 1, + "isbn": "159059725", + "name": "The Definitive Guide to Django: Web Development Done Right", + "price": "30.00", + "rating": 4.5, + "authors": [1, 2], + "contact": 1, + "pages": 447, + "pubdate": "2007-12-6" + } + }, + { + "pk": 2, + "model": "annotations.book", + "fields": { + "publisher": 2, + "isbn": "067232959", + "name": "Sams Teach Yourself Django in 24 Hours", + "price": "23.09", + "rating": 3.0, + "authors": [3], + "contact": 3, + "pages": 528, + "pubdate": "2008-3-3" + } + }, + { + "pk": 3, + "model": "annotations.book", + "fields": { + "publisher": 1, + "isbn": "159059996", + "name": "Practical Django Projects", + "price": "29.69", + "rating": 4.0, + "authors": [4], + "contact": 4, + "pages": 300, + "pubdate": "2008-6-23" + } + }, + { + "pk": 4, + "model": "annotations.book", + "fields": { + "publisher": 3, + "isbn": "013235613", + "name": "Python Web Development with Django", + "price": "29.69", + "rating": 4.0, + "authors": [5, 6, 7], + "contact": 5, + "pages": 350, + "pubdate": "2008-11-3" + } + }, + { + "pk": 5, + "model": "annotations.book", + "fields": { + "publisher": 3, + "isbn": "013790395", + "name": "Artificial Intelligence: A Modern Approach", + "price": "82.80", + "rating": 4.0, + "authors": [8, 9], + "contact": 8, + "pages": 1132, + "pubdate": "1995-1-15" + } + }, + { + "pk": 6, + "model": "annotations.book", + "fields": { + "publisher": 4, + "isbn": "155860191", + "name": "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp", + "price": "75.00", + "rating": 5.0, + "authors": [8], + "contact": 8, + "pages": 946, + "pubdate": "1991-10-15" + } + }, + { + "pk": 1, + "model": "annotations.store", + "fields": { + "books": [1, 2, 3, 4, 5, 6], + "name": "Amazon.com", + "original_opening": "1994-4-23 9:17:42", + "friday_night_closing": "23:59:59" + } + }, + { + "pk": 2, + "model": "annotations.store", + "fields": { + "books": [1, 3, 5, 6], + "name": "Books.com", + "original_opening": "2001-3-15 11:23:37", + "friday_night_closing": "23:59:59" + } + }, + { + "pk": 3, + "model": "annotations.store", + "fields": { + "books": [3, 4, 6], + "name": "Mamma and Pappa's Books", + "original_opening": "1945-4-25 16:24:14", + "friday_night_closing": "21:30:00" + } + }, + { + "pk": 1, + "model": "annotations.author", + "fields": { + "age": 34, + "friends": [2, 4], + "name": "Adrian Holovaty" + } + }, + { + "pk": 2, + "model": "annotations.author", + "fields": { + "age": 35, + "friends": [1, 7], + "name": "Jacob Kaplan-Moss" + } + }, + { + "pk": 3, + "model": "annotations.author", + "fields": { + "age": 45, + "friends": [], + "name": "Brad Dayley" + } + }, + { + "pk": 4, + "model": "annotations.author", + "fields": { + "age": 29, + "friends": [1], + "name": "James Bennett" + } + }, + { + "pk": 5, + "model": "annotations.author", + "fields": { + "age": 37, + "friends": [6, 7], + "name": "Jeffrey Forcier" + } + }, + { + "pk": 6, + "model": "annotations.author", + "fields": { + "age": 29, + "friends": [5, 7], + "name": "Paul Bissex" + } + }, + { + "pk": 7, + "model": "annotations.author", + "fields": { + "age": 25, + "friends": [2, 5, 6], + "name": "Wesley J. Chun" + } + }, + { + "pk": 8, + "model": "annotations.author", + "fields": { + "age": 57, + "friends": [9], + "name": "Peter Norvig" + } + }, + { + "pk": 9, + "model": "annotations.author", + "fields": { + "age": 46, + "friends": [8], + "name": "Stuart Russell" + } + } +] diff --git a/tests/annotations/models.py b/tests/annotations/models.py new file mode 100644 index 0000000000..0c438b99f8 --- /dev/null +++ b/tests/annotations/models.py @@ -0,0 +1,86 @@ +# coding: utf-8 +from django.db import models +from django.utils.encoding import python_2_unicode_compatible + + +@python_2_unicode_compatible +class Author(models.Model): + name = models.CharField(max_length=100) + age = models.IntegerField() + friends = models.ManyToManyField('self', blank=True) + + def __str__(self): + return self.name + + +@python_2_unicode_compatible +class Publisher(models.Model): + name = models.CharField(max_length=255) + num_awards = models.IntegerField() + + def __str__(self): + return self.name + + +@python_2_unicode_compatible +class Book(models.Model): + isbn = models.CharField(max_length=9) + name = models.CharField(max_length=255) + pages = models.IntegerField() + rating = models.FloatField() + price = models.DecimalField(decimal_places=2, max_digits=6) + authors = models.ManyToManyField(Author) + contact = models.ForeignKey(Author, related_name='book_contact_set') + publisher = models.ForeignKey(Publisher) + pubdate = models.DateField() + + def __str__(self): + return self.name + + +@python_2_unicode_compatible +class Store(models.Model): + name = models.CharField(max_length=255) + books = models.ManyToManyField(Book) + original_opening = models.DateTimeField() + friday_night_closing = models.TimeField() + + def __str__(self): + return self.name + + +@python_2_unicode_compatible +class DepartmentStore(Store): + chain = models.CharField(max_length=255) + + def __str__(self): + return '%s - %s ' % (self.chain, self.name) + + +@python_2_unicode_compatible +class Employee(models.Model): + # The order of these fields matter, do not change. Certain backends + # rely on field ordering to perform database conversions, and this + # model helps to test that. + first_name = models.CharField(max_length=20) + manager = models.BooleanField(default=False) + last_name = models.CharField(max_length=20) + store = models.ForeignKey(Store) + age = models.IntegerField() + salary = models.DecimalField(max_digits=8, decimal_places=2) + + def __str__(self): + return '%s %s' % (self.first_name, self.last_name) + + +@python_2_unicode_compatible +class Company(models.Model): + name = models.CharField(max_length=200) + motto = models.CharField(max_length=200, null=True, blank=True) + ticker_name = models.CharField(max_length=10, null=True, blank=True) + description = models.CharField(max_length=200, null=True, blank=True) + + def __str__(self): + return ('Company(name=%s, motto=%s, ticker_name=%s, description=%s)' + % (self.name, self.motto, self.ticker_name, self.description) + ) diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py new file mode 100644 index 0000000000..afc23b85df --- /dev/null +++ b/tests/annotations/tests.py @@ -0,0 +1,288 @@ +from __future__ import unicode_literals +import datetime +from decimal import Decimal + +from django.core.exceptions import FieldError +from django.db.models import ( + Sum, Count, + F, Value, Func, + IntegerField, BooleanField, CharField) +from django.db.models.fields import FieldDoesNotExist +from django.test import TestCase + +from .models import Author, Book, Store, DepartmentStore, Company, Employee + + +class NonAggregateAnnotationTestCase(TestCase): + fixtures = ["annotations.json"] + + def test_basic_annotation(self): + books = Book.objects.annotate( + is_book=Value(1, output_field=IntegerField())) + for book in books: + self.assertEqual(book.is_book, 1) + + def test_basic_f_annotation(self): + books = Book.objects.annotate(another_rating=F('rating')) + for book in books: + self.assertEqual(book.another_rating, book.rating) + + def test_joined_annotation(self): + books = Book.objects.select_related('publisher').annotate( + num_awards=F('publisher__num_awards')) + for book in books: + self.assertEqual(book.num_awards, book.publisher.num_awards) + + def test_annotate_with_aggregation(self): + books = Book.objects.annotate( + is_book=Value(1, output_field=IntegerField()), + rating_count=Count('rating')) + for book in books: + self.assertEqual(book.is_book, 1) + self.assertEqual(book.rating_count, 1) + + def test_aggregate_over_annotation(self): + agg = Author.objects.annotate(other_age=F('age')).aggregate(otherage_sum=Sum('other_age')) + other_agg = Author.objects.aggregate(age_sum=Sum('age')) + self.assertEqual(agg['otherage_sum'], other_agg['age_sum']) + + def test_filter_annotation(self): + books = Book.objects.annotate( + is_book=Value(1, output_field=IntegerField()) + ).filter(is_book=1) + for book in books: + self.assertEqual(book.is_book, 1) + + def test_filter_annotation_with_f(self): + books = Book.objects.annotate( + other_rating=F('rating') + ).filter(other_rating=3.5) + for book in books: + self.assertEqual(book.other_rating, 3.5) + + def test_filter_annotation_with_double_f(self): + books = Book.objects.annotate( + other_rating=F('rating') + ).filter(other_rating=F('rating')) + for book in books: + self.assertEqual(book.other_rating, book.rating) + + def test_filter_agg_with_double_f(self): + books = Book.objects.annotate( + sum_rating=Sum('rating') + ).filter(sum_rating=F('sum_rating')) + for book in books: + self.assertEqual(book.sum_rating, book.rating) + + def test_filter_wrong_annotation(self): + with self.assertRaisesRegexp(FieldError, "Cannot resolve keyword .*"): + list(Book.objects.annotate( + sum_rating=Sum('rating') + ).filter(sum_rating=F('nope'))) + + def test_update_with_annotation(self): + book_preupdate = Book.objects.get(pk=2) + Book.objects.annotate(other_rating=F('rating') - 1).update(rating=F('other_rating')) + book_postupdate = Book.objects.get(pk=2) + self.assertEqual(book_preupdate.rating - 1, book_postupdate.rating) + + def test_annotation_with_m2m(self): + books = Book.objects.annotate(author_age=F('authors__age')).filter(pk=1).order_by('author_age') + self.assertEqual(books[0].author_age, 34) + self.assertEqual(books[1].author_age, 35) + + def test_annotation_reverse_m2m(self): + books = Book.objects.annotate( + store_name=F('store__name')).filter( + name='Practical Django Projects').order_by( + 'store_name') + + self.assertQuerysetEqual( + books, [ + 'Amazon.com', + 'Books.com', + 'Mamma and Pappa\'s Books' + ], + lambda b: b.store_name + ) + + def test_values_annotation(self): + """ + Annotations can reference fields in a values clause, + and contribute to an existing values clause. + """ + # annotate references a field in values() + qs = Book.objects.values('rating').annotate(other_rating=F('rating') - 1) + book = qs.get(pk=1) + self.assertEqual(book['rating'] - 1, book['other_rating']) + + # filter refs the annotated value + book = qs.get(other_rating=4) + self.assertEqual(book['other_rating'], 4) + + # can annotate an existing values with a new field + book = qs.annotate(other_isbn=F('isbn')).get(other_rating=4) + self.assertEqual(book['other_rating'], 4) + self.assertEqual(book['other_isbn'], '155860191') + + def test_defer_annotation(self): + """ + Deferred attributes can be referenced by an annotation, + but they are not themselves deferred, and cannot be deferred. + """ + qs = Book.objects.defer('rating').annotate(other_rating=F('rating') - 1) + + with self.assertNumQueries(2): + book = qs.get(other_rating=4) + self.assertEqual(book.rating, 5) + self.assertEqual(book.other_rating, 4) + + with self.assertRaisesRegexp(FieldDoesNotExist, "\w has no field named u?'other_rating'"): + book = qs.defer('other_rating').get(other_rating=4) + + def test_mti_annotations(self): + """ + Fields on an inherited model can be referenced by an + annotated field. + """ + d = DepartmentStore.objects.create( + name='Angus & Robinson', + original_opening=datetime.date(2014, 3, 8), + friday_night_closing=datetime.time(21, 00, 00), + chain='Westfield' + ) + + books = Book.objects.filter(rating__gt=4) + for b in books: + d.books.add(b) + + qs = DepartmentStore.objects.annotate( + other_name=F('name'), + other_chain=F('chain'), + is_open=Value(True, BooleanField()), + book_isbn=F('books__isbn') + ).select_related('store').order_by('book_isbn').filter(chain='Westfield') + + self.assertQuerysetEqual( + qs, [ + ('Angus & Robinson', 'Westfield', True, '155860191'), + ('Angus & Robinson', 'Westfield', True, '159059725') + ], + lambda d: (d.other_name, d.other_chain, d.is_open, d.book_isbn) + ) + + def test_column_field_ordering(self): + """ + Test that columns are aligned in the correct order for + resolve_columns. This test will fail on mysql if column + ordering is out. Column fields should be aligned as: + 1. extra_select + 2. model_fields + 3. annotation_fields + 4. model_related_fields + """ + store = Store.objects.first() + Employee.objects.create(id=1, first_name='Max', manager=True, last_name='Paine', + store=store, age=23, salary=Decimal(50000.00)) + Employee.objects.create(id=2, first_name='Buffy', manager=False, last_name='Summers', + store=store, age=18, salary=Decimal(40000.00)) + + qs = Employee.objects.extra( + select={'random_value': '42'} + ).select_related('store').annotate( + annotated_value=Value(17, output_field=IntegerField()) + ) + + rows = [ + (1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00), store.name, 17), + (2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00), store.name, 17) + ] + + self.assertQuerysetEqual( + qs.order_by('id'), rows, + lambda e: ( + e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age, + e.salary, e.store.name, e.annotated_value)) + + def test_column_field_ordering_with_deferred(self): + store = Store.objects.first() + Employee.objects.create(id=1, first_name='Max', manager=True, last_name='Paine', + store=store, age=23, salary=Decimal(50000.00)) + Employee.objects.create(id=2, first_name='Buffy', manager=False, last_name='Summers', + store=store, age=18, salary=Decimal(40000.00)) + + qs = Employee.objects.extra( + select={'random_value': '42'} + ).select_related('store').annotate( + annotated_value=Value(17, output_field=IntegerField()) + ) + + rows = [ + (1, 'Max', True, 42, 'Paine', 23, Decimal(50000.00), store.name, 17), + (2, 'Buffy', False, 42, 'Summers', 18, Decimal(40000.00), store.name, 17) + ] + + # and we respect deferred columns! + self.assertQuerysetEqual( + qs.defer('age').order_by('id'), rows, + lambda e: ( + e.id, e.first_name, e.manager, e.random_value, e.last_name, e.age, + e.salary, e.store.name, e.annotated_value)) + + def test_custom_functions(self): + Company(name='Apple', motto=None, ticker_name='APPL', description='Beautiful Devices').save() + Company(name='Django Software Foundation', motto=None, ticker_name=None, description=None).save() + Company(name='Google', motto='Do No Evil', ticker_name='GOOG', description='Internet Company').save() + Company(name='Yahoo', motto=None, ticker_name=None, description='Internet Company').save() + + qs = Company.objects.annotate( + tagline=Func( + F('motto'), + F('ticker_name'), + F('description'), + Value('No Tag'), + function='COALESCE') + ).order_by('name') + + self.assertQuerysetEqual( + qs, [ + ('Apple', 'APPL'), + ('Django Software Foundation', 'No Tag'), + ('Google', 'Do No Evil'), + ('Yahoo', 'Internet Company') + ], + lambda c: (c.name, c.tagline) + ) + + def test_custom_functions_can_ref_other_functions(self): + Company(name='Apple', motto=None, ticker_name='APPL', description='Beautiful Devices').save() + Company(name='Django Software Foundation', motto=None, ticker_name=None, description=None).save() + Company(name='Google', motto='Do No Evil', ticker_name='GOOG', description='Internet Company').save() + Company(name='Yahoo', motto=None, ticker_name=None, description='Internet Company').save() + + class Lower(Func): + function = 'LOWER' + + qs = Company.objects.annotate( + tagline=Func( + F('motto'), + F('ticker_name'), + F('description'), + Value('No Tag'), + function='COALESCE') + ).annotate( + tagline_lower=Lower(F('tagline'), output_field=CharField()) + ).order_by('name') + + # LOWER function supported by: + # oracle, postgres, mysql, sqlite, sqlserver + + self.assertQuerysetEqual( + qs, [ + ('Apple', 'APPL'.lower()), + ('Django Software Foundation', 'No Tag'.lower()), + ('Google', 'Do No Evil'.lower()), + ('Yahoo', 'Internet Company'.lower()) + ], + lambda c: (c.name, c.tagline_lower) + ) diff --git a/tests/expressions/tests.py b/tests/expressions/tests.py index bd9eed9603..2756c8e10d 100644 --- a/tests/expressions/tests.py +++ b/tests/expressions/tests.py @@ -296,6 +296,21 @@ class ExpressionsTests(TestCase): g = deepcopy(f) self.assertEqual(f.name, g.name) + def test_f_reuse(self): + f = F('id') + n = Number.objects.create(integer=-1) + c = Company.objects.create( + name="Example Inc.", num_employees=2300, num_chairs=5, + ceo=Employee.objects.create(firstname="Joe", lastname="Smith") + ) + c_qs = Company.objects.filter(id=f) + self.assertEqual(c_qs.get(), c) + # Reuse the same F-object for another queryset + n_qs = Number.objects.filter(id=f) + self.assertEqual(n_qs.get(), n) + # The original query still works correctly + self.assertEqual(c_qs.get(), c) + class ExpressionsNumericTests(TestCase): @@ -362,12 +377,16 @@ class ExpressionsNumericTests(TestCase): Complex expressions of different connection types are possible. """ n = Number.objects.create(integer=10, float=123.45) - self.assertEqual(Number.objects.filter(pk=n.pk) - .update(float=F('integer') + F('float') * 2), 1) + self.assertEqual(Number.objects.filter(pk=n.pk).update( + float=F('integer') + F('float') * 2), 1) self.assertEqual(Number.objects.get(pk=n.pk).integer, 10) self.assertEqual(Number.objects.get(pk=n.pk).float, Approximate(256.900, places=3)) + def test_incorrect_field_expression(self): + with self.assertRaisesRegexp(FieldError, "Cannot resolve keyword u?'nope' into field.*"): + list(Employee.objects.filter(firstname=F('nope'))) + class ExpressionOperatorTests(TestCase): def setUp(self): |