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?