Django ORM：检索帖子和最新评论，而不执行N + 1查询 [英] Django ORM: Retrieving posts and latest comments without performing N+1 queries

问题描述

我有一个非常标准的基本社交应用程序 - 具有状态更新（即帖子）和每个帖子的多个评论。

给定以下简化模型，使用Django的ORM可以有效地检索与每个帖子相关联的所有帖子和最新的两个注释，而不执行N + 1个查询？ （也就是说，不执行单独的查询来获取页面上每个帖子的最新评论。）

  class Post（models .Model）：
 title = models.CharField（max_length = 255）
 text = models.TextField（）
 
类注释（models.Model）：
文本= models.TextField（）
 post = models.ForeignKey（Post，related_name ='comments'）
 
 class Meta：
 ordering = ['-pk'] 
  

Post.objects.prefetch_related（'comments'）。all（）获取所有帖子和评论，但我想仅在每篇文章中检索有限数量的评论。

更新： / p>

我明白，如果这可以使用Django的ORM完成，可能必须使用某些版本的 prefetch_related 。多个查询完全正常，只要我避免每页N + 1个查询。

Django中处理此问题的典型/推荐方式是什么？ p>

更新2：

似乎没有直接和容易的方式来做到这一点使用Django ORM进行简单查询。在下面的答案中有许多有用的解决方案/方法/解决方法，其中包括：

• 在数据库中缓存最新的注释ID
• 
  
• 执行原始SQL查询


• 检索所有注释ID，并在python


• 中进行分组和加入限制您的应用程序仅显示最新的评论

我不知道哪一个标记为正确，因为我没有有机会尝试所有这些方法 - 但是我向Hynekcer奖励了许多选项。

更新3：

我最终使用@ user1583799的解决方案。

解决方案

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 = 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在帖子中：
 p.get_related_replies = [r for r in related_replies if r.post_id == p.id] 
  

当新评论出现时，更新 related_replies 。

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.

解决方案

prefetch_related('comments') will fetch all comments of the posts.

I had the same problem, and the database is Postgresql. I found a way:

Add a extra fieldrelated_replies. Note the FieldType is ArrayField, which support in django1.8dev. I copy the code to my project(the version of django is 1.7), just change 2 lines, it works.(or use djorm-pg-array )

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

And use two queries:

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]

When new comment comes, update related_replies.

这篇关于Django ORM：检索帖子和最新评论，而不执行N + 1查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！