#34533 assigned Bug OuterRef not resolved as part of ORDER BY clause

Django Internals ORM
1

Hello Django Community,

I am working on an issue where using order_by(OuterRef('pk')) in a Subquery causes errors due to outer query constraints.

What I Have Observed:

  1. Directly applying order_by(OuterRef('pk')) in a Subquery causes the query to fail.
  2. To handle this, I moved the Subquery and annotation logic outside the main query, which avoids the errors but does not strictly align with the test case requirements.

What I Tried:

  • Removed or adjusted order_by(OuterRef('pk')) to make the query work without errors.
  • Explored Subquery and OuterRef logic but couldn’t figure out how to resolve the constraints properly.

Where I Need Help:

  1. How can I handle order_by(OuterRef('pk')) correctly in a Subquery?
  2. Is there any adjustment I need to make in how queries are being compiled or referenced?

If anyone has faced a similar issue or can point me in the right direction, I would really appreciate your guidance.

Thank you!

2

Hi @AyushKhatri-Dev - thank you for working on a ticket!

First question, have you written a test?

For some general ORM advise to give an intro, perhaps watch this video?

Then maybe try to find closed tickets that that solved something similar :thinking:

I do a lot of trial and error with the existing test suite and new test to see what I can get working :grin:

1 Like
3

In addition to what Sarah said, there’s a detailed hint on the ticket, see:

I suspect this ticket will be hard to solve without tackling the large problem of compile time resolving of order_by .

In other words, this is probably one of the most challenging tickets currently open against the ORM. Everything is possible, but there might be wisdom in starting with other ORM tickets to build your chops :muscle:

1 Like
4

Strong :+1: to what Jacob said, it is a non trivial ticket to work on as it would likely require that not only have logic for resolving expressions but also for unresolving / unreferencing them.

To complement the Trac answer, let’s compare how filter works compared to order_by.

When you chain filter calls you can only augment the set of table and column references. In other words there is no unfilter method to ask that previous filter calls are no longer relevant. Since calls are only additive it means that there’s never a need to remove references to tables that were JOIN in filter calls that are no longer relevant. There a few other methods that work in this additive manner such as annotate.

Methods like order_by, only, defer on the other hand are altering (for the lack of a better word) where they allow the entirety of the clause to be overridden at a later time. This means that order_by("foo").order_by("bar") is equivalent to order_by("bar") and because the ORM doesn’t have a low level way (in mean sql.Query low level here) to say prune that reference to “foo” as it’s no longer relevant the resolving of order_by clause happens just before the compilation phase instead of at method call time (it’s one of the few exception to the rule discussed in the video above).

Now, how does that relate to the OuterRef case you might ask? Well it just happens that OuterRef resolving happens to depend on the common immediate way of resolving expression and kind of break if done at compilation time like order_by is handled and thus it requires that we either add very specialized code to order_by to handle this case (not that it already does some special things already) or that we address the bigger issue of the lack of reference pruning at the sql.Query level.

I expect the first approach to require a medium level of understanding of the ORM internals and the second is quite the feat.

3 Likes