原始SQL -> Django ORM :查询返回所有包含最近批准的特定用户评论的博客文章

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“用户是超级用户，密码是”堆栈溢出“)：

[
{
  "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"
  }
}
]

我试图查询所有博客文章的数据库以及满足这两种条件的每一篇博客文章的最新评论：

1. 这个评论是由“一些读者”(user_id = 2)提出的。
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(Post)sql查询。这是个糟糕的练习。

Answer 2

您需要所有的帖子，但是注释应该与指定的标准相匹配，否则None. For that you can add a method/property in的帖子模型就能以您想要的方式获得最近的评论。

不要认为用ORM做这件事有好的方法。

在模型中添加以下方法，

class Post(models.Model):
    ...
    #your code

    def get_comment(self, user):
        comment = None
        try:
            comment = self.comment_set.filter(user=user, approved=True).latest('id')
        except Comment.DoesNotExist:
            comment = None #no comment matching criteria

        return comment

从视图或其他代码中，可以这样做。

for post in Post.objects.all():
    print post.get_comment(request.user)