django - Django - annotate with multiple Count
问题描述
I have a model called Post
which has two fields upvotes
and downvotes
. Now, upvotes
, downvotes
are ManyToManyField
to a Profile
. This is the model:
class Post(models.Model):
profile = models.ForeignKey(Profile, on_delete=models.CASCADE)
title = models.CharField(max_length=300)
content = models.CharField(max_length=1000)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
subreddit = models.ForeignKey(Subreddit, on_delete=models.CASCADE)
upvotes = models.ManyToManyField(Profile, blank=True, related_name='upvoted_posts')
downvotes = models.ManyToManyField(Profile, blank=True, related_name='downvoted_posts')
So, I want to fetch all the posts such that they are in the order of
total(upvotes) - total(downvotes)
So I have used this query:
Post.objects.annotate(
total_votes=Count('upvotes')-Count('downvotes')
).order_by('total_votes')
The problem with this query is the total_votes
is always turning out to be zero.
The below queries will explain the situation:
In [5]: Post.objects.annotate(up=Count('upvotes')).values('up')
Out[5]: <QuerySet [{'up': 1}, {'up': 3}, {'up': 2}]>
In [6]: Post.objects.annotate(down=Count('downvotes')).values('down')
Out[6]: <QuerySet [{'down': 1}, {'down': 1}, {'down': 1}]>
In [10]: Post.objects.annotate(up=Count('upvotes'), down=Count('downvotes'), total=Count('upvotes')-Count('downvotes')).values('up', 'down', 'total')
Out[10]: <QuerySet [{'up': 1, 'down': 1, 'total': 0}, {'up': 3, 'down': 3, 'total': 0}, {'up': 2, 'down': 2, 'total': 0}]>
Seems like both up
and down
are having the same value(which is actually the value of up
). How can I solve this?
I have tried this:
In [9]: Post.objects.annotate(up=Count('upvotes')).annotate(down=Count('downvotes')).values('up', 'down')
Out[9]: <QuerySet [{'up': 1, 'down': 1}, {'up': 3, 'down': 3}, {'up': 2, 'down': 2}]>
but even this gives the same output.
解决方案
尝试使用dictinct
参数:
Post.objects.annotate(
total_votes=Count('upvotes', distinct=True)-Count('downvotes', distinct=True)
).order_by('total_votes')
从文档:
将多个聚合与 annotate() 组合会产生错误的结果,因为使用的是连接而不是子查询。对于大多数聚合,没有办法避免这个问题,但是,Count 聚合有一个不同的参数可能会有所帮助。
推荐阅读
- java - 使用 AWS CDK 实施生命周期后无法删除 S3 存储桶
- php - 向电报发送值
- git - .gitignore 文件中的单 /* 和双 /** 尾随星号有什么区别?
- html - CSS:仅用于选择器
- 有过的父母
- 孩子们?
- 有过的父母
- android-studio - Android Studio & Sonoff (Domotic & Automation)
- google-apps-script - 更新表单,跳过一列又一列,同时拒绝一起更新文本项
- amazon-web-services - Beanstalk 中网络的编辑按钮丢失
- flutter - Flutter:帮助创建检测用户笔迹并将其转换为文本的应用程序
- reactjs - 反应国家地区选择
- postgresql - 基于条件返回唯一值的 PostgreSQL 查询