Django annotate with related_name from A->C where A->B and B->C where '->' is reverse relationship

I searched a lot on internet but could not find a similar question.

I have 3 models : Domain, Topic, Post Each Domain can have many Topics and each Topic can have many Posts.

Topic has a foreign key to Domain and Post has a foreign key to Topic.

So I can include count of Posts in Topic by annotate(Count('posts')) and I can include count of Topics in Board by annotate(Count('topics'))

Is there any way to include count of Posts in Board by annotate ?

Solution

Yes, you can use double underscores (__) to look through a relation:

from django.db.models import Count

Domain.objects.annotate(
    num_posts=Count('topics__posts')
)

if you combine this with counting the number of Topics, you will need to add distinct=True to the Count of the Topics, since this will make two JOINs, and the JOIN on Posts will act as a "multiplier":

from django.db.models import Count

Domain.objects.annotate(
    num_posts=Count('topics__posts'),
    num_topics=Count('topics', distinct=True)
)

This will result in a query like:

SELECT domain.*
       COUNT(post.id) AS num_posts
       COUNT(DISTINCT topic.id) AS num_topics
FROM domain
LEFT OUTER JOIN topic ON topic.domain_id = domain.id
LEFT OUTER JOIN post ON post.topic_id = topic.id
GROUP BY domain.id

If you would have omitted the distinct=True, the same topic would be counted multiple times (exactly the same number of times as the number of posts related to that topic), and thus num_topics should then be the same as num_posts. By using DISTINCT we count the number of unique topics in each group.