Django ORM: receiving messages and recent comments without performing N + 1 requests

Question

I have a standard basic social application - with status update (i.e. posts) and a few comments per post.

Given the following simplified models, is it possible, using Django ORM, to efficiently receive all messages and the last two comments associated with each message without performing N + 1 requests? (That is, without fulfilling a separate request 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() retrieves all posts and comments, but I would like to receive a limited number of comments per post only.

UPDATE:

, Django ORM, , prefetch_related. , N + 1 .

/ Django?

2:

, Django ORM. // , :

• SQL-
• "" python

, , , hynekcer .

3:

@user1583799.

Answer 1

prefetch_related('comments') .

, Postgresql. :

related_replies. , FieldType ArrayField, django1.8dev. ( django - 1.7), 2 , . ( djorm-pg-array)

class Post(models.Model): related_replies = ArrayField(models.IntegerField(), size=10, null=True)

:

posts = model.Post.object.filter()

related_replies_id = chain(*[p.related_replies for p in posts])
related_replies = models.Comment.objects.filter(
    id__in=related_replies_id).select_related('created_by')[::1]  # cache queryset

for p in posts:
    p.get_related_replies = [r for r in related_replies if r.post_id == p.id]

related_replies.

Answer 2

Django 1.7, Prefetch, .

, , . PostgreSQL , :

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

: , , :

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

... , . , , .

Answer 3

, , . . , - (id, post_id). .

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]]

, . .

. , raw SQL. , PostgresQL.

---

, .

... prefetch , 99% .

, 99% .

---

• , wand post_id [1, 3, 5] (enything, ..).
• ['post', 'pk']

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

, . , , .:

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)

• "oldest_displayed"

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

• pk, ( ..)

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

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

"oldest_displayed" SQL ( ):

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;