#30093: queryset, union, order_by over an annotated field
-------------------------------------+-------------------------------------
Reporter: greenkey | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset union | Triage Stage:
annotate | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by greenkey:
Old description:
> 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.
>
> {{{#!python
> 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 or am I doing something wrong?
New description:
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.
{{{#!python
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
...
a.union(b).order_by('rank')
# this is OK
}}}
Is this a Django bug or am I doing something wrong?
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30093#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.f271b9d44c36d9dcc2c514c23d7200f8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
