Django. How to annotate a object count from a related model

Question

lets say I have a model Comments and another model Answers. I want to query all comments but also include in the query the number of answers each comment has. I thought annotate() would be useful in this case, but I just don't know how to write it down. The docs write that:

New in Django 1.8: Previous versions of Django only allowed aggregate functions to be used as annotations. It is now possible to annotate a model with all kinds of expressions.

So. This is an example of my models:

class Comment(models.Model):
   ...

class Answer(models.Model):
   comment = models.ForeignKey(Comment, related_name='answers')

And this is an example query:

queryset = Comment.objects.all().annotate(...?)

I'm not sure how to annotate the Count of answers each comment has. That is, how many answers point to each comment on the FK field comment. Is it even possible? is there a better way? Is it better to just write a method on the manager?

user764357 · Accepted Answer · 2015-10-26 22:07:03Z

22

You need to use a Count aggregation:

from django.db.models import Count
comments = Comments.objects.annotate(num_answers=Count('answers'))

answered Oct 26, 2015 at 22:07

user764357

Sign up to request clarification or add additional context in comments.

1 Comment

Alejandro Veintimilla Over a year ago

Thanks for the answer. Extra note: I had to add a related_name to my FK in order to make it work. Just in case someone finds this useful.

Sardar Faisal · Accepted Answer · 2024-02-02 14:29:00Z

2

A better solution would be:

from django.db.models import Prefetch, QuerySet, Count

def get_queryset(self) -> QuerySet[Customer]:
        return (
            Comments.objects
            .prefetch_related(
                    Prefetch('answers', queryset=Answer.objects.only('id')),
                )
            .annotate(
                answers_count=Count('answers', distinct=True),
            )
            .order_by("-id")
            )

Please adjust according to specific scenario.

answered Feb 2, 2024 at 14:29

Sardar Faisal

7218 silver badges21 bronze badges

Collectives™ on Stack Overflow

Django. How to annotate a object count from a related model

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related