#26211: prefetch_related with Prefetch with queryset with explicit Ordering
Ignored
-------------------------------------+-------------------------------------
Reporter: cancan101 | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> Given these models:
>
> {{{
> class Parent(models.Model):
> pass
>
> class Child(models.Model):
> saved_dt = models.DateTimeField(auto_now_add=True)
> parent = models.ForeignKey(Parent)
> }}}
>
> I am executing this query (for each `Parent` I want to get the
> `child_set` in sorted order):
>
> {{{
> [m.child_set.order_by('saved_dt') for m in
> Parent.objects.prefetch_related(Prefetch('child_set',
> Child.objects.order_by('saved_dt'))).all()]
> }}}
>
> I would expect that to be two SQL queries, but in reality I see `N+2`
> where `N` is the number of `Child`s:
>
> {{{
> SELECT "prefetch_parent"."id" FROM "prefetch_parent"; args=()
> SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
> "prefetch_child"."parent_id" FROM "prefetch_child" WHERE
> "prefetch_child"."parent_id" IN (1, 2) ORDER BY
> "prefetch_child"."saved_dt" ASC; args=(1, 2)
> SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
> "prefetch_child"."parent_id" FROM "prefetch_child" WHERE
> "prefetch_child"."parent_id" = 1 ORDER BY "prefetch_child"."saved_dt" ASC
> LIMIT 21; args=(1,)
> SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
> "prefetch_child"."parent_id" FROM "prefetch_child" WHERE
> "prefetch_child"."parent_id" = 2 ORDER BY "prefetch_child"."saved_dt" ASC
> LIMIT 21; args=(2,)
> }}}
New description:
Given these models:
{{{
class Parent(models.Model):
pass
class Child(models.Model):
saved_dt = models.DateTimeField(auto_now_add=True)
parent = models.ForeignKey(Parent)
}}}
I am executing this query (for each `Parent` I want to get the `child_set`
in sorted order):
{{{
[m.child_set.order_by('saved_dt') for m in
Parent.objects.prefetch_related(Prefetch('child_set',
Child.objects.order_by('saved_dt'))).all()]
}}}
I would expect that to be two SQL queries, but in reality I see `N+2`
where `N` is the number of `Child`s:
{{{
SELECT "prefetch_parent"."id" FROM "prefetch_parent"; args=()
SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
"prefetch_child"."parent_id" FROM "prefetch_child" WHERE
"prefetch_child"."parent_id" IN (1, 2) ORDER BY
"prefetch_child"."saved_dt" ASC; args=(1, 2)
SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
"prefetch_child"."parent_id" FROM "prefetch_child" WHERE
"prefetch_child"."parent_id" = 1 ORDER BY "prefetch_child"."saved_dt" ASC
LIMIT 21; args=(1,)
SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
"prefetch_child"."parent_id" FROM "prefetch_child" WHERE
"prefetch_child"."parent_id" = 2 ORDER BY "prefetch_child"."saved_dt" ASC
LIMIT 21; args=(2,)
}}}
I would hope that the second call to `children.order_by('saved_dt')` can
return self since the queryset is already sorted by the desired key.
--
Comment (by cancan101):
@charettes I actually think that the example you provided indicated what I
would consider a deficiency of the ORM: coalescing multiple calls to
`order_by` with the same argument (I have modified the original queryset):
{{{
>>> children = Child.objects.order_by('saved_dt').all()
>>> list(children)
[<Child: Child object>, <Child: Child object>]
(0.001) SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
"prefetch_child"."parent_id" FROM "prefetch_child" ORDER BY
"prefetch_child"."saved_dt" ASC; args=()
>>> list(children)
[<Child: Child object>, <Child: Child object>]
>>> children.order_by('saved_dt')
[<Child: Child object>, <Child: Child object>]
(0.000) SELECT "prefetch_child"."id", "prefetch_child"."saved_dt",
"prefetch_child"."parent_id" FROM "prefetch_child" ORDER BY
"prefetch_child"."saved_dt" ASC LIMIT 21; args=()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26211#comment:6>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.bce804fba9f396a494a028ca94f2b7f8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
