How to apply annotation to each item of QuerySet with Django ORM

Question

There are a lot questions similar to this one but none of them worked for me.

Let's assume that I have the following models:

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

In my QuerySet, I want to retrieve all cafes with the following fields in each: 'cafe name', 'total number of foods', 'total number of not sold foods', and 'percentage of not sold foods'

Is there any way to achieve the above result with Django ORM?

Answer 1

I managed to do it by this query:

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

Thanks to @ruddra for pointing to the right direction.

Answer 2

You can try like this:

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

More information can be found in documentation.