How to calculate count of related many to many objects based on another queryset?

Question

class Zone(Model):
  ...

class Flight(Model):
  zones = ManyToManyField(Zone)

flights = Flight.objects.filter(...)
qs1 = Zone.objects.annotate(
   count=flights.filter(zones__pk=F('pk')).distinct().count(),  # this is not valid expression
)

Despite having F inside queryset with count() in annotation it still throw an error TypeError: QuerySet.annotate() received non-expression(s): 0. meaning that that queryset was executed in place.

Also doesn't work, but this time it just returns invalid value (always 1, always counting Zone single object instead of what inside filter):

qs1 = Zone.objects.annotate(
   count=Count('pk', filter=flights.filter(zones__pk=F('pk'))),  # with 'flight' instead of first 'pk' it also doesn't work
)

The count will immediately evaluate, so you can not use that. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Sep 23, 2019 at 19:36

willeM_ Van Onsem · Accepted Answer · 2019-09-23 19:41:51Z

2

A .count() is evaluated eagerly in Django, so Django will try to evaluate the flights.filter(zones__pk=F('pk')).distinct().count(), and succeed to do so, since F('pk') will count the number of fligts where there are zones that happen to have the same primary key as the primary key of the Flight. You will need to use OuterRef [Django-doc], and an .annotate(..) on the subquery.

But you make this too complex. You can simply annotate with:

from django.db.models import Q, Sum

Zone.objects.annotate(
    count=Count('flight', distinct=True, filter=Q(flight__…))
)

Here the filter=Q(flight__…) is the part of the filter of your flights. So if the Flights are filtered by a hypothetical active=True, you filter with:

Zone.objects.annotate(
    count=Count('flight', distinct=True, filter=Q(flight__active=True))
)

answered Sep 23, 2019 at 19:41

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Collectives™ on Stack Overflow

How to calculate count of related many to many objects based on another queryset?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related