A Django query to find top 3 posts with maximum comments

Question

how can i find the top 3 blogpost with the maximum comments
here are my models:

 class BlogPost(models.Model):
    title = models.CharField(max_length=250)
    body = models.TextField()

class Comments(models.Model):
    post = models.ForeignKey('BlogPost', null=False, on_delete=models.CASCADE)
    comment = models.TextField()

Answer 1

This will give you the top 3 posts along with number of comments (dcount) grouped by posts.

from django.db.models import Count

Comments.objects.values('post').annotate(dcount=Count('id')).order_by('-dcount')[:3]

Answer 2

I would strongly advise against using .values(…) ^[Django-doc], since then you no longer work with Post objects, but with dictionaries. You can use:

from django.db.models import Count

BlogPost.objects.alias(
    num_comments=Count('Comments')
).order_by('-num_comments')[:3]

---

Note: normally a Django model is given a singular name, so Comment instead of Comments.

---

Note: Specifying null=False ^[Django-doc] is not necessary: fields are by default not NULLable.

Answer 3

from django.db.models import Count

BlogPost.post_set.values('comment').annotate(comment_count=Count('comment'))

Try this and check if will return something.

Edit 1:

Try this instead

posts = BlogPost.objects.all()
relation = []
for post in posts:
    post_number = len(list(post.post_set.all()))
    relation.append({'post': post, 'post_number': post_number})