#25403: A quite common use of .extra()
-------------------------------------+-------------------------------------
Reporter: PaoloC68 | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by jarshwah):
The prefetch api already lets you get the correct result at the expense of
an extra query. Unfortunately, Django doesn't have a good story when it
comes to supporting joins to subqueries just yet. Note that the linked
blog post also causes an extra query, so the below is comparable (and
supported!)
{{{
>>> p1 = Post.objects.create(text='Post 1')
>>> p2 = Post.objects.create(text='Post 2')
>>> c1 = Comment.objects.create(text='Comment 1', post=p1)
>>> c2 = Comment.objects.create(text='Comment 2', post=p1)
>>> c3 = Comment.objects.create(text='Comment 3', post=p2)
>>> latest_comment_ids =
Comment.objects.values('post_id').annotate(max_id=Max('id')).values('max_id')
>>> latest_comments = Comment.objects.filter(id__in=latest_comment_ids)
>>> posts = Post.objects.prefetch_related(Prefetch('comment_set',
queryset=latest_comments, to_attr='latest_comment'))
>>> for post in posts:
... print(post.text)
... for comment in post.latest_comment:
... print(comment.text)
...
Post 1
Comment 2
Post 2
Comment 3
}}}
The queries executed will be close to:
{{{
SELECT * FROM "scratch_post"
-- and --
SELECT
"scratch_comment"."id",
"scratch_comment"."text",
"scratch_comment"."needs_attention",
"scratch_comment"."created_at",
"scratch_comment"."post_id"
FROM "scratch_comment"
WHERE "scratch_comment"."id" IN (
SELECT MAX(U0."id") AS "max_id"
FROM "scratch_comment" U0
GROUP BY U0."post_id"
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25403#comment:3>
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/066.5197314aaa40a8c4b10444c44ec83c84%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
