Related managers should honor the queryset used for prefetching their results.

[Alex Rothberg]

This is a variation of #26211 which does not involve multiple calls to order_by.
Given these models:

class Parent(models.Model):
    pass

class Child(models.Model):
    saved_dt = models.DateTimeField(auto_now_add=True)
    parent = models.ForeignKey(Parent)

why does this return False?:

>>> Parent.objects.prefetch_related(Prefetch('child_set', Child.objects.order_by('saved_dt'))).first().child_set.all().ordered
False

it looks like the SQL queries generated do actually have the sort logic, but the query set does not reflect this fact:

SELECT "prefetch_parent"."id" FROM "prefetch_parent" ORDER BY "prefetch_parent"."id" ASC LIMIT 1; args=()
SELECT "prefetch_child"."id", "prefetch_child"."saved_dt", "prefetch_child"."parent_id" FROM "prefetch_child" WHERE "prefetch_child"."parent_id" IN (1) ORDER BY "prefetch_child"."saved_dt" ASC; args=(1,)

[Tim Graham]

I'm not familiar with the internals of prefetch_related() but it looks like it could be a bug. It might be tricky or infeasible to fix for some reason.

[Simon Charette]

I'm not sure this worth fixing but here's why ordered=False and how we could fix this.

When using prefetch_related() without to_attr the prefetched results are stored in the cached results of a QuerySet instance created by ​calling the all() method on the related manager (e.g. parent.child_set.all()).

If we were to fix this we should add a branch that reuses the queryset provided to the Prefetch object and filter() it by the related manager lookups (e.g. qs.filter(parent=parent)).

Retrieving these related manager lookups is a problem in itself as the forward many to one and reverse many to many managers don't expose a common method to bind and existing queryset to a model instance. This would require factoring out the logic of these managers get_queryset() methods into a new method that accepts an existing queryset the prefetch_one_level function could hook into.

An other issue is the fact the prefetch_one_level function doesn't seem to have access to the queryset specified by to the Prefetch object. This would need to be passed along somehow.

The last remaining point is performance. This change will require the creation of multiple querysets (which is pretty slow due to .query cloning).

e.g.

parents = Parent.objects.prefetch_related(
    Prefetch(
        'children',
        Child.objects.prefetch_related(
            Prefetch(
                'friends',
                Friend.objects.order_by('saved_dt')
            )
        ).order_by('saved_dt')
    )
)

Would require the creation of len(parents) + sum(len(parent.children) for parent in parents)) extra querysets. It's hard to figure out the order of the slowdown without benchmark but it's something we should keep in mind as prefetching is used as a performance enhancement technique.

[Simon Charette]

I had ​a try at this.

[Tim Graham]

I guess this is still a work in progress as docs for the deprecation aren't there yet.

[Simon Charette]

Pushed the deprecation required docs.

[Simon Charette <charette.s@…>]

In c92123cc:

Fixed #26226 -- Made related managers honor the queryset used for prefetching their results.

Thanks Loïc for the suggested improvements and Tim for the review.

[Tim Graham <timograham@…>]

In b70094f0:

Refs #26226 -- Removed support for related manager classes without a _apply_rel_filters() method.

Per deprecation timeline.