Django Count()在多个注释中 [英] Django Count() in multiple annotations
问题描述
class User(models.Model):
username = models .CharField(max_length = 25)
...
class主题(models.Model):
user = models.ForeignKey(User)
...
class Post(models.Model):
user = models.ForeignKey(User)
...
现在说我想看看每个用户的用户数量有多少(例如他们的用户名以ab开头)。
所以如果我对每个帖子和主题做一个查询:
User.objects.filter(username_startswith =ab)
.annotate(posts = Count('post'))
.values_list(username,posts)
Yeilds:
[('abe',5) ('abby',12),...]
和
User.objects.filter(username_startswith =ab)
.annotate(topics = Count('topic') )
.values_list(username,topics)
收益:
[('abe',2),('abby',6),...]
HOWEVER ,当我尝试注释两个以获取一个列表时,我会感到奇怪:
User.objects.filter(username_startswith =ab)
.annotate(posts = Count('post'))
.annotate(topic = Count('topic'))
.values_list(username,posts,topics)
收益:
[('abe',10,10),(' abby',72,72),...]
为什么主题和帖子相加在一起?我希望这样:
[('abe',5,2),('abby',12,6) ..]
获得正确列表的最佳方法是什么?
我认为 Count('topics',distinct = True)
应该做正确的事情。这将使用 COUNT(DISTINCT topic.id)
而不是 COUNT(topic.id)
以避免重复。 (
User.objects.filter(
username_startswith =ab)。annotate(
posts = Count (
topic = Count('topic',distinct = True))values_list(
username,posts,topics)
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)
...
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:
[('abe', 5),('abby', 12),...]
and
User.objects.filter(username_startswith="ab")
.annotate(topics=Count('topic'))
.values_list("username","topics")
Yields:
[('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")
Yields:
[('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?
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屋!