Changes between Initial Version and Version 9 of Ticket #35309


Ignore:
Timestamp:
Mar 16, 2024, 9:10:49 AM (8 months ago)
Author:
Simon Charette
Comment:

I'm sorry for the awkward back and forth here but reviewing Laurent's PR made something clear to me that wasn't from the origin report.

The requested optimization here is solely for single valued relationships (backward and forward 1:1 and forward 1:M). In this scenario, as pointed out by Laurent, ORDER BY doesn't matter as the related collection is either empty or a singleton and thus order_by() can always be used in their respective get_prefetch_queryset.

In the light of this realization I've adjusted the report and moved back this ticket to an accepted optimization.

Laurent, as for the patch I suggest simply decorating existing tests that make use of prefetching for single valued relationship (there are plenty in prefetch_related tests) which assertNumQueries and use the context queries to assert against the lack of ORDER BY.

e.g. 

with self.assertNumQueries(2) as ctx:
   list(Book.objects.prefetch_related("author"))
self.assertNotIn("ORDER BY", ctx.queries[-1]["sql"])

I think that systematically calling order_by without the _do_not_modify_order_by should do.

Again, sorry for the misunderstanding and thank you for your efforts towards contributing this improvement to Django.

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #35309

    • Property Has patch set
    • Property Cc Simon Charette added
    • Property Keywords single-valued added
    • Property Patch needs improvement set
    • Property Triage Stage Unreviewed → Accepted
    • Property Summary Remove Order by on models when prefetching by id → Elide ordering of prefetch querysets for single valued relationships

  • Ticket #35309 – Description

    initial v9  
    1 Hello,
     1While the ordering of multi-valued relationships must be preserved when prefetching relationships is it unnecessary when using `prefetch_related` against single valued relationships.
    22
    3 I don't know if the "bug" is still here with Django 5.
    4 But on my version of Django, I have the following "bug" :
    5 Assume you have the following code :
     3For example, given the following models
    64
    75{{{#!python
    8 class A(models.Model):
     6class Author(models.Model):
    97    name = models.CharField(max_length=200)
    108
    … …  
    1311
    1412
    15 class B(models.Model):
    16     a = models.ForeignKey(A, related_name="bs", on_delete=models.CASCADE)
     13class Book(models.Model):
     14    title = models.CharField(max_length=200)
     15    author = models.ForeignKey(Author, related_name="books", on_delete=models.CASCADE)
    1716
    18 
    19 a1 = A.objects.create(name="a1")
    20 a2 = A.objects.create(name="a2")
    21 a3 = A.objects.create(name="a3")
    22 a4 = A.objects.create(name="a4")
    23 a5 = A.objects.create(name="a5")
    24 a6 = A.objects.create(name="a6")
    25 a7 = A.objects.create(name="a7")
    26 
    27 b1 = B.objects.create(a=a1)
    28 b2 = B.objects.create(a=a2)
    29 b3 = B.objects.create(a=a3)
    30 b4 = B.objects.create(a=a4)
    31 b5 = B.objects.create(a=a5)
    32 b6 = B.objects.create(a=a6)
    33 b7 = B.objects.create(a=a7)
    34 
    35 bs = B.objects.all().prefetch_related("a")
     17    class Meta:
     18        ordering = ["title"]
    3619}}}
    3720
    38 The prefetch of as will use the order by and add useless charge on the DB server.
    39 There may be other cases than ForeignKey where the order by is useless.
    40 But since OneToOne inherits from ForeignKey, I don't see anything else right now.
     21The ordering of an author's books in `Author.objects.prefetch_related("books")` has a significance as multiple books might be associated with each authors.
    4122
    42 Hence, I request this enhancement, please :)
    43 #ClimateChangeBrake
     23It's not the case for a book's author in `Book.objects.prefetch_related("author")` through as the relationship can only contain a single author and there is a single way to order the members of a singleton.
    4424
    45 Best regards,
    46     Laurent Lyaudet
     25In other words `sorted([element], key=sort_func)` will result in `[element]` for any `sort_func`.
    4726
    48 
    49 
    50 
    51 
     27This property holds true for all the single valued relationships that the ORM supports (backward and forward 1:1 and forward 1:M) which allows the prefetching to elide any predefined ordering safely to avoid an unnecessary and possibly expensive ordering defined for the related model queryset.
Back to Top