Django - 使用多个计数进行注释

Question

我有一个模型叫 Post其中有两个字段 upvotes和 downvotes .现在，upvotes , downvotes是 ManyToManyField到 Profile .这是模型:

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')

所以，我想获取所有帖子，使它们按以下顺序排列

total(upvotes) - total(downvotes)

所以我使用了这个查询:

Post.objects.annotate(
    total_votes=Count('upvotes')-Count('downvotes')
).order_by('total_votes')

此查询的问题是 total_votes结果总是 零 .

以下查询将解释这种情况:

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}]>

似乎两者都是 up和 down具有相同的值(实际上是 up 的值)。我该如何解决这个问题？

我试过这个:

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}]>

但即使这样也会产生相同的输出。

Answer 1

尝试使用 dictinct 争论:

Post.objects.annotate(
    total_votes=Count('upvotes', distinct=True)-Count('downvotes', distinct=True)
).order_by('total_votes')

从文档:

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries. For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help.