Django的计数()的多个注释 [英] 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")
Yeilds:
Yeilds:
[('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?
推荐答案
我觉得 伯爵('主题',分明= 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的计数()的多个注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!