#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:

 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 (

 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 =
 old_get_prefetch_querysets_reverse_one_to_one =

 def get_prefetch_querysets_forward_many_to_one(self, *args, **kwargs):
     result = old_get_prefetch_querysets_forward_many_to_one(self, *args,
     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,
     if not hasattr(result[0], '_do_not_modify_order_by'):
         result = (result[0].order_by(), *result[1:])
     return result

 ForwardManyToOneDescriptor.get_prefetch_querysets =
 ReverseOneToOneDescriptor.get_prefetch_querysets =

 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):

         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(
         a_s = list(A.objects.all().prefetch_related(

 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>
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 view this discussion on the web visit 

Reply via email to