[Django] #34413: Variant of Prefetch but for the earliest/latest related object
Django
-------------------------------------+-------------------------------------
Reporter: Willem | Owner: nobody
Van Onsem |
Type: New | Status: assigned
feature |
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
A frequently asked feature that seems to be missing is fetch the
earlest/latest related object for each item. Indeed, we can for example
work with a subquery to fetch *the primary key* of the earliest/latest
related object, but not that object itself.
It turns out however that automating this is not that complicated. What we
need to do is (automatically) construct a reverse filter, so if we want
the latest `Comment` of each `Post`, we make a queryset that makes a
`Comment.objects.filter(post_id=OuterRef('pk'))`, then we convert that
into a subquery that will, for each `Post` fetch the primary key of the
latest comment, and slightly alter the logic that is already used to for
prefetching to then prefetch all the `Comment`s for these primary keys,
and add attributes to the corresponding `Post`s.
I made a small proof-of-concept that should normally work for (most)
cases: it allows one to use an arbitrary queryset and specify an ordering
(or use the ordering already in the queryset or by the model). It can also
span over multiple layers. It requires to *always* specify a `to_attr`,
since, especially when spanning over multiple relations, the default
`to_attr` would require double underscores.
The most ugly part is that we work with an annotation that is then
accessible by the user, so it is not somehow hidden. Technically we could
remove the attribute, or do something extra in the ORM to prevent exposing
this attribute.
I did not yet check what querysets will be problematic. For example a
sliced queryset would (very likely) not work, and likely most/all
restrictions already in place for a the queryset of a `Prefetch` object
are still applicable.
--
Ticket URL: <https://code.djangoproject.com/ticket/34413>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "prefetching_the_earliest_latest_related_object_.patch"
added.
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Willem Van Onsem):
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
A simple demonstration of the `PrefetchEarliest` could be:
{{{
User.objects.prefetch_related(PrefetchEarliest('groups',
to_attr='first_group', Group.objects.order_by('name'))
}}}
This will then annotate `.first_group` to each `User` in the queryset with
the group for that user that is by name (lexicographically) first for
example.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Component: Database layer | Version: dev
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => duplicate
Comment:
Pretty sure this is a duplicate of #26780 fixed in the soon to be released
4.2.
Since 242499f2dc2bf24a9a5c855690a2e13d3303581a `Prefetch(queryset)`
supports slicing through the use of filtering over partitioned rank
(`RANK() OVER (PARTITION BY <join_field> ORDER BY <order_by>`) so earliest
and latest related objects (or any slice of related objects for that
matter) can be prefetched.
{{{#!python
User.objects.prefetch_related(
Prefetch("groups", queryset=Group.objects.order_by('name')[:1],
to_attr="first_group")
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> A simple demonstration of the `PrefetchEarliest` could be:
>
> {{{
> User.objects.prefetch_related(PrefetchEarliest('groups',
> to_attr='first_group', Group.objects.order_by('name'))
> }}}
>
> This will then annotate `.first_group` to each `User` in the queryset
> with the group for that user that is by name (lexicographically) first
> for example.
New description:
What would happen if we span over multiple relations, like:
{{{
User.objects.prefetch_related(
Prefetch("groups__permissions",
queryset=Permission.objects.order_by('codename')[:1],
to_attr="first_permission")
)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Willem Van Onsem):
Replying to [comment:3 Simon Charette]
What would happen if we span over multiple relations, like:
{{{
User.objects.prefetch_related(
Prefetch("groups__permissions",
queryset=Permission.objects.order_by('codename')[:1],
to_attr="first_permission")
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> What would happen if we span over multiple relations, like:
>
> {{{
> User.objects.prefetch_related(
> Prefetch("groups__permissions",
> queryset=Permission.objects.order_by('codename')[:1],
> to_attr="first_permission")
> )
> }}}
New description:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Nothing special really, there's one query per ''depth'' of prefetching and
`Prefetch.queryset` is always for the trailing relation of the lookup so
something along the lines of
{{{#!sql
SELECT auth_group.*
FROM auth_group
JOIN user_groups ON (user_groups.group_id = auth_group.id)
WHERE user_groups.user_id IN ($user_ids)
}}}
then
{{{#!sql
SELECT * FROM (
SELECT
auth_permission.*,
RANK() OVER (
PARTITION BY auth_group_permissions.group_id
ORDER BY auth_permission.name
) _prefetch_rank
FROM auth_permission
JOIN auth_group_permissions ON (auth_permission.id =
auth_group_permissions.permission_id)
WHERE auth_group_permissions.group_id IN ($groups_ids)
)
WHERE _prefetch_rank <= 1
}}}
You can confirm it's the case by using
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Willem Van Onsem):
Replying to [comment:7 Simon Charette]
Well that was what I thought, the semantics differ: the `Prefetch` will in
this case fetch the earliest `Permission` on each `Group` prefetched for
that user, whereas the `PrefetchEarliest` would annotate the earliest
`Permission` to each `User`. So it limits "bandwidth", and annotates at
the same layer. The two therefore don't seem duplicates if I understand
correctly?
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Not sure I understand the rationale here, what does it even mean to
''annotate the earliest Permission to each User'' since `User` relates to
`Permission` through groups and that's how `prefetch_related` operates.
Why not use a `Subquery` annotation instead of a prefetch for this
particular case?
{{{#!python
User.objects.annotate(
first_permission=Permission.objects.filter(
groups__users=OuterRef('pk')
).order_by('name')[:1]
)
}}}
{{{#!sql
SELECT
user.*,
(
SELECT permission.*
FROM permission
JOIN group_permissions ON (group_permissions.permission_id =
permission.id)
JOIN user_groups ON (user_groups.group_id =
group_permissions.group_id)
WHERE user_groups.user_id = user.id
ORDER BY permission.name
LIMIT 1
) first_permission
FROM user
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Willem Van Onsem | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gordon Wrigley):
I would like to do singular prefetches, which seems kinda like what this
is asking for.
With regard to:
{{{#!python
User.objects.prefetch_related(
Prefetch("groups", queryset=Group.objects.order_by('name')[:1],
to_attr="first_group")
)
}}}
The downside of this is "first_group" is actually a 1 element list. I was
imagining maybe it could be just the item when there is a single item
slice, or failing that a "flat=True" option on Prefetch.
--
Ticket URL: <https://code.djangoproject.com/ticket/34413#comment:10>