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 stackoverflow.com/questions/33775011/… and stackoverflow.com/questions/30752268/… — Risadinha
– Risadinha, Commented Aug 31, 2018 at 15:42
Possible duplicate of How to filter objects for count annotation in Django? — Risadinha
– Risadinha, Commented Aug 31, 2018 at 15:45

willeM_ Van Onsem · Accepted Answer · 2018-08-31 16:49:52Z

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, 2018 at 16:49

answered Aug 31, 2018 at 15:40

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Naroju Over a year ago

Thanks for answering again. I am getting import errors for Coalesce and V.

willeM_ Van Onsem Over a year ago

@Naroju: yes, I think there have been some renamings and movements in the Django library. It is probably now fixed.

Naroju Over a year ago

Small doubt. Can I annotate another related_name of another model for counting. Like chaining.. ?

willeM_ Van Onsem Over a year ago

Counting different related objects concurrently typically is inefficient, as wel as dangerous since the different joins can act as a multiplier.

Naroju Over a year ago

Yeah. Then how to do it?

|

Collectives™ on Stack Overflow

Django count related objects with condition

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related