django - 原始 SQL -> Django ORM 代码 : Query that returns all blog posts with most recent approved comment for a specific user

Question

我正在开发一个简单的博客系统。这是我的 models.py文件:

from django.contrib.auth.models import User
from django.db import models


class Comment(models.Model):
    user = models.ForeignKey(User)
    post = models.ForeignKey('Post')
    content = models.TextField()
    approved = models.NullBooleanField()

    class Meta:
        ordering = ('-id',)

    def __unicode__(self):
        return u'Comment by %s' % self.user


class Post(models.Model):
    user = models.ForeignKey(User)
    title = models.CharField(max_length=200)
    slug = models.CharField(max_length=50)
    content = models.TextField()

    class Meta:
        ordering = ('title',)

    def __unicode__(self):
        return self.title

这是我命名为 testdata.json 的夹具中的一些测试数据(“some_author”用户是 super 用户，密码是“Stack Overflow”):

[
{
  "pk": 1, 
  "model": "auth.user", 
  "fields": {
    "username": "some_author", 
    "first_name": "Some", 
    "last_name": "Author", 
    "is_active": true, 
    "is_superuser": true, 
    "is_staff": true, 
    "last_login": "2014-07-02T20:18:49Z", 
    "groups": [], 
    "user_permissions": [], 
    "password": "pbkdf2_sha256$12000$PTl1hfgcIGZy$/0w1jNMBuKi9zk11JXhoS5WrbMBUgMDkZAhEvNEelbs=", 
    "email": "some_author@example.com", 
    "date_joined": "2014-07-02T20:18:29Z"
  }
},
{
  "pk": 2, 
  "model": "auth.user", 
  "fields": {
    "username": "some_reader", 
    "first_name": "Some", 
    "last_name": "Reader", 
    "is_active": true, 
    "is_superuser": false, 
    "is_staff": false, 
    "last_login": "2014-07-02T20:21:10Z", 
    "groups": [], 
    "user_permissions": [], 
    "password": "pbkdf2_sha256$12000$CtTGfFeOaRhd$oVR6zFSpK2qg1AZ4fgdBG/wt6Sr56dHsEIxFO99mHC8=", 
    "email": "some_reader@example.com", 
    "date_joined": "2014-07-02T20:21:10Z"
  }
},
{
  "pk": 3, 
  "model": "auth.user", 
  "fields": {
    "username": "another_reader", 
    "first_name": "Another", 
    "last_name": "Reader", 
    "is_active": true, 
    "is_superuser": false, 
    "is_staff": false, 
    "last_login": "2014-07-02T20:21:34Z", 
    "groups": [], 
    "user_permissions": [], 
    "password": "pbkdf2_sha256$12000$ZPnmV7fVeie3$08H2vv3A8Py4E92+uVAIiEaeg8CAL5deTyNAZj1YJMs=", 
    "email": "another_reader@example.com", 
    "date_joined": "2014-07-02T20:21:34Z"
  }
},
{
  "pk": 1, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 1 on post 1: approved", 
    "post": 1, 
    "user": 2, 
    "approved": true
  }
},
{
  "pk": 2, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 1 on post 2: not approved", 
    "post": 2, 
    "user": 2, 
    "approved": false
  }
},
{
  "pk": 3, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 2 on post 3: approved", 
    "post": 3, 
    "user": 2, 
    "approved": true
  }
},
{
  "pk": 4, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 2 of 2 on post 3: not approved", 
    "post": 3, 
    "user": 2, 
    "approved": false
  }
},
{
  "pk": 5, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 2 on post 4: not approved", 
    "post": 4, 
    "user": 2, 
    "approved": false
  }
},
{
  "pk": 6, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 2 of 2 on post 4: approved", 
    "post": 4, 
    "user": 2, 
    "approved": true
  }
},
{
  "pk": 7, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 2 on post 5: approved", 
    "post": 5, 
    "user": 2, 
    "approved": true
  }
},
{
  "pk": 8, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 2 of 2 on post 5: approved", 
    "post": 5, 
    "user": 2, 
    "approved": true
  }
},
{
  "pk": 9, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 2 on post 6: not approved", 
    "post": 6, 
    "user": 2, 
    "approved": false
  }
},
{
  "pk": 10, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 2 of 2 on post 6: not approved", 
    "post": 6, 
    "user": 2, 
    "approved": false
  }
},
{
  "pk": 11, 
  "model": "blog.comment", 
  "fields": {
    "content": "Comment 1 of 1 on post 7: approved", 
    "post": 7, 
    "user": 3, 
    "approved": true
  }
},
{
  "pk": 1, 
  "model": "blog.post", 
  "fields": {
    "content": "First post", 
    "slug": "post-1", 
    "user": 1, 
    "title": "Post 1"
  }
},
{
  "pk": 2, 
  "model": "blog.post", 
  "fields": {
    "content": "Second post", 
    "slug": "post-2", 
    "user": 1, 
    "title": "Post 2"
  }
},
{
  "pk": 3, 
  "model": "blog.post", 
  "fields": {
    "content": "Third post", 
    "slug": "post-3", 
    "user": 1, 
    "title": "Post 3"
  }
},
{
  "pk": 4, 
  "model": "blog.post", 
  "fields": {
    "content": "Fourth post", 
    "slug": "post-4", 
    "user": 1, 
    "title": "Post 4"
  }
},
{
  "pk": 5, 
  "model": "blog.post", 
  "fields": {
    "content": "Fifth post", 
    "slug": "post-5", 
    "user": 1, 
    "title": "Post 5"
  }
},
{
  "pk": 6, 
  "model": "blog.post", 
  "fields": {
    "content": "Sixth post", 
    "slug": "post-6", 
    "user": 1, 
    "title": "Post 6"
  }
},
{
  "pk": 7, 
  "model": "blog.post", 
  "fields": {
    "content": "Seventh post", 
    "slug": "post-7", 
    "user": 1, 
    "title": "Post 7"
  }
},
{
  "pk": 8, 
  "model": "blog.post", 
  "fields": {
    "content": "Eighth post", 
    "slug": "post-8", 
    "user": 1, 
    "title": "Post 8"
  }
}
]

