如何使用 annotate() 来计算 Django 中相关模型的子集？

发布于 2024-09-25 14:31:38 字数 708 浏览 8 评论 0原文

我正在尝试使用 Django 的注释功能将相关模型的计数添加到查询集中。但是，我不需要相关对象的完整计数，我只想计算活动对象的数量（即“is_active=True”）。我不知道如何过滤计数。

（简化的）相关模型：

class Post(models.Model):
    user = models.ForeignKey(User)
    title = models.CharField(max_length=80)
    body = models.TextField()

class Comment(models.Model):
    user = models.ForeignKey(User)
    post = models.ForeignKey(Post)
    comment_body = models.CharField(max_length=80)
    is_active = models.BooleanField(default=True)

在视图中，我试图注释一个查询集：

queryset=Post.objects.all().annotate(num_comments=Count('comment', distinct=True))

上面计算了与帖子相关的所有评论，而我只想计算“is_active”的评论。 Google 和 Django 文档在这里没有帮助我。有人遇到过并解决过这个问题吗？

原文

I'm trying to use Django's annotate feature to add the count of a related model to a queryset. However, I don't want a full count of related objects, I only want to count the active ones (i.e., "is_active=True"). I can't figure out how to filter down the count.

The (simplified) relevant models:

class Post(models.Model):
    user = models.ForeignKey(User)
    title = models.CharField(max_length=80)
    body = models.TextField()

class Comment(models.Model):
    user = models.ForeignKey(User)
    post = models.ForeignKey(Post)
    comment_body = models.CharField(max_length=80)
    is_active = models.BooleanField(default=True)

In a view, I'm trying to annotate a queryset:

queryset=Post.objects.all().annotate(num_comments=Count('comment', distinct=True))

The above counts all the comments related to a post, whereas I only want to count the "is_active" ones. Google and the Django docs aren't helping me here. Has anyone had and solved this problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾铮苏瑾 2024-10-02 14:31:38

您只需在进行注释之前过滤 is_active ：

Post.objects.filter(comment__is_active=True).annotate(num_comments=Count('comment'))

请参阅此处的解释。

You just need to filter on is_active before doing the annotation:

Post.objects.filter(comment__is_active=True).annotate(num_comments=Count('comment'))

See the explanation here.

回复收藏 0 原文

幽蝶幻影 2024-10-02 14:31:38

这就是我必须“注释”我的帖子查询集上的活跃评论数量的方式：

Post.objects.extra(select={"num_comments":
     """
     SELECT COUNT(myapp_comment.id) FROM myapp_reply
     WHERE myapp_comment.is_active='1' AND 
     myapp_comment.post_id = myapp_post.id
     """
     },)

不漂亮，但它有效。正如我在上面的评论中提到的，不可能为此使用内置聚合函数 annotate() ，因为它计算了所有相关评论，而我只想计算活跃的相关评论。

丹尼尔的解决方案不起作用，因为它过滤掉了没有评论的帖子。我不想过滤掉任何帖子，只想过滤掉不活跃的评论。

如果有人有更好的解决方案，我很乐意投票并最佳回答你！

This is how I had to "annotate" the number of active comments on my Post queryset:

Post.objects.extra(select={"num_comments":
     """
     SELECT COUNT(myapp_comment.id) FROM myapp_reply
     WHERE myapp_comment.is_active='1' AND 
     myapp_comment.post_id = myapp_post.id
     """
     },)

Not pretty, but it works. As I mentioned in a comment above, it wasn't possible to use the built-in aggregation function annotate() for this, since that counted all related comments and I only wanted to count the active related comments.

Daniel's solution didn't work, because it filtered out Posts which had no comments. I don't want to filter out any Posts, just inactive comments.

If anyone has a better solution, I will gladly up-vote and best-answer you!

回复收藏 0 原文

︶葆Ⅱㄣ 2024-10-02 14:31:38

根据您使用的数据库，有两种变体。如果您使用MySQL，解决方案简单而优雅：

Post.objects.annotate(num_comments=Sum('comment.is_active'))

这有效，因为在数据库中布尔字段是整数，True 为 1，False 为 0。

但这仅在 MySQL 中有效并且仅适用于布尔字段。完成这项工作的更通用方法适用于所有数据库，并且可以进行更复杂的检查，这是使用插入一点“hacky”的一点 SQL：

Post.objects.annotate(num_comments=Count('comment',
    field='CASE WHEN myapp_comment.is_active THEN 1 END'))

我在我的个人博客中遇到了同样的问题，这就是解决方案。我为此写了一篇博客文章。 http://venelin.sytes.net/blog/ django/filtrirane-na-agregirash-count-v-django/。它是保加利亚语，但我的网站使用谷歌翻译。翻译不是很好，但可能有助于理解为什么会这样。

There is two variants based on what database you are using. If you use MySQL the solution is simple and elegant:

Post.objects.annotate(num_comments=Sum('comment.is_active'))

This works because in database boolean fields are integers and True is 1, False is 0.

But this works ONLY in MySQL and works ONLY for boolean fields. The more general way to do the job which works on all databases and can do more complex checks is using a little SQL inserted a little 'hacky':

Post.objects.annotate(num_comments=Count('comment',
    field='CASE WHEN myapp_comment.is_active THEN 1 END'))

I have the same problem in my personal blog, and that was the solution. I write a blog post for that. http://venelin.sytes.net/blog/django/filtrirane-na-agregirash-count-v-django/. It's on bulgarian but my site uses google translation. The translation is not very good but may help to understand why this works.

回复收藏 0 原文

~没有更多了~