#35309: Remove Order by on models when prefetching by id
-------------------------------------+-------------------------------------
Reporter: Laurent Lyaudet | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: prefetch order_by | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Laurent Lyaudet):
I spent my night on it but I was able to make a patch, and I don't think
there will be any regression.
Consider the following models in some project
TestNoOrderByForForeignKeyPrefetches and some app test_no_order_by
models.py file:
{{{#!python
from django.core.management.base import BaseCommand
from django.db import connection
from django.db.models import Prefetch, QuerySet, RawQuerySet
from django.db.models.fields.related_descriptors import (
ForwardManyToOneDescriptor,
ReverseOneToOneDescriptor,
)
from TestNoOrderByForForeignKeyPrefetches.test_no_order_by.models import
A, B
old_prefetch_init = Prefetch.__init__
def new_prefetch_init(self, *args, **kwargs):
result = old_prefetch_init(self, *args, **kwargs)
if self.queryset is not None:
self.queryset._do_not_modify_order_by = True
return result
Prefetch.__init__ = new_prefetch_init
old_get_prefetch_querysets_forward_many_to_one =
ForwardManyToOneDescriptor.get_prefetch_querysets
old_get_prefetch_querysets_reverse_one_to_one =
ReverseOneToOneDescriptor.get_prefetch_querysets
def get_prefetch_querysets_forward_many_to_one(self, *args, **kwargs):
result = old_get_prefetch_querysets_forward_many_to_one(self, *args,
**kwargs)
if not hasattr(result[0], '_do_not_modify_order_by'):
result = (result[0].order_by(), *result[1:])
return result
def get_prefetch_querysets_reverse_one_to_one(self, *args, **kwargs):
result = old_get_prefetch_querysets_reverse_one_to_one(self, *args,
**kwargs)
if not hasattr(result[0], '_do_not_modify_order_by'):
result = (result[0].order_by(), *result[1:])
return result
ForwardManyToOneDescriptor.get_prefetch_querysets =
get_prefetch_querysets_forward_many_to_one
ReverseOneToOneDescriptor.get_prefetch_querysets =
get_prefetch_querysets_reverse_one_to_one
old_clone_queryset = QuerySet._clone
def new_clone_queryset(self):
result = old_clone_queryset(self)
if hasattr(self, '_do_not_modify_order_by'):
result._do_not_modify_order_by = True
return result
QuerySet._clone = new_clone_queryset
old_clone_raw_queryset = RawQuerySet._clone
def new_clone_raw_queryset(self):
result = old_clone_raw_queryset(self)
if hasattr(self, '_do_not_modify_order_by'):
result._do_not_modify_order_by = True
return result
RawQuerySet._clone = new_clone_raw_queryset
class Command(BaseCommand):
help = "Test"
def handle(self, *args, **options):
B.objects.all().delete()
A.objects.all().delete()
a1 = A.objects.create(name="a1")
a2 = A.objects.create(name="a2")
a3 = A.objects.create(name="a3")
a4 = A.objects.create(name="a4")
a5 = A.objects.create(name="a5")
a6 = A.objects.create(name="a6")
a7 = A.objects.create(name="a7")
b1 = B.objects.create(a=a1, name="b1")
b2 = B.objects.create(a=a2, name="b2")
b3 = B.objects.create(a=a3, name="b3")
b4 = B.objects.create(a=a4, name="b4")
b5 = B.objects.create(a=a5, name="b5")
b6 = B.objects.create(a=a6, name="b6")
b7 = B.objects.create(a=a7, name="b7")
bs = list(B.objects.all().prefetch_related("a"))
a_s = list(A.objects.all().prefetch_related("bs"))
bs = list(B.objects.all().prefetch_related(
Prefetch(
"a",
queryset=A.objects.order_by("-name")
),
))
a_s = list(A.objects.all().prefetch_related(
Prefetch(
"bs",
queryset=B.objects.order_by("-name")
),
))
print(connection.queries)
}}}
If you launch the command with python3 manage.py test_no_order_by_command,
you will see that there are 8 SELECT after the 14 INSERT and that there is
only 7 ORDER BY on them as requested.
I will prepare a PR.
--
Ticket URL: <
https://code.djangoproject.com/ticket/35309#comment:5>