在Django ORM中连接子查询表

Question

假设我有一个名为Book的模型和另一个名为Reaction的模型，该模型有一个指向Book的外键和一个名为outcome的字符串字段。数据库中有大量的book和reaction行。我想使用Django ORM查询数据库，生成以下SQL:

SELECT b.*, r.n_like, r.n_dislike
FROM book b
JOIN (
SELECT
book_id,
COALESCE(SUM(CASE WHEN outcome='like' THEN 1 ELSE 0 END), 0) n_like,
COALESCE(SUM(CASE WHEN outcome='dislike' THEN 1 ELSE 0 END), 0) n_dislike
FROM reaction
GROUP BY book_id) r ON b.id=r.book_id;

我该怎么做呢?

请注意，此查询只返回至少有一个反应的图书及其相应的like和dislike反应的数量。

我知道如何在Django中使用Case,When,Sum和Coalesce。我也知道如何编写一个查询，在SELECT子句中生成这些注释(我尝试过这种查询，它很慢)。我的问题仅仅是关于加入和选择一个子查询与Django模型。子查询是这样的:

reactions_query = Subquery(
Reaction.objects
.values('book_id')
.annotate(n_like=Coalesce(Sum(Case(When(outcome='like', then=1), default=0, output_field=IntegerField())), 0))
.annotate(n_dislike=Coalesce(Sum(Case(When(outcome='dislike', then=1), default=0, output_field=IntegerField())), 0))
.values('book_id', 'n_like', 'n_dislike')
)
Book.objects.filter(...) <-- how?

编辑这些不是我的实物模型。我简化了它们。总之，它们是这样的:

class Book(models.Model):
name = models.CharField(max_length=100)
pub_date = models.CharField(max_length=100)
about = models.TextField()

class Reaction(models.Model):
LIKE = 'like'
DISLIKE = 'dislike'
OUTCOME_CHOICES = ((LIKE, ':)'), (DISLIKE, ':('))
book = models.ForeignKey(Book, on_delete=models.CASCADE)
outcome = models.CharField(max_length=20, choices=OUTCOME_OPTIONS, null=True)

Answer 1

您可以使用:

from django.db.models importCount, Q, Value
from django.db.models.functions import Coalesce
Book.objects.filter(
reaction__isnull=False
).annotate(
n_like=Coalesce(Count('reaction', filter=Q(outcome='like')), Value(0)),
n_dislike=Coalesce(Count('reaction', filter=Q(outcome='dislike')), Value(0))
)

在没有任何反应的情况下，.filter(reaction__isnull=False)将阻止Books的加入。这将创建一个具有INNER JOIN的查询。