Django Aggregation, sum of counts

Question

I have 3 models: Forum, Thread, Post and I'm creating a view to display the list of forums. But I also want to display the number of threads and the number of posts for each forum.

I then have to:

• count the number of posts for each thread
• sum the counts of posts of each thread for each forum

I found something similar here: Django: Sum the count of a sub sub foreign object but the answer is not working for me.

from django.shortcuts import render
from django.template import Context
from django.contrib.auth.decorators import login_required
from django.db.models import Count

from chinwag.models import Forum, Thread, Post

@login_required
def forums(request):
    forums = Forum.objects.annotate(num_posts=Count('threads__posts')).all(
            ).select_related('threads__last_post')
    return render(request, 'chinwag/forums.html', Context({
        'forums': forums,
    }))

Is it possible to do it in 1 SQL query? How?

Answer 1

If I understood correctly, you can use

Forum.objects.annotate(num_threads=Count('threads__id'),
                       num_posts=Count('threads__posts__id'))

This makes two annotations in one database hit.

The first counts all threads on the forum, the second counts all posts on all threads of the forum (assuming a thread as a ForeignKey with Form, and a post a ForeignKey with threads.

The exact naming in 'threads__posts__id' depends on the name of the foreign key, but Django will spit an error with suggestions if they are not correct.

P.S. you can remove .all(), it is not doing anything.