[SOLVED] Django: group items by foreign key

Django: group items by foreign key

I have comment and post models as below

class Comment(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE)
    parent = models.ForeignKey('self', unique=False, blank=True, null=True, on_delete=models.CASCADE)


class Post(models.Model):
    title = models.CharField(max_length=120, null=False, blank=False)

I use this raw sql query to get all the comments which has post_id 11 and group the results.

    all_comments = Comment.objects.raw('SELECT * FROM comments_comment where post_id=11 order by coalesce(parent_id, id), (case when parent_id is null then 1 else 2 end ), created_at')

This query works perfectly okay for me. However, I would like to do the query in Django way instead of using raw query. What would be the Django equivalent of this?

Solution

We can .annotate(…) ^[Django-doc] with the criteria on which we want to order by. We can for example refer to Coalesce(…) with pid, and the case when … then … else … end with type:

from django.db.models import Case, IntegerField, Value, When
from django.db.models import Coalesce

Comment.objects.filter(
    post_id=11
).annotate(
    pid=Coalesce('parent_id', 'id'),
    type=Case(
        When(parent_id__isnull=True, then=Value(1))
        default=Value(2),
        output_field=IntegerField()
    )
).order_by(
    'pid', 'type', 'created_at'
)