CODEWITHSUNDEEP
pythonsqldatabasedjangoaggregate
choucavalier
choucavalier

Reputation: 2770

Django Aggregation, sum of counts

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:

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?

Upvotes: 5

Views: 3092

Answers (1)

Jorge Leitao
Jorge Leitao

Reputation: 20123

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.

Upvotes: 7

Related Questions