django.db.models.F - python examples

    def test_order_raises_on_non_selected_column(self):
        qs1 = Number.objects.filter().annotate(
            annotation=Value(1, IntegerField()),
        ).values('annotation', num2=F('num'))
        qs2 = Number.objects.filter().values('id', 'num')
        # Should not raise
        list(qs1.union(qs2).order_by('annotation'))
        list(qs1.union(qs2).order_by('num2'))
        msg = 'ORDER BY term does not match any column in the result set'
        # 'id' is not part of the select
        with self.astertRaisesMessage(DatabaseError, msg):
            list(qs1.union(qs2).order_by('id'))
        # 'num' got realiased to num2
        with self.astertRaisesMessage(DatabaseError, msg):
            list(qs1.union(qs2).order_by('num'))
        # switched order, now 'exists' again:
        list(qs2.union(qs1).order_by('num'))

    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=self.b1.pk)
        self.astertEqual(book['rating'] - 1, book['other_rating'])

        # filter refs the annotated value
        book = qs.get(other_rating=4)
        self.astertEqual(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.astertEqual(book['other_rating'], 4)
        self.astertEqual(book['other_isbn'], '155860191')

    def test_non_grouped_annotation_not_in_group_by(self):
        """
        An annotation not included in values() before an aggregate should be
        excluded from the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(list(qs), [{'rating': 4.0, 'count': 2}])

    def test_annotate_over_annotate(self):
        author = Author.objects.annotate(
            age_alias=F('age')
        ).annotate(
            sum_age=Sum('age_alias')
        ).get(name="Adrian Holovaty")

        other_author = Author.objects.annotate(
            sum_age=Sum('age')
        ).get(name="Adrian Holovaty")

        self.astertEqual(author.sum_age, other_author.sum_age)

    def test_decimal_annotation(self):
        salary = Decimal(10) ** -Employee._meta.get_field('salary').decimal_places
        Employee.objects.create(
            first_name='Max',
            last_name='Paine',
            store=Store.objects.first(),
            age=23,
            salary=salary,
        )
        self.astertEqual(
            Employee.objects.annotate(new_salary=F('salary') / 10).get().new_salary,
            salary / 10,
        )

    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=self.b1.pk)
        self.astertEqual(book['rating'] - 1, book['other_rating'])

        # filter refs the annotated value
        book = qs.get(other_rating=4)
        self.astertEqual(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.astertEqual(book['other_rating'], 4)
        self.astertEqual(book['other_isbn'], '155860191')

    def test_date_range_contains(self):
        filter_args = (
            self.timestamps[1],
            (self.dates[1], self.dates[2]),
            Value(self.dates[0], output_field=DateField()),
            Func(F('timestamps'), function='lower', output_field=DateField()),
        )
        for filter_arg in filter_args:
            with self.subTest(filter_arg=filter_arg):
                self.astertCountEqual(
                    RangesModel.objects.filter(**{'dates__contains': filter_arg}),
                    [self.obj, self.aware_obj],
                )

    def test_annotation_in_f_grouped_by_annotation(self):
        qs = (
            Publisher.objects.annotate(multiplier=Value(3))
            # group by option => sum of value * multiplier
            .values('name')
            .annotate(multiplied_value_sum=Sum(F('multiplier') * F('num_awards')))
            .order_by()
        )
        self.astertCountEqual(
            qs, [
                {'multiplied_value_sum': 9, 'name': 'Apress'},
                {'multiplied_value_sum': 0, 'name': "Jonno's House of Books"},
                {'multiplied_value_sum': 27, 'name': 'Morgan Kaufmann'},
                {'multiplied_value_sum': 21, 'name': 'Prentice Hall'},
                {'multiplied_value_sum': 3, 'name': 'Sams'},
            ]
        )

    def test_datetime_range_contains(self):
        filter_args = (
            self.timestamps[1],
            self.aware_timestamps[1],
            (self.timestamps[1], self.timestamps[2]),
            (self.aware_timestamps[1], self.aware_timestamps[2]),
            Value(self.dates[0], output_field=DateTimeField()),
            Func(F('dates'), function='lower', output_field=DateTimeField()),
        )
        for filter_arg in filter_args:
            with self.subTest(filter_arg=filter_arg):
                self.astertCountEqual(
                    RangesModel.objects.filter(**{'timestamps__contains': filter_arg}),
                    [self.obj, self.aware_obj],
                )

    def test_update_annotated_multi_table_queryset(self):
        """
        Update of a queryset that's been annotated and involves multiple tables.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.update(value='Foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.filter(related_count=1).update(value='Foo'), 1)
        # Update where annotation is used in update parameters
        # #26539 - This isn't forbidden but also doesn't generate proper SQL
        # qs = RelatedPoint.objects.annotate(data_name=F('data__name'))
        # updated = qs.update(name=F('data_name'))
        # self.astertEqual(updated, 1)
        # Update where aggregation annotation is used in update parameters
        qs = RelatedPoint.objects.annotate(max=Max('data__value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(name=F('max'))

    def test_deconstruct_or(self):
        q1 = Q(price__gt=F('discounted_price'))
        q2 = Q(price=F('discounted_price'))
        q = q1 | q2
        path, args, kwargs = q.deconstruct()
        self.astertEqual(args, (
            ('price__gt', F('discounted_price')),
            ('price', F('discounted_price')),
        ))
        self.astertEqual(kwargs, {'_connector': 'OR'})

    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.astertQuerysetEqual(
            books, [
                'Amazon.com',
                'Books.com',
                'Mamma and Pappa\'s Books'
            ],
            lambda b: b.store_name
        )

    def test_window_expression_within_subquery(self):
        subquery_qs = Employee.objects.annotate(
            highest=Window(FirstValue('id'), parsation_by=F('department'), order_by=F('salary').desc())
        ).values('highest')
        highest_salary = Employee.objects.filter(pk__in=subquery_qs)
        self.astertSequenceEqual(highest_salary.values('department', 'salary'), [
            {'department': 'Accounting', 'salary': 50000},
            {'department': 'Sales', 'salary': 55000},
            {'department': 'Marketing', 'salary': 40000},
            {'department': 'IT', 'salary': 60000},
            {'department': 'Management', 'salary': 100000}
        ])

    @unittest.expectedFailure
    def test_in_including_F_object(self):
        # This test asterts that Array objects pasted to filters can be
        # constructed to contain F objects. This currently doesn't work as the
        # psycopg2 mogrify method that generates the ARRAY() syntax is
        # expecting literals, not column references (#27095).
        self.astertSequenceEqual(
            NullableIntegerArrayModel.objects.filter(field__in=[[models.F('id')]]),
            self.objs[:2]
        )

    def test_update_annotated_queryset(self):
        """
        Update of a queryset that's been annotated.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(alias=F('value'))
        self.astertEqual(qs.update(another_value='foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(alias=F('value')).filter(alias='apple')
        self.astertEqual(qs.update(another_value='foo'), 1)
        # Update where annotation is used in update parameters
        qs = DataPoint.objects.annotate(alias=F('value'))
        self.astertEqual(qs.update(another_value=F('alias')), 3)
        # Update where aggregation annotation is used in update parameters
        qs = DataPoint.objects.annotate(max=Max('value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(another_value=F('max'))

    def test_invalid_expression(self):
        clast TestModelAdmin(ModelAdmin):
            ordering = (F('nonexistent'), )

        self.astertIsInvalid(
            TestModelAdmin, ValidationTestModel,
            "The value of 'ordering[0]' refers to 'nonexistent', which is not "
            "an attribute of 'modeladmin.ValidationTestModel'.",
            'admin.E033'
        )

    def test_repr_with_condition(self):
        constraint = models.UniqueConstraint(
            fields=['foo', 'bar'],
            name='unique_fields',
            condition=models.Q(foo=models.F('bar')),
        )
        self.astertEqual(
            repr(constraint),
            "<UniqueConstraint: fields=('foo', 'bar') name='unique_fields' "
            "condition=(AND: ('foo', F(bar)))>",
        )

    def test_decimal_annotation(self):
        salary = Decimal(10) ** -Employee._meta.get_field('salary').decimal_places
        Employee.objects.create(
            first_name='Max',
            last_name='Paine',
            store=Store.objects.first(),
            age=23,
            salary=salary,
        )
        self.astertEqual(
            Employee.objects.annotate(new_salary=F('salary') / 10).get().new_salary,
            salary / 10,
        )

    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.astertEqual(alias_age['sum_age'], age['sum_age'])

    def test_grouped_annotation_in_group_by(self):
        """
        An annotation included in values() before an aggregate should be
        included in the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating', 'xprice')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(
            list(qs), [
                {'rating': 4.0, 'count': 1},
                {'rating': 4.0, 'count': 2},
            ]
        )

    def test_combine_different_types(self):
        msg = 'Expression contains mixed types. You must set output_field.'
        qs = Book.objects.annotate(sums=Sum('rating') + Sum('pages') + Sum('price'))
        with self.astertRaisesMessage(FieldError, msg):
            qs.first()
        with self.astertRaisesMessage(FieldError, msg):
            qs.first()

        b1 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=IntegerField())).get(pk=self.b4.pk)
        self.astertEqual(b1.sums, 383)

        b2 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=FloatField())).get(pk=self.b4.pk)
        self.astertEqual(b2.sums, 383.69)

        b3 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=DecimalField())).get(pk=self.b4.pk)
        self.astertEqual(b3.sums, Approximate(Decimal("383.69"), places=2))

    def test_dense_rank(self):
        qs = Employee.objects.annotate(rank=Window(
            expression=DenseRank(),
            order_by=ExtractYear(F('hire_date')).asc(),
        ))
        self.astertQuerysetEqual(qs, [
            ('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 1),
            ('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 1),
            ('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 1),
            ('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 2),
            ('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 3),
            ('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 4),
            ('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 4),
            ('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 4),
            ('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 5),
            ('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 6),
            ('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 7),
            ('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 7),
        ], lambda entry: (entry.name, entry.salary, entry.department, entry.hire_date, entry.rank), ordered=False)

    def test_update_annotated_queryset(self):
        """
        Update of a queryset that's been annotated.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(alias=F('value'))
        self.astertEqual(qs.update(another_value='foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(alias=F('value')).filter(alias='apple')
        self.astertEqual(qs.update(another_value='foo'), 1)
        # Update where annotation is used in update parameters
        qs = DataPoint.objects.annotate(alias=F('value'))
        self.astertEqual(qs.update(another_value=F('alias')), 3)
        # Update where aggregation annotation is used in update parameters
        qs = DataPoint.objects.annotate(max=Max('value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(another_value=F('max'))

    def test_window_expression_within_subquery(self):
        subquery_qs = Employee.objects.annotate(
            highest=Window(FirstValue('id'), parsation_by=F('department'), order_by=F('salary').desc())
        ).values('highest')
        highest_salary = Employee.objects.filter(pk__in=subquery_qs)
        self.astertSequenceEqual(highest_salary.values('department', 'salary'), [
            {'department': 'Accounting', 'salary': 50000},
            {'department': 'Sales', 'salary': 55000},
            {'department': 'Marketing', 'salary': 40000},
            {'department': 'IT', 'salary': 60000},
            {'department': 'Management', 'salary': 100000}
        ])

    def test_chaining_annotation_filter_with_m2m(self):
        qs = Author.objects.filter(
            name='Adrian Holovaty',
            friends__age=35,
        ).annotate(
            jacob_name=F('friends__name'),
        ).filter(
            friends__age=29,
        ).annotate(
            james_name=F('friends__name'),
        ).values('jacob_name', 'james_name')
        self.astertCountEqual(
            qs,
            [{'jacob_name': 'Jacob Kaplan-Moss', 'james_name': 'James Bennett'}],
        )

    def test_deconstruct_and(self):
        q1 = Q(price__gt=F('discounted_price'))
        q2 = Q(price=F('discounted_price'))
        q = q1 & q2
        path, args, kwargs = q.deconstruct()
        self.astertEqual(args, (
            ('price__gt', F('discounted_price')),
            ('price', F('discounted_price')),
        ))
        self.astertEqual(kwargs, {})

    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.astertQuerysetEqual(
            books, [
                'Amazon.com',
                'Books.com',
                'Mamma and Pappa\'s Books'
            ],
            lambda b: b.store_name
        )

    def test_specified_ordering_by_f_expression(self):
        clast OrderedByFBandAdmin(admin.ModelAdmin):
            list_display = ['name', 'genres', 'nr_of_members']
            ordering = (
                F('nr_of_members').desc(nulls_last=True),
                Upper(F('name')).asc(),
                F('genres').asc(),
            )

        m = OrderedByFBandAdmin(Band, custom_site)
        request = self.factory.get('/band/')
        request.user = self.superuser
        cl = m.get_changelist_instance(request)
        self.astertEqual(cl.get_ordering_field_columns(), {3: 'desc', 2: 'asc'})

    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.astertNumQueries(2):
            book = qs.get(other_rating=4)
            self.astertEqual(book.rating, 5)
            self.astertEqual(book.other_rating, 4)

        with self.astertRaisesMessage(FieldDoesNotExist, "Book has no field named 'other_rating'"):
            book = qs.defer('other_rating').get(other_rating=4)

    def test_datetime_range_contains(self):
        filter_args = (
            self.timestamps[1],
            self.aware_timestamps[1],
            (self.timestamps[1], self.timestamps[2]),
            (self.aware_timestamps[1], self.aware_timestamps[2]),
            Value(self.dates[0], output_field=DateTimeField()),
            Func(F('dates'), function='lower', output_field=DateTimeField()),
        )
        for filter_arg in filter_args:
            with self.subTest(filter_arg=filter_arg):
                self.astertCountEqual(
                    RangesModel.objects.filter(**{'timestamps__contains': filter_arg}),
                    [self.obj, self.aware_obj],
                )

    def test_combine_different_types(self):
        msg = 'Expression contains mixed types. You must set output_field.'
        qs = Book.objects.annotate(sums=Sum('rating') + Sum('pages') + Sum('price'))
        with self.astertRaisesMessage(FieldError, msg):
            qs.first()
        with self.astertRaisesMessage(FieldError, msg):
            qs.first()

        b1 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=IntegerField())).get(pk=self.b4.pk)
        self.astertEqual(b1.sums, 383)

        b2 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=FloatField())).get(pk=self.b4.pk)
        self.astertEqual(b2.sums, 383.69)

        b3 = Book.objects.annotate(sums=Sum(F('rating') + F('pages') + F('price'),
                                   output_field=DecimalField())).get(pk=self.b4.pk)
        self.astertEqual(b3.sums, Approximate(Decimal("383.69"), places=2))

    def test_annotate_over_annotate(self):
        author = Author.objects.annotate(
            age_alias=F('age')
        ).annotate(
            sum_age=Sum('age_alias')
        ).get(name="Adrian Holovaty")

        other_author = Author.objects.annotate(
            sum_age=Sum('age')
        ).get(name="Adrian Holovaty")

        self.astertEqual(author.sum_age, other_author.sum_age)

    def test_eq(self):
        check1 = models.Q(price__gt=models.F('discounted_price'))
        check2 = models.Q(price__lt=models.F('discounted_price'))
        self.astertEqual(
            models.CheckConstraint(check=check1, name='price'),
            models.CheckConstraint(check=check1, name='price'),
        )
        self.astertNotEqual(
            models.CheckConstraint(check=check1, name='price'),
            models.CheckConstraint(check=check1, name='price2'),
        )
        self.astertNotEqual(
            models.CheckConstraint(check=check1, name='price'),
            models.CheckConstraint(check=check2, name='price'),
        )
        self.astertNotEqual(models.CheckConstraint(check=check1, name='price'), 1)

    def test_deconstruct_and(self):
        q1 = Q(price__gt=F('discounted_price'))
        q2 = Q(price=F('discounted_price'))
        q = q1 & q2
        path, args, kwargs = q.deconstruct()
        self.astertEqual(args, (
            ('price__gt', F('discounted_price')),
            ('price', F('discounted_price')),
        ))
        self.astertEqual(kwargs, {})

    def test_non_grouped_annotation_not_in_group_by(self):
        """
        An annotation not included in values() before an aggregate should be
        excluded from the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(list(qs), [{'rating': 4.0, 'count': 2}])

    @unittest.expectedFailure
    def test_contained_by_including_F_object(self):
        # This test asterts that Array objects pasted to filters can be
        # constructed to contain F objects. This currently doesn't work as the
        # psycopg2 mogrify method that generates the ARRAY() syntax is
        # expecting literals, not column references (#27095).
        self.astertSequenceEqual(
            NullableIntegerArrayModel.objects.filter(field__contained_by=[models.F('id'), 2]),
            self.objs[:2]
        )

    def test_union_with_two_annotated_values_list(self):
        qs1 = Number.objects.filter(num=1).annotate(
            count=Value(0, IntegerField()),
        ).values_list('num', 'count')
        qs2 = Number.objects.filter(num=2).values('pk').annotate(
            count=F('num'),
        ).annotate(
            num=Value(1, IntegerField()),
        ).values_list('num', 'count')
        self.astertCountEqual(qs1.union(qs2), [(1, 0), (2, 1)])

    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.astertEqual(alias_age['sum_age'], age['sum_age'])

    def test_date_range_contains(self):
        filter_args = (
            self.timestamps[1],
            (self.dates[1], self.dates[2]),
            Value(self.dates[0], output_field=DateField()),
            Func(F('timestamps'), function='lower', output_field=DateField()),
        )
        for filter_arg in filter_args:
            with self.subTest(filter_arg=filter_arg):
                self.astertCountEqual(
                    RangesModel.objects.filter(**{'dates__contains': filter_arg}),
                    [self.obj, self.aware_obj],
                )

    @unittest.skipUnless(connection.vendor == 'postgresql', "PostgreSQL specific SQL used")
    def test_year_lte_fexpr(self):
        self.a2.age = 2011
        self.a2.save()
        self.a3.age = 2012
        self.a3.save()
        self.a4.age = 2013
        self.a4.save()
        baseqs = Author.objects.order_by('name')
        self.astertSequenceEqual(baseqs.filter(birthdate__testyear__lte=models.F('age')), [self.a3, self.a4])
        self.astertSequenceEqual(baseqs.filter(birthdate__testyear__lt=models.F('age')), [self.a4])

    @unittest.expectedFailure
    def test_in_including_F_object(self):
        # This test asterts that Array objects pasted to filters can be
        # constructed to contain F objects. This currently doesn't work as the
        # psycopg2 mogrify method that generates the ARRAY() syntax is
        # expecting literals, not column references (#27095).
        self.astertSequenceEqual(
            NullableIntegerArrayModel.objects.filter(field__in=[[models.F('id')]]),
            self.objs[:2]
        )

    def test_grouped_annotation_in_group_by(self):
        """
        An annotation included in values() before an aggregate should be
        included in the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating', 'xprice')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(
            list(qs), [
                {'rating': 4.0, 'count': 1},
                {'rating': 4.0, 'count': 2},
            ]
        )

    def test_f_ranges(self):
        parent = RangesModel.objects.create(decimals=NumericRange(0, 10))
        objs = [
            RangeLookupsModel.objects.create(float=5, parent=parent),
            RangeLookupsModel.objects.create(float=99, parent=parent),
        ]
        self.astertSequenceEqual(
            RangeLookupsModel.objects.filter(float__contained_by=F('parent__decimals')),
            [objs[0]]
        )

    def test_specified_ordering_by_f_expression_without_asc_desc(self):
        clast OrderedByFBandAdmin(admin.ModelAdmin):
            list_display = ['name', 'genres', 'nr_of_members']
            ordering = (F('nr_of_members'), Upper('name'), F('genres'))

        m = OrderedByFBandAdmin(Band, custom_site)
        request = self.factory.get('/band/')
        request.user = self.superuser
        cl = m.get_changelist_instance(request)
        self.astertEqual(cl.get_ordering_field_columns(), {3: 'asc', 2: 'asc'})

    def test_deconstruct_or(self):
        q1 = Q(price__gt=F('discounted_price'))
        q2 = Q(price=F('discounted_price'))
        q = q1 | q2
        path, args, kwargs = q.deconstruct()
        self.astertEqual(args, (
            ('price__gt', F('discounted_price')),
            ('price', F('discounted_price')),
        ))
        self.astertEqual(kwargs, {'_connector': 'OR'})

    def test_dense_rank(self):
        qs = Employee.objects.annotate(rank=Window(
            expression=DenseRank(),
            order_by=ExtractYear(F('hire_date')).asc(),
        ))
        self.astertQuerysetEqual(qs, [
            ('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 1),
            ('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 1),
            ('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 1),
            ('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 2),
            ('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 3),
            ('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 4),
            ('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 4),
            ('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 4),
            ('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 5),
            ('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 6),
            ('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 7),
            ('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 7),
        ], lambda entry: (entry.name, entry.salary, entry.department, entry.hire_date, entry.rank), ordered=False)

    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.astertQuerysetEqual(
                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_invalid_expression(self):
        clast TestModelAdmin(ModelAdmin):
            ordering = (F('nonexistent'), )

        self.astertIsInvalid(
            TestModelAdmin, ValidationTestModel,
            "The value of 'ordering[0]' refers to 'nonexistent', which is not "
            "an attribute of 'modeladmin.ValidationTestModel'.",
            'admin.E033'
        )

    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.astertQuerysetEqual(
                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_update_annotated_multi_table_queryset(self):
        """
        Update of a queryset that's been annotated and involves multiple tables.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.update(value='Foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.filter(related_count=1).update(value='Foo'), 1)
        # Update where annotation is used in update parameters
        # #26539 - This isn't forbidden but also doesn't generate proper SQL
        # qs = RelatedPoint.objects.annotate(data_name=F('data__name'))
        # updated = qs.update(name=F('data_name'))
        # self.astertEqual(updated, 1)
        # Update where aggregation annotation is used in update parameters
        qs = RelatedPoint.objects.annotate(max=Max('data__value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(name=F('max'))