Django count related objects with condition

Question

I want to filter the results before counting. In below code, I am counting the total number of workers for each Ticket Counter.

class TicketCounterList(ListAPIView):
    queryset = TicketCounter.objects.filter(ticket_counter_is_deleted=False)
            .annotate(num_workers=Count('workers'))
    serializer_class = TicketCounterSerializer

workers in the above code is the related_name from another model ( WorkerToTicketCounter model). What I want to do is, I want to be able to filter workers with a condition is_deleted= True rather than counting all workers. Is it possible? I am using Django 1.11.13

Is there anything like below

queryset = TicketCounter.objects.filter(ticket_counter_is_deleted=False)
            .annotate(num_workers=Count('workers',filter="xxxx"))

get it? I want to filter the workers.

EDIT: My Models:

class TicketCounter(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    ticket_counter_name = models.CharField(max_length=100, default="")
    ticket_counter_description = models.CharField(max_length=1500, default="")
    ticket_counter_address = models.CharField(max_length=1500, default="")


class WorkerToTicketCounter(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    ticket_counter = models.ForeignKey(TicketCounter,related_name="workers")
    worker = models.ForeignKey(User,related_name='worker_for_ticket_counter')

My Serializer:

class TicketCounterSerializer(serializers.ModelSerializer):
    num_workers = serializers.IntegerField()
    class Meta:
        model = TicketCounter
        fields = (
            'ticket_counter_name',
            'ticket_counter_description',
            'ticket_counter_address',
            'num_workers',
        )

Similar https://stackoverflow.com/questions/33775011/how-to-annotate-count-with-a-condition-in-a-django-queryset and https://stackoverflow.com/questions/30752268/how-to-filter-objects-for-count-annotation-in-django — Risadinha, Aug 31 '18 at 15:42
Possible duplicate of [How to filter objects for count annotation in Django?](https://stackoverflow.com/questions/30752268/how-to-filter-objects-for-count-annotation-in-django) — Risadinha, Aug 31 '18 at 15:45

Willem Van Onsem · Accepted Answer · 2018-08-31T16:49:52.030

2

Since django-2.0, you can do filtering in the Count, but this will not help us here.

What we however can do is sum up the workers__is_deleted, or its negation. For example if we want to return the number of workers that are not deleted:

from django.db.models import F, IntegerField, Sum, Value
from django.db.models.functions import Coalesce

class TicketCounterList(ListAPIView):
    queryset = TicketCounter.objects.filter(
        ticket_counter_is_deleted=False
    ).annotate(
        num_workers=Cast(
            Coalesce(Sum(Value(1) - F('workers__is_deleted')), Value(0)),
            IntegerField()
        )
    )
    serializer_class = TicketCounterSerializer

Or if you want to count the deleted workers:

from django.db.models import F, IntegerField, Sum, Value
from django.db.models.functions import Coalesce

class TicketCounterList(ListAPIView):
    queryset = TicketCounter.objects.filter(
        ticket_counter_is_deleted=False
    ).annotate(
        num_workers=Cast(
            Coalesce(Sum(F('workers__is_deleted')), Value(0)),
            IntegerField()
        )
    )
    serializer_class = TicketCounterSerializer

edited Aug 31 '18 at 16:49

answered Aug 31 '18 at 15:40

Willem Van Onsem

443,496
30
428
555

Thanks for answering again. I am getting import errors for `Coalesce` and `V`. – Naroju Aug 31 '18 at 16:43
@Naroju: yes, I think there have been some renamings and movements in the Django library. It is probably now fixed. – Willem Van Onsem Aug 31 '18 at 16:50
Small doubt. Can I annotate another `related_name` of another model for counting. Like chaining.. ? – Naroju Aug 31 '18 at 16:54
Counting different related objects concurrently typically is inefficient, as wel as dangerous since the different joins can act as a *multiplier*. – Willem Van Onsem Aug 31 '18 at 16:55
Yeah. Then how to do it? – Naroju Aug 31 '18 at 16:56
@Naroju: you could technically speaking do this with subqueries. But I think by upgrading to Django-2.0, you save yourself a lot of trouble, since it has a filter, and then this "hacky" solution is not necessary. – Willem Van Onsem Aug 31 '18 at 16:58
Thanks again for answering second time :) – Naroju Aug 31 '18 at 17:02

Django count related objects with condition

1 Answers1