How to list by most used foreign keys in django

Question

I have 2 models: Post and Comment as you can see below

class Post(models.Model):
id = models.UUIDField( 
     primary_key = True, 
     default = uuid.uuid4, 
     editable = False)
user = models.ForeignKey(CustomUser, on_delete=models.CASCADE, )
date = models.DateTimeField(default=datetime.now)
title = models.CharField(max_length=100,)
body = models.CharField(max_length=1000)

class Comment(models.Model):
    id = models.UUIDField( 
         primary_key = True, 
         default = uuid.uuid4, 
         editable = False)
    post = models.ForeignKey(Post, on_delete=models.CASCADE, default=None, null=True, blank=True)
    user = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    date = models.DateTimeField(default=datetime.now)
    body = models.CharField(max_length=1000)

I want to get the most commented posts last week so how would I list the Posts with most comments. Thanks!

Answer 1

You use an alias and you order:

from datetime import timedelta

from django.db.models import Count
from django.utils.timezone import now

Post.objects.filter(date__gte=now() - timedelta(days=7)).alias(
    n_comments=Count('comment')
).order_by('-n_comments')

If you want to fetch the number of comments along with it, you can use .annotate(…) ^[Django-doc] over .alias(…) ^[Django-doc]:

from datetime import timedelta

from django.db.models import Count
from django.utils.timezone import now

Post.objects.filter(date__gte=now() - timedelta(days=7)).annotate(
    n_comments=Count('comment')
).order_by('-n_comments')

The Post objects arising from this will have an extra attribute .n_comments, with the number of Comments for that `Post.