Django annotate count of subquery items - ValueError: This queryset contains a reference to an outer-CodePudding

I have an Agent, Client and Car models.

In Client model: agent = ForeignKey('Agent'...)

In Car model: client = ForeignKey('Client'...)

I want to annotate (on an Agent QuerySet) a total number of active cars of all clients of an agent.

So if the agent Bob has clients Alice and Peter, Alice has 3 active cars and Peter has 2 active cars, the active_cars variable will be 5.

I tried:

Agent.objects.annotate(
    active_cars=Subquery(
        Car.objects.all().active().filter(
            client__agent=OuterRef('pk')
        ).count()
    )
)

which raises:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

Do you know how to do that?

CodePudding user response：

.count() does not return a queryset: it executes the queryset. That is why Django complains.

I have no idea what .active() does, but if just ignoring this, you can have

Agent.objects.annotate(
    active_cars=Count('client__car__id')
)

No need of a subquery.

CodePudding user response：

Assuming your Car model has a boolean field named "active" and that is what you want to filter the count by, you can pass a filter argument to Count to filter the rows that are counted

from django.db.models import Count, Q

Agent.objects.annotate(
    active_cars=Count('client__car', filter=Q(client__car__active=True))
)