I have this query in django

qs_match_annotation = "some sub query"
qs = qs.annotate(match=qs_match_annotation)
qs = qs.filter(match__gt=0)
qs = qs.order_by("-match")

the sql generated for the qs_match_annotation is written again in the order_by instead of just generating

ORDER BY match DESC

it writes the whole subquery again

how to fix such thing?

I have this query in django

qs_match_annotation = "some sub query"
qs = qs.annotate(match=qs_match_annotation)
qs = qs.filter(match__gt=0)
qs = qs.order_by("-match")

the sql generated for the qs_match_annotation is written again in the order_by instead of just generating

ORDER BY match DESC

it writes the whole subquery again

how to fix such thing?

• sql
• django

• Do you need it in the SELECT. – willeM_ Van Onsem Commented Nov 16, 2024 at 8:16
• @willeM_ Van Onsem now following – mohamed naser Commented Nov 16, 2024 at 8:21

2 Answers 2

how to fix such thing?

You don't. It is one of the inefficiencies of the Django ORM that will (probably) eventually get fixed. As far as I know it is also not standard SQL that you can refer to a column in the SELECT query: some dialects (might) not allow this, and yes, I found that stupid as well.

But it typically has only a very small impact on performance, because typically most effort is wasted in fetching the data.

If you don't need match in the SELECT query, you can however use an .alias(…) ^[Django-doc]:

qs = qs.alias(match=qs_match_annotation).filter(match__gt=0).order_by('-match')

Then at least it is not duplicated in the SELECT clause.

So, apparently this is a sql problem not django

you can't have an alias in the were statement.

probably the alias won't be evaluated in the where part of the query execution.

So, the db can't use a column it doesn't have and as a result they forbid it

does it mean the alias sql will be evaluated more than once? No, depending on the database optimization engine but most probably no, the only insufficient here is writing the same sql more than once but has no effect on the performance

also, if you don't care about the match value returned

use qs.alias() to prevent it from being written there as well