queryset, union, order_by over an annotated field

user_2073897 0
Viewed 1229

It seems that Django is kind of an overthinker when it comes to order by a field.

I need to make a union of two queries (queryset), the first one is ranked, the second one is not, in the final result I want a single queryset because it is going to be paginated.

I'll give you an example using the User model so you can try this at home.

from django.contrib.auth.models import User
from django.db.models import F, Value, IntegerField
from django.db.models.expressions import RawSQL

queryset = User.objects

a = queryset.filter(email__contains='a').annotate(rank=RawSQL("rank() OVER (ORDER BY id desc)", [], output_field=IntegerField()))
b = queryset.filter(email__contains='b').annotate(rank=Value(None, output_field=IntegerField()))

a.union(b).order_by(F('rank').desc(nulls_last=True))
# DatabaseError: ORDER BY term does not match any column in the result set.

a.order_by(F('rank').desc(nulls_last=True))
# this is OK

b.order_by(F('rank').desc(nulls_last=True))
# ProgrammingError: non-integer constant in ORDER BY
# LINE 1: ...ERE "auth_user"."email"::text LIKE '%b%' ORDER BY NULL DESC ...

Is this a Django bug?

I'm using Django==1.11.17

django-models django python
1 Answers

I believe the issue arises because Django handles the Value(None, output_field=IntegerField()) by just injecting NULL, which is not valid in an ORDER BY clause.

Then, because Django puts the expression that was annotated on in as the ORDER BY, it uses this value.

I think the only way to overcome this would be to have the entire query (ie, both parts of the union) evaluate inside a subquery - which would then have columns for the values that were annotated on, rather than just the expressions themselves.

Perhaps you could argue this is what django should do, because it overcomes this problem (and also means the expression is not evaluated multiple times by the database, being in the SELECT and also the WHERE parts).

user_1880
Related