Django annotate count of foreign key's foreign key

Question

I have three main models:

Class Client(models.Model):
   stuff

Class Property(models.Model):
    client = models.ForeignKey(Client)
    branch = models.ForeignKey(Branch, blank=True, null=True)

Class Branch(models.Model):
    name = models.CharField(max_length=200)

My final goal is basically to try and deduce a client's most involved branch based on the properties it is connected to.

I'm sure the best way to do this is using Django's aggregate using annotate, but I'm not sure how exactly to do this. I might have to use extra instead.

I know I'll need to something like Client.objects.annotate(branch_count=Count('property__branch')), but I need to somehow add filtering for properties attached to each branch.

The other way perhaps is to make use of Collections.Counter

d = {}
for c in Client.objects.all():
    d[c] = Counter(c.property_set.values_list('branch', flat=True))

This would build a dictionary of counts, then I'll use it to look up the branch with the highest count for any one client.

Answer 1

In the Property Model give a related_name attribute to client(lets say "client_ref")

Client.objects.annotate(branch_count=Count(
             'client_ref__branch')).filter(branch__name="some_name")
             .values('branch')

where name can be replaced with some other filter property