如何在Django QuerySet中添加其他列

问题描述

我有一个带有Books的QuerySet，我想在每个Book结果中添加一个 score 字段。

I have a QuerySet with Books and I would like to add a score field to every Book result.

qs = Book.objects.all()

原始SQL我会这样写：

In raw SQL I would write:

SELECT
    *,
    (
        (SELECT COUNT(*) FROM votes WHERE value=1 AND book=b.id) - 
        (SELECT COUNT(*) FROM votes WHERE value=-1 AND book=b.id)
    ) AS score
FROM
    Book b;

如何在Django中实现？我尝试了 annotate（），但似乎不适合这种东西。

How can I achieve it in Django? I tried annotate(), but it seems it's not meant for this kind of stuff.

推荐答案

如果投票的可能值只有1和-1，则可以使用提到的： Book.objects.annotate（score = Sum（'votes__value'））。

If votes possible values are only 1 and -1 you can just sum them using mentioned annotate: Book.objects.annotate(score=Sum('votes__value')).

如果还有更多可能的值，则可以通过添加 .filter（votes__value__range =（1,1））

If there is more possible values you can filter annotation by adding .filter(votes__value__range=(1,1)) to the above query.

如果要更复杂，则必须将 extra 与选择。

If it's more complex you would have to use extra with select.

这篇关于如何在Django QuerySet中添加其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！