Transforms in order_by() not allowed after alias()

[Jacob Walls]

This example fails with:

django.core.exceptions.FieldError: Cannot resolve keyword 'other_pubdate' into field. Choices are: ... other_pubdate, ...

tests/annotations/tests.py

@@ -1481 +1481 @@
         self.assertIs(hasattr(qs.first(), "other_age"), False)
         self.assertQuerySetEqual(qs, [34, 34, 35, 46, 57], lambda a: a.age)
 
+    def test_order_by_alias_transform(self):
+        qs = (
+            Book.objects.alias(other_pubdate=F("pubdate"))
+            .order_by("-other_pubdate__year")
+        )
+        self.assertQuerySetEqual(qs, [2008, 2007, 1995, 1991], lambda a: a.pubdate.year)
+
     def test_order_by_alias_aggregate(self):
         qs = (
             Author.objects.values("age")

However, all of these variants work, suggesting to me that there should be a simple fix.

Keep the transform, change alias to annotate:

Book.objects.annotate(other_pubdate=F("pubdate")).order_by("other_pubdate__year")

Keep the transform, use a field name:

Book.objects.order_by("pubdate__year")

Keep the alias, drop the transform:

Book.objects.alias(other_pubdate=F("pubdate")).order_by("other_pubdate")

If not practical for some reason, then at the very least we should improve "Cannot resolve keyword 'other_pubdate' into field. Choices are: ... other_pubdate, ...".

[Pravin]

The simple fix i could see around ​https://github.com/django/django/blob/c6be0bf3bb744d234947cefd6def9f31d9655800/django/db/models/sql/query.py#L2342
for example,

diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index adf0663beb..91d3436cf0 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -2339,7 +2339,7 @@ class Query(BaseExpression):
                 if item == "?":
                     continue
                 item = item.removeprefix("-")
-                if item in self.annotations:
+                if item in self.annotations or self.annotations.get(item.split(LOOKUP_SEP)[0]):
                     continue
                 if self.extra and item in self.extra:
                     continue

But this may introduce edge around bad aliases , it will failed if there is __ in the alias, as we already discussed similar in Multi-column subquery work

[Jacob Walls]

​PR

[Sarah Boyce]

Thank you! The bad error is a regression from dce1b9c2de00a3385c029c02dca325f44e7697a4 (refs #36480)

[Jacob Walls]

Good point about the recent change. My PR adds support for this use -- do you think we should backport that, or just backport something that improves the error message and defers the feature?

[Sarah Boyce]

I think this will fall into the "mergers' discretion" category and both choices are fine for me. As you are 6.1's release manager, happy to do whatever option you prefer

[Sarah Boyce]

Demoting from release blocker as this never worked "well" prior to dce1b9c2de00a3385c029c02dca325f44e7697a4 and updating the error message to be clearer for 6.1 doesn't feel needed

[Jacob Walls <jacobtylerwalls@…>]

In be6cf832:

Fixed #37274 -- Allowed transforms in order_by() after alias().