Opened 9 years ago

Last modified 8 years ago

#26226 closed Bug

Related managers should honor the queryset used for prefetching their results. — at Version 6

Reported by: Alex Rothberg Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: prefetch
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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

Change History (6)

comment:1 by Tim Graham, 9 years ago

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

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.

comment:2 by Simon Charette, 9 years ago

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.

comment:3 by Simon Charette, 9 years ago

Has patch: set
Keywords: prefetch added
Summary: prefetch_related with Prefetch with queryset with explicit Ordering Ignored - single order_byRelated managers should honor the queryset used for prefetching their results.
Version: 1.9master

I had a try at this.

comment:4 by Tim Graham, 9 years ago

Needs documentation: set

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

comment:5 by Simon Charette, 9 years ago

Needs documentation: unset

Pushed the deprecation required docs.

comment:6 by Tim Graham, 9 years ago

Description: modified (diff)
Triage Stage: AcceptedReady for checkin
Note: See TracTickets for help on using tickets.
Back to Top