1

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 ?

Yusuf
  • 321
  • 1
  • 7
  • 19

1 Answers1

1

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.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • I understand the first part and my query is resolved...however can you explain what you meant in second part (two JOINs)...? – Yusuf Apr 18 '20 at 13:51
  • 1
    @Yusuf: it will query with `SELECT COUNT(*) FROM domain LEFT OUTER JOIN topic ON topic.domain_id = domain.id LEFT OUT JOIN post ON post.topic_id = topic.id GROUP BY domain.id`. So if we count `topic`s, it will not count *unique* topics, but for each post count the same topic (so multiple times). To prevent that, we count unique topics. – Willem Van Onsem Apr 18 '20 at 13:54
  • so ```distinct=True``` should come for num_posts, right ? – Yusuf Apr 18 '20 at 13:59
  • 1
    @Ysuf: it does not matter in what order you define the `count`s, but you should count *distinct* topics, since that is where the "duplication" will happen. – Willem Van Onsem Apr 18 '20 at 14:00
  • The SQL query you mentioned above will be generated for num_posts and for num_topics it will be like : ```select count(*) from domain inner join topic on domain.id=topic.domain.id``` – Yusuf Apr 18 '20 at 14:05
  • 1
    @Yusuf: but it will not make two separate count queries if you annotate both in the same `.annotate(..)` clause. – Willem Van Onsem Apr 18 '20 at 14:06
  • Oh! thanks ..now I got it..why duplication will happen – Yusuf Apr 18 '20 at 14:07
  • I have another query- should i create another question or you can answer here only - If I want to annotate new column for ```domain``` like ```last_updated_post``` where it will store the last updated ```post```, how to write ```annotate``` statement for this - MW ```post``` has one field ```updated_on``` (DateTimeField) - Something like ```domain.objects.annotate(postt='topics__posts'.order_by('-updated_on').first())``` – Yusuf Apr 18 '20 at 14:12
  • 1
    @Yusuf: you can add as extra annotation `last_updated_post=Max('topics__posts__updated_on')`. – Willem Van Onsem Apr 18 '20 at 14:14
  • 1
    @Yusuf: for the latest post itself, however that will require extra joining, and might be better done in two steps. Probably worth writing an new question. – Willem Van Onsem Apr 18 '20 at 14:15
  • I am writing another question! – Yusuf Apr 18 '20 at 14:15
  • The link to new question : https://stackoverflow.com/questions/61291587/django-annotate-with-field-of-related-name-of-related-name – Yusuf Apr 18 '20 at 15:24