如何在Django ORM中对QuerySet的每一项应用注释

Question

有很多类似的问题，但没有一个适合我。

让我们假设我有以下模型:

class Cafe(models.Model):
name = models.CharField(max_length=150)
def __str__(self):
return self.name

class Food(models.Model):
class SoldStatus(models.TextChoices):
SOLD_OUT = "SoldOut", "Sold out"
NOT_SOLD_OUT = "NotSoldOut", "Not sold out"
name = models.CharField(max_length=50)
cafe = models.ForeignKey(Cafe, related_name="foods", on_delete=models.CASCADE)
status = models.CharField(choices=SoldStatus.choices)
def __str__(self):
return self.name

在我的QuerySet中，我想检索所有具有以下字段的咖啡馆:"咖啡馆名称"、"食品总数"、"未售出食品总数"one_answers"未售出食品百分比">

是否有办法实现上述结果与Django ORM?

Answer 1

你可以这样尝试:

Cafe.objects.annotate(
total=Count('foods'), 
sold=Count('foods', filter=Q(foods__status=Food.SoldStatus.SOLD_OUT)),
unsold=sold=Count('foods', filter=Q(foods__status=Food.SoldStatus.NOT_SOLD_OUT))
)

更多信息请参见文档。

Answer 2

我通过这个查询做到了:

from django.db.models import Q, Case, When
ONE_HUNDRED = 100
Cafe.objects.annotate(
total=Count('food'), 
unsold=Count('food', filter=Q(food__status=Food.SoldStatus.NOT_SOLD_OUT)),
percent_of_unsold=Case(
When(total=0, then=None),
default=ONE_HUNDRED * Count('food', filter=Q(food__status=Food.SoldStatus.NOT_SOLD_OUT)) / Count('food'),
)
)

感谢@ruddra为我们指出了正确的方向。