我正在尝试在数据库中查询所有博客文章以及满足这两个条件的每个博客文章的最新评论:

该评论由“某些读者”(user_id = 2)

发表

评论已通过

我希望查询返回所有博客文章，即使他们没有满足上述两个条件的评论。对于没有满足以上两个条件的评论的博文，返回的评论栏应该是 NULL .我有这个使用原始 SQL:

for p in Post.objects.raw(
    '''
    SELECT blog_post.id,
           blog_post.title,
           blog_comment.content
    FROM   blog_post
           LEFT OUTER JOIN (SELECT post_id,
                                   MAX(id) AS latest
                            FROM   blog_comment
                            WHERE  user_id = 2
                                   AND approved = 1
                            GROUP  BY post_id) AS x
                        ON x.post_id = blog_post.id
           LEFT OUTER JOIN blog_comment
                        ON blog_comment.post_id = x.post_id
                           AND blog_comment.id = x.latest
    ORDER  BY blog_post.id;
    '''
):
    print '%s: %s' % (
        p.title,
        p.content,
    )

上面的代码输出这个(这是我想要的):

Post 1: Comment 1 of 1 on post 1: approved
Post 2: None
Post 3: Comment 1 of 2 on post 3: approved
Post 4: Comment 2 of 2 on post 4: approved
Post 5: Comment 2 of 2 on post 5: approved
Post 6: None
Post 7: None
Post 8: None

我的问题是:是否有可能(有效地)做同样的事情，但不诉诸原始 SQL？我喜欢尽可能避免原始查询。

Answer 1

如果没有 django orm 范式中的原始 sql，你就无法做到这一点。
但是您可以通过对 db 的两个查询来实现:

from django.db.models import Max
posts = Post.objects.annotate(Max('comment_set__id'))
comments_cache = Comment.objects.filter(id__in= posts.values('id', flat=True))
comments_dict = dict([(item.id, item) for item in comments_cache])
for item in posts:
     print post, comments_dict[item.id]

我经常进行复杂的查询，但仍然找不到比在缓存对象中使用很少的查询获取我需要的所有数据并根据需要对其进行分组更好的方法。

请不要使用以下代码:

#get_comment: return self.comment_set.filter(user=user, approved=True).latest('id')
for post in Post.objects.all():
    print post.get_comment(request.user)

它将向数据库生成 len(posts) sql-queries。这是不好的做法。