CODEWITHSUNDEEP
pythondjangodjango-queryset
Alejandro Veintimilla
Alejandro Veintimilla

Reputation: 11523

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

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?

Upvotes: 9

Views: 8615

Answers (2)

Sardar Faisal
Sardar Faisal

Reputation: 673

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.

Upvotes: 0

user764357
user764357

Reputation:

You need to use a Count aggregation:

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

Upvotes: 22

Related Questions