Fixed #24060 -- Added OrderBy Expressions

2 files changed, 131 insertions, 0 deletions

diff --git a/tests/db_functions/tests.py b/tests/db_functions/tests.py
index 2ab18b61e0..00a5e258de 100644
--- a/tests/db_functions/tests.py
+++ b/tests/db_functions/tests.py
@@ -68,6 +68,37 @@ class FunctionTests(TestCase):
             lambda a: a.headline
         )
 
+    def test_coalesce_ordering(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+
+        authors = Author.objects.order_by(Coalesce('alias', 'name'))
+        self.assertQuerysetEqual(
+            authors, [
+                'Rhonda',
+                'John Smith',
+            ],
+            lambda a: a.name
+        )
+
+        authors = Author.objects.order_by(Coalesce('alias', 'name').asc())
+        self.assertQuerysetEqual(
+            authors, [
+                'Rhonda',
+                'John Smith',
+            ],
+            lambda a: a.name
+        )
+
+        authors = Author.objects.order_by(Coalesce('alias', 'name').desc())
+        self.assertQuerysetEqual(
+            authors, [
+                'John Smith',
+                'Rhonda',
+            ],
+            lambda a: a.name
+        )
+
     def test_concat(self):
         Author.objects.create(name='Jayden')
         Author.objects.create(name='John Smith', alias='smithj', goes_by='John')
@@ -184,6 +215,22 @@ class FunctionTests(TestCase):
 
         self.assertEqual(authors.filter(alias_length__lte=Length('name')).count(), 1)
 
+    def test_length_ordering(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='John Smith', alias='smithj1')
+        Author.objects.create(name='Rhonda', alias='ronny')
+
+        authors = Author.objects.order_by(Length('name'), Length('alias'))
+
+        self.assertQuerysetEqual(
+            authors, [
+                ('Rhonda', 'ronny'),
+                ('John Smith', 'smithj'),
+                ('John Smith', 'smithj1'),
+            ],
+            lambda a: (a.name, a.alias)
+        )
+
     def test_substr(self):
         Author.objects.create(name='John Smith', alias='smithj')
         Author.objects.create(name='Rhonda')
@@ -230,3 +277,25 @@ class FunctionTests(TestCase):
 
         with six.assertRaisesRegex(self, ValueError, "'pos' must be greater than 0"):
             Author.objects.annotate(raises=Substr('name', 0))
+
+    def test_nested_function_ordering(self):
+        Author.objects.create(name='John Smith')
+        Author.objects.create(name='Rhonda Simpson', alias='ronny')
+
+        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')))
+        self.assertQuerysetEqual(
+            authors, [
+                'Rhonda Simpson',
+                'John Smith',
+            ],
+            lambda a: a.name
+        )
+
+        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')).desc())
+        self.assertQuerysetEqual(
+            authors, [
+                'John Smith',
+                'Rhonda Simpson',
+            ],
+            lambda a: a.name
+        )
diff --git a/tests/ordering/tests.py b/tests/ordering/tests.py
index 5d323e006b..abb30f43f1 100644
--- a/tests/ordering/tests.py
+++ b/tests/ordering/tests.py
@@ -3,6 +3,7 @@ from __future__ import unicode_literals
 from datetime import datetime
 from operator import attrgetter
 
+from django.db.models import F
 from django.test import TestCase
 
 from .models import Article, Author
@@ -203,3 +204,64 @@ class OrderingTests(TestCase):
             ],
             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):
+        """
+        A column may only be included once (the first occurrence) so we check
+        to ensure there are no duplicates by inspecting the SQL.
+        """
+        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")
+        )