Django ORM: Retrieving posts and latest comments without performing N+1 queries

Question

I have a very standard, basic social application -- with status updates (i.e., posts), and multiple comments per post.

Given the following simplified models, is it possible, using Django's ORM, to efficiently retrieve all posts and the latest two comments associated with each post, without performing N+1 queries? (That is, without performing a separate query to get the latest comments for each post on the page.)

class Post(models.Model):
    title = models.CharField(max_length=255)
    text = models.TextField()

class Comment(models.Model):
    text = models.TextField()
    post = models.ForeignKey(Post, related_name='comments')

    class Meta:
        ordering = ['-pk']

Post.objects.prefetch_related('comments').all() fetches all posts and comments, but I'd like to retrieve a limited number of comments per post only.

UPDATE:

I understand that, if this can be done at all using Django's ORM, it probably must be done with some version of prefetch_related. Multiple queries are totally okay, as long as I avoid making N+1 queries per page.

What is the typical/recommended way of handling this problem in Django?

UPDATE 2:

There seems to be no direct and easy way to do this efficiently with a simple query using the Django ORM. There are a number of helpful solutions/approaches/workarounds in the answers below, including:

• Caching the latest comment IDs in the database
• Performing a raw SQL query
• Retrieving all comment IDs and doing the grouping and "joining" in python
• Limiting your application to displaying the latest comment only

I didn't know which one to mark as correct because I haven't gotten a chance to experiment with all of these methods yet -- but I awarded the bounty to hynekcer for presenting a number of options.

UPDATE 3:

I ended up using @user1583799's solution.

Answer 1

If you're using Django 1.7 the new Prefetch objects—allowing you to customize the prefetch queryset—could prove helpful.

Unfortunately I can't think of a simple way to do exactly what you're asking. If you're on PostgreSQL and are willing to get just the latest comment for each post, the following should work in two queries:

comments = Comment.objects.order_by('post_id', '-id').distinct('post_id')
posts = Post.objects.prefetch_related(Prefetch('comments',
                                               queryset=comments,
                                               to_attr='latest_comments'))

for post in posts:
    latest_comment = post.latest_comments[0] if post.latest_comments else None

Another variation: if your comments had a timestamp and you wanted to limit the comments to the most recent ones by date, that would look something like:

comments = Comment.objects.filter(timestamp__gt=one_day_ago)

...and then as above. Of course, you could still post-process the resulting list to limit the display to a maximum of two comments.

Answer 2

This solution is optimized for memory requirements, as you expect it important. It needs three queries. The first query asks for posts, the second query only for tuples (id, post_id). The third for details of filtered latest comments.

from itertools import groupby, islice
posts = Post.objects.filter(...some your flter...)
# sorted by date or by id
all_comments = (Comment.objects.filter(post__in=posts).values('post_id')
        .order_by('post_id', '-pk'))
last_comments = []
# the queryset is evaluated now. Only about 100 itens chunks are in memory at
# once during iterations.
for post_id, related_comments in groupby(all_comments(), lambda x: x.post_id):
        last_comments.extend(islice(related_comments, 2))
results = {}
for comment in Comment.objects.filter(pk__in=last_comments):
    results.setdefault(comment.post_id, []).append(comment)
# output
for post in posts:
    print post.title, [x.comment for x in results[post.id]]

But I think it will be faster for many database backends to combine the second and the third query into one and so to ask immediately for all fields of comments. Unuseful comments will be forgotten immediately.

The fastest solution would be with nested queries. The algorithm is like the one above, but everything is realized by raw SQL. It is limited only to some backends like PostgresQL.

---

EDIT
I agree that is not useful for you

... prefetch loads into memory thousands of comments, 99% of which will not be shown.

and therefore I wrote that relatively complicated solution that 99% of them will be read continuously without loading into memory.

---

EDIT

• All examples are for the condition that you wand post_id in [1, 3, 5] (enything selected earlier by categories etc.)
• In all cases create the index for Comments on fields ['post', 'pk']

A) Nested query for PostgresQL

SELECT post_id, id, text FROM 
  (SELECT post_id, id, text, rank() OVER (PARTITION BY post_id ORDER BY id DESC)
   FROM app_comment WHERE post_id in (1, 3, 5)) sub
WHERE rank <= 2
ORDER BY post_id, id

Or explicitely require with less memory if we don't believe the optimizer. It should read data only from index in two inner selects, which is much less data than from the table.:

SELECT post_id, id, text FROM app_comment WHERE id IN
  (SELECT id FROM
     (SELECT id, rank() OVER (PARTITION BY post_id ORDER BY id DESC)
      FROM app_comment WHERE post_id in (1, 3, 5)) sub
   WHERE rank <= 2)
ORDER BY post_id, id

B) With a cached ID of the oldest displayed comment

• Add field "oldest_displayed" to Post

  class Post(models.Model):
    oldest_displayed = models.IntegerField()

• Filter comments for pk if interesting posts (that you have selected earlier by categories etc.)

Filter

from django.db.models import F
qs = Comment.objects.filter(
       post__pk__in=[1, 3, 5],
       post__oldest_displayed__lte=F('pk')
       ).order_by('post_id', 'pk')
pprint.pprint([(x.post_id, x.pk) for x in qs])

Hmm, very nice ... and how it is compiled by Django?

>>> print(qs.query.get_compiler('default').as_sql()[0])      # added white space
SELECT "app_comment"."id", "app_comment"."text", "app_comment"."post_id"
FROM "app_comment"
INNER JOIN "app_post" ON ( "app_comment"."post_id" = "app_post"."id" )
WHERE ("app_comment"."post_id" IN (%s, %s, %s)
      AND "app_post"."oldest_displayed" <= ("app_comment"."id"))
ORDER BY app_comment"."post_id" ASC, "app_comment"."id" ASC

Prepare all "oldest_displayed" by one nested SQL initially (and set zero for posts with less than two comments):

UPDATE app_post SET oldest_displayed = 0

UPDATE app_post SET oldest_displayed = qq.id FROM
  (SELECT post_id, id FROM
     (SELECT post_id, id, rank() OVER (PARTITION BY post_id ORDER BY id DESC)
      FROM app_comment ) sub
   WHERE rank = 2) qq
WHERE qq.post_id = app_post.id;