by default article objects all orders by pubdate descending then headline ascending getting a single item should work too override ordering with orderby which is in the same format as the ordering attribute in models attempts to override default ordering on related models with an unknown field should result in an error only the last orderby has any effect since they each override any previous ordering asc and desc are chainable with nullslast asc and desc are chainable with nullsfirst use the stop part of slicing notation to limit the results use the stop and start parts of slicing notation to offset the result list use to order randomly ordering can be reversed using the reverse method on a queryset this allows you to extract things like the last two items reverse and then take the first two ordering can be based on fields included from an extra clause if the extra clause uses an sql keyword for a name it will be protected by quoting pk works as an ordering option in meta ordering by a foreign key by its attribute name prevents the query from inheriting its related model ordering option 19195 a column may only be included once the first occurrence so we check to ensure there are no duplicates by inspecting the sql order by annotated constant from selected columns order by annotated constant which is out of selected columns order by constant an ordering referencing a model with an ordering referencing a model multiple time no circular reference should be detected 24654 f expressions can be used in meta ordering articles orderedbyfarticle objects all articles filterheadlinearticle 2 updateself 2 articles filterheadlinearticle 3 updateself 1 self assertquerysetequal articles article 1 article 4 article 3 article 2 attrgetterheadline def testorderbyptrfieldwithdefaultorderingbyexpressionself ca1 childarticle objects create headlineh2 pubdatedatetime2005 7 27 self 2 ca2 childarticle objects create headlineh2 pubdatedatetime2005 7 27 self 1 ca3 childarticle objects create headlineh3 pubdatedatetime2005 7 27 self 1 ca4 childarticle objects createheadlineh1 pubdatedatetime2005 7 28 articles childarticle objects orderbyarticleptr self assertsequenceequalarticles ca4 ca2 ca1 ca3 def testdefaultorderingdoesnotaffectgroupbyself article objects excludeheadlinearticle 4 updateself 1 article objects filterheadlinearticle 4 updateself 2 articles article objects values annotatecountcount self assertcountequal articles self 1 pk count 3 self 2 pk count 1 def testorderbyparentfkwithexpressionindefaultorderingself p3 orderedbyexpression objects createnameobj 3 p2 orderedbyexpression objects createnameobj 2 p1 orderedbyexpression objects createnameobj 1 c3 orderedbyexpressionchild objects createparentp3 c2 orderedbyexpressionchild objects createparentp2 c1 orderedbyexpressionchild objects createparentp1 self assertsequenceequal orderedbyexpressionchild objects orderbyparent c1 c2 c3 def testorderbygrandparentfkwithexpressionindefaultorderingself p3 orderedbyexpression objects createnameobj 3 p2 orderedbyexpression objects createnameobj 2 p1 orderedbyexpression objects createnameobj 1 c3 orderedbyexpressionchild objects createparentp3 c2 orderedbyexpressionchild objects createparentp2 c1 orderedbyexpressionchild objects createparentp1 g3 orderedbyexpressiongrandchild objects createparentc3 g2 orderedbyexpressiongrandchild objects createparentc2 g1 orderedbyexpressiongrandchild objects createparentc1 self assertsequenceequal orderedbyexpressiongrandchild objects orderbyparent g1 g2 g3 def testorderbyexpressionrefself self assertquerysetequal objects annotateuppernameuppername orderby lengthuppername objects orderbylengthuppername def testorderingselectrelatedcollisionself self assertequal article objects selectrelated annotatenameuppername filterpkself a1 pk orderbyorderbyfname first self a1 self assertequal article objects selectrelated annotatenameuppername filterpkself a1 pk orderbyname first self a1 def testorderbyexprqueryreuseself qs objects annotatenumcountarticle orderby fnum desc pk self assertcountequalqs qs iterator by default article objects all orders by pub_date descending then headline ascending getting a single item should work too override ordering with order_by which is in the same format as the ordering attribute in models attempts to override default ordering on related models with an unknown field should result in an error only the last order_by has any effect since they each override any previous ordering asc and desc are chainable with nulls_last asc and desc are chainable with nulls_first use the stop part of slicing notation to limit the results use the stop and start parts of slicing notation to offset the result list use to order randomly ordering can be reversed using the reverse method on a queryset this allows you to extract things like the last two items reverse and then take the first two ordering can be based on fields included from an extra clause if the extra clause uses an sql keyword for a name it will be protected by quoting pk works as an ordering option in meta ordering by a foreign key by its attribute name prevents the query from inheriting its related model ordering option 19195 a column may only be included once the first occurrence so we check to ensure there are no duplicates by inspecting the sql order by annotated constant from selected columns order by annotated constant which is out of selected columns order by constant an ordering referencing a model with an ordering referencing a model multiple time no circular reference should be detected 24654 f expressions can be used in meta ordering
from datetime import datetime
from operator import attrgetter

from django.core.exceptions import FieldError
from django.db.models import (
    CharField,
    Count,
    DateTimeField,
    F,
    Max,
    OrderBy,
    OuterRef,
    Subquery,
    Value,
)
from django.db.models.functions import Length, Upper
from django.test import TestCase

from .models import (
    Article,
    Author,
    ChildArticle,
    OrderedByExpression,
    OrderedByExpressionChild,
    OrderedByExpressionGrandChild,
    OrderedByFArticle,
    Reference,
)


class OrderingTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        cls.a1 = Article.objects.create(
            headline="Article 1", pub_date=datetime(2005, 7, 26)
        )
        cls.a2 = Article.objects.create(
            headline="Article 2", pub_date=datetime(2005, 7, 27)
        )
        cls.a3 = Article.objects.create(
            headline="Article 3", pub_date=datetime(2005, 7, 27)
        )
        cls.a4 = Article.objects.create(
            headline="Article 4", pub_date=datetime(2005, 7, 28)
        )
        cls.author_1 = Author.objects.create(name="Name 1")
        cls.author_2 = Author.objects.create(name="Name 2")
        for i in range(2):
            Author.objects.create()

    def test_default_ordering(self):
        
        self.assertQuerySetEqual(
            Article.objects.all(),
            [
                "Article 4",
                "Article 2",
                "Article 3",
                "Article 1",
            ],
            attrgetter("headline"),
        )

        
        self.assertEqual(Article.objects.all()[0], self.a4)

    def test_default_ordering_override(self):
        
        self.assertQuerySetEqual(
            Article.objects.order_by("headline"),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.order_by("pub_date", "-headline"),
            [
                "Article 1",
                "Article 3",
                "Article 2",
                "Article 4",
            ],
            attrgetter("headline"),
        )

    def test_default_ordering_override_unknown_field(self):
        
        msg = (
            "Cannot resolve keyword 'unknown_field' into field. Choices are: "
            "article, author, editor, editor_id, id, name"
        )
        with self.assertRaisesMessage(FieldError, msg):
            list(Article.objects.order_by("author__unknown_field"))

    def test_order_by_override(self):
        
        self.assertQuerySetEqual(
            Article.objects.order_by("id"),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.order_by("id").order_by("-headline"),
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )

    def test_order_by_nulls_first_and_last(self):
        msg = "nulls_first and nulls_last are mutually exclusive"
        with self.assertRaisesMessage(ValueError, msg):
            Article.objects.order_by(
                F("author").desc(nulls_last=True, nulls_first=True)
            )

    def assertQuerySetEqualReversible(self, queryset, sequence):
        self.assertSequenceEqual(queryset, sequence)
        self.assertSequenceEqual(queryset.reverse(), list(reversed(sequence)))

    def test_order_by_nulls_last(self):
        Article.objects.filter(headline="Article 3").update(author=self.author_1)
        Article.objects.filter(headline="Article 4").update(author=self.author_2)
        
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(F("author").desc(nulls_last=True), "headline"),
            [self.a4, self.a3, self.a1, self.a2],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(F("author").asc(nulls_last=True), "headline"),
            [self.a3, self.a4, self.a1, self.a2],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(
                Upper("author__name").desc(nulls_last=True), "headline"
            ),
            [self.a4, self.a3, self.a1, self.a2],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(
                Upper("author__name").asc(nulls_last=True), "headline"
            ),
            [self.a3, self.a4, self.a1, self.a2],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.annotate(upper_name=Upper("author__name")).order_by(
                F("upper_name").asc(nulls_last=True), "headline"
            ),
            [self.a3, self.a4, self.a1, self.a2],
        )

    def test_order_by_nulls_first(self):
        Article.objects.filter(headline="Article 3").update(author=self.author_1)
        Article.objects.filter(headline="Article 4").update(author=self.author_2)
        
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(F("author").asc(nulls_first=True), "headline"),
            [self.a1, self.a2, self.a3, self.a4],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(F("author").desc(nulls_first=True), "headline"),
            [self.a1, self.a2, self.a4, self.a3],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(
                Upper("author__name").asc(nulls_first=True), "headline"
            ),
            [self.a1, self.a2, self.a3, self.a4],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.order_by(
                Upper("author__name").desc(nulls_first=True), "headline"
            ),
            [self.a1, self.a2, self.a4, self.a3],
        )
        self.assertQuerySetEqualReversible(
            Article.objects.annotate(upper_name=Upper("author__name")).order_by(
                F("upper_name").desc(nulls_first=True), "headline"
            ),
            [self.a1, self.a2, self.a4, self.a3],
        )

    def test_orders_nulls_first_on_filtered_subquery(self):
        Article.objects.filter(headline="Article 1").update(author=self.author_1)
        Article.objects.filter(headline="Article 2").update(author=self.author_1)
        Article.objects.filter(headline="Article 4").update(author=self.author_2)
        Author.objects.filter(name__isnull=True).delete()
        author_3 = Author.objects.create(name="Name 3")
        article_subquery = (
            Article.objects.filter(
                author=OuterRef("pk"),
                headline__icontains="Article",
            )
            .order_by()
            .values("author")
            .annotate(
                last_date=Max("pub_date"),
            )
            .values("last_date")
        )
        self.assertQuerySetEqualReversible(
            Author.objects.annotate(
                last_date=Subquery(article_subquery, output_field=DateTimeField())
            )
            .order_by(F("last_date").asc(nulls_first=True))
            .distinct(),
            [author_3, self.author_1, self.author_2],
        )

    def test_stop_slicing(self):
        
        self.assertQuerySetEqual(
            Article.objects.order_by("headline")[:2],
            [
                "Article 1",
                "Article 2",
            ],
            attrgetter("headline"),
        )

    def test_stop_start_slicing(self):
        
        self.assertQuerySetEqual(
            Article.objects.order_by("headline")[1:3],
            [
                "Article 2",
                "Article 3",
            ],
            attrgetter("headline"),
        )

    def test_random_ordering(self):
        
        self.assertEqual(len(list(Article.objects.order_by("?"))), 4)

    def test_reversed_ordering(self):
        
        self.assertQuerySetEqual(
            Article.objects.reverse()[:2],
            [
                "Article 1",
                "Article 3",
            ],
            attrgetter("headline"),
        )

    def test_reverse_ordering_pure(self):
        qs1 = Article.objects.order_by(F("headline").asc())
        qs2 = qs1.reverse()
        self.assertQuerySetEqual(
            qs2,
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            qs1,
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )

    def test_reverse_meta_ordering_pure(self):
        Article.objects.create(
            headline="Article 5",
            pub_date=datetime(2005, 7, 30),
            author=self.author_1,
            second_author=self.author_2,
        )
        Article.objects.create(
            headline="Article 5",
            pub_date=datetime(2005, 7, 30),
            author=self.author_2,
            second_author=self.author_1,
        )
        self.assertQuerySetEqual(
            Article.objects.filter(headline="Article 5").reverse(),
            ["Name 2", "Name 1"],
            attrgetter("author.name"),
        )
        self.assertQuerySetEqual(
            Article.objects.filter(headline="Article 5"),
            ["Name 1", "Name 2"],
            attrgetter("author.name"),
        )

    def test_no_reordering_after_slicing(self):
        msg = "Cannot reverse a query once a slice has been taken."
        qs = Article.objects.all()[0:2]
        with self.assertRaisesMessage(TypeError, msg):
            qs.reverse()
        with self.assertRaisesMessage(TypeError, msg):
            qs.last()

    def test_extra_ordering(self):
        
        self.assertQuerySetEqual(
            Article.objects.extra(
                select={"foo": "pub_date"}, order_by=["foo", "headline"]
            ),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )

    def test_extra_ordering_quoting(self):
        
        self.assertQuerySetEqual(
            Article.objects.extra(
                select={"order": "pub_date"}, order_by=["order", "headline"]
            ),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )

    def test_extra_ordering_with_table_name(self):
        self.assertQuerySetEqual(
            Article.objects.extra(order_by=["ordering_article.headline"]),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.extra(order_by=["-ordering_article.headline"]),
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )

    def test_order_by_pk(self):
        
        self.assertEqual(
            [a.pk for a in Author.objects.all()],
            [a.pk for a in Author.objects.order_by("-pk")],
        )

    def test_order_by_fk_attname(self):
        
        authors = list(Author.objects.order_by("id"))
        for i in range(1, 5):
            author = authors[i - 1]
            article = getattr(self, "a%d" % (5 - i))
            article.author = author
            article.save(update_fields={"author"})

        self.assertQuerySetEqual(
            Article.objects.order_by("author_id"),
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )

    def test_order_by_self_referential_fk(self):
        self.a1.author = Author.objects.create(editor=self.author_1)
        self.a1.save()
        self.a2.author = Author.objects.create(editor=self.author_2)
        self.a2.save()
        self.assertQuerySetEqual(
            Article.objects.filter(author__isnull=False).order_by("author__editor"),
            ["Article 2", "Article 1"],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.filter(author__isnull=False).order_by("author__editor_id"),
            ["Article 1", "Article 2"],
            attrgetter("headline"),
        )

    def test_order_by_f_expression(self):
        self.assertQuerySetEqual(
            Article.objects.order_by(F("headline")),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.order_by(F("headline").asc()),
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        self.assertQuerySetEqual(
            Article.objects.order_by(F("headline").desc()),
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )

    def test_order_by_f_expression_duplicates(self):
        
        qs = Article.objects.order_by(F("headline").asc(), F("headline").desc())
        sql = str(qs.query).upper()
        fragment = sql[sql.find("ORDER BY") :]
        self.assertEqual(fragment.count("HEADLINE"), 1)
        self.assertQuerySetEqual(
            qs,
            [
                "Article 1",
                "Article 2",
                "Article 3",
                "Article 4",
            ],
            attrgetter("headline"),
        )
        qs = Article.objects.order_by(F("headline").desc(), F("headline").asc())
        sql = str(qs.query).upper()
        fragment = sql[sql.find("ORDER BY") :]
        self.assertEqual(fragment.count("HEADLINE"), 1)
        self.assertQuerySetEqual(
            qs,
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
            attrgetter("headline"),
        )

    def test_order_by_constant_value(self):
        
        qs = Article.objects.annotate(
            constant=Value("1", output_field=CharField()),
        ).order_by("constant", "-headline")
        self.assertSequenceEqual(qs, [self.a4, self.a3, self.a2, self.a1])
        
        self.assertSequenceEqual(
            qs.values_list("headline", flat=True),
            [
                "Article 4",
                "Article 3",
                "Article 2",
                "Article 1",
            ],
        )
        
        qs = Article.objects.order_by(Value("1", output_field=CharField()), "-headline")
        self.assertSequenceEqual(qs, [self.a4, self.a3, self.a2, self.a1])

    def test_related_ordering_duplicate_table_reference(self):
        
        first_author = Author.objects.create()
        second_author = Author.objects.create()
        self.a1.author = first_author
        self.a1.second_author = second_author
        self.a1.save()
        self.a2.author = second_author
        self.a2.second_author = first_author
        self.a2.save()
        r1 = Reference.objects.create(article_id=self.a1.pk)
        r2 = Reference.objects.create(article_id=self.a2.pk)
        self.assertSequenceEqual(Reference.objects.all(), [r2, r1])

    def test_default_ordering_by_f_expression(self):
        
        articles = OrderedByFArticle.objects.all()
        articles.filter(headline="Article 2").update(author=self.author_2)
        articles.filter(headline="Article 3").update(author=self.author_1)
        self.assertQuerySetEqual(
            articles,
            ["Article 1", "Article 4", "Article 3", "Article 2"],
            attrgetter("headline"),
        )

    def test_order_by_ptr_field_with_default_ordering_by_expression(self):
        ca1 = ChildArticle.objects.create(
            headline="h2",
            pub_date=datetime(2005, 7, 27),
            author=self.author_2,
        )
        ca2 = ChildArticle.objects.create(
            headline="h2",
            pub_date=datetime(2005, 7, 27),
            author=self.author_1,
        )
        ca3 = ChildArticle.objects.create(
            headline="h3",
            pub_date=datetime(2005, 7, 27),
            author=self.author_1,
        )
        ca4 = ChildArticle.objects.create(headline="h1", pub_date=datetime(2005, 7, 28))
        articles = ChildArticle.objects.order_by("article_ptr")
        self.assertSequenceEqual(articles, [ca4, ca2, ca1, ca3])

    def test_default_ordering_does_not_affect_group_by(self):
        Article.objects.exclude(headline="Article 4").update(author=self.author_1)
        Article.objects.filter(headline="Article 4").update(author=self.author_2)
        articles = Article.objects.values("author").annotate(count=Count("author"))
        self.assertCountEqual(
            articles,
            [
                {"author": self.author_1.pk, "count": 3},
                {"author": self.author_2.pk, "count": 1},
            ],
        )

    def test_order_by_parent_fk_with_expression_in_default_ordering(self):
        p3 = OrderedByExpression.objects.create(name="oBJ 3")
        p2 = OrderedByExpression.objects.create(name="OBJ 2")
        p1 = OrderedByExpression.objects.create(name="obj 1")
        c3 = OrderedByExpressionChild.objects.create(parent=p3)
        c2 = OrderedByExpressionChild.objects.create(parent=p2)
        c1 = OrderedByExpressionChild.objects.create(parent=p1)
        self.assertSequenceEqual(
            OrderedByExpressionChild.objects.order_by("parent"),
            [c1, c2, c3],
        )

    def test_order_by_grandparent_fk_with_expression_in_default_ordering(self):
        p3 = OrderedByExpression.objects.create(name="oBJ 3")
        p2 = OrderedByExpression.objects.create(name="OBJ 2")
        p1 = OrderedByExpression.objects.create(name="obj 1")
        c3 = OrderedByExpressionChild.objects.create(parent=p3)
        c2 = OrderedByExpressionChild.objects.create(parent=p2)
        c1 = OrderedByExpressionChild.objects.create(parent=p1)
        g3 = OrderedByExpressionGrandChild.objects.create(parent=c3)
        g2 = OrderedByExpressionGrandChild.objects.create(parent=c2)
        g1 = OrderedByExpressionGrandChild.objects.create(parent=c1)
        self.assertSequenceEqual(
            OrderedByExpressionGrandChild.objects.order_by("parent"),
            [g1, g2, g3],
        )

    def test_order_by_expression_ref(self):
        self.assertQuerySetEqual(
            Author.objects.annotate(upper_name=Upper("name")).order_by(
                Length("upper_name")
            ),
            Author.objects.order_by(Length(Upper("name"))),
        )

    def test_ordering_select_related_collision(self):
        self.assertEqual(
            Article.objects.select_related("author")
            .annotate(name=Upper("author__name"))
            .filter(pk=self.a1.pk)
            .order_by(OrderBy(F("name")))
            .first(),
            self.a1,
        )
        self.assertEqual(
            Article.objects.select_related("author")
            .annotate(name=Upper("author__name"))
            .filter(pk=self.a1.pk)
            .order_by("name")
            .first(),
            self.a1,
        )

    def test_order_by_expr_query_reuse(self):
        qs = Author.objects.annotate(num=Count("article")).order_by(
            F("num").desc(), "pk"
        )
        self.assertCountEqual(qs, qs.iterator())