多个注释中的 Django Count() [英] Django Count() in multiple annotations
问题描述
假设我有一个简单的论坛模型:
Say I have a simple forum model:
class User(models.Model):
username = models.CharField(max_length=25)
...
class Topic(models.Model):
user = models.ForeignKey(User)
...
class Post(models.Model):
user = models.ForeignKey(User)
...
现在说我想查看用户子集的每个用户有多少主题和帖子(例如,他们的用户名以ab"开头).
Now say I want to see how many topics and posts each users of subset of users has (e.g. their username starts with "ab").
因此,如果我对每个帖子和主题进行一次查询:
So if I do one query for each post and topic:
User.objects.filter(username_startswith="ab")
.annotate(posts=Count('post'))
.values_list("username","posts")
产量:
[('abe', 5),('abby', 12),...]
和
User.objects.filter(username_startswith="ab")
.annotate(topics=Count('topic'))
.values_list("username","topics")
产量:
[('abe', 2),('abby', 6),...]
然而,当我尝试对两者进行注释以获得一个列表时,我得到了一些奇怪的东西:
HOWEVER, when I try annotating both to get one list, I get something strange:
User.objects.filter(username_startswith="ab")
.annotate(posts=Count('post'))
.annotate(topics=Count('topic'))
.values_list("username","posts", "topics")
产量:
[('abe', 10, 10),('abby', 72, 72),...]
为什么主题和帖子会成倍增加?我期待这个:
Why are the topics and posts multiplied together? I expected this:
[('abe', 5, 2),('abby', 12, 6),...]
获得正确列表的最佳方法是什么?
What would be the best way of getting the correct list?
推荐答案
我认为 Count('topics', distinct=True)
应该做正确的事.这将使用 COUNT(DISTINCT topic.id)
而不是 COUNT(topic.id)
以避免重复.
I think Count('topics', distinct=True)
should do the right thing. That will use COUNT(DISTINCT topic.id)
instead of COUNT(topic.id)
to avoid duplicates.
User.objects.filter(
username_startswith="ab").annotate(
posts=Count('post', distinct=True)).annotate(
topics=Count('topic', distinct=True)).values_list(
"username","posts", "topics")
这篇关于多个注释中的 Django Count()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!