Queryset ordering for nested objects

Question

I need to build a simple nested comment system in my project with REST API.

There are some requirements:

The page should contain the pagination of all comments, not just the parent ones. So, if there are 1 comment and 20 nested comments from other users, then on the first page should be displayed, like so:

-Comment 1

-- Sub comment 1

-- ...

-- Sub comment 9

On the second page:

-- Sub comment 10

-- ...

-- Sub comment 19

On the third page:

-- Sub comment 20

And it should be an optimal solution in terms of the least database access.

I was wondering about custom Queryset ordering, when after each comment follows nested comments, like so:

// Note, that ids follow in chaotic order
{
    id: 1,
    text: "Comment 1",
    parent: null
}, 
{
    id: 5,
    text: "Sub comment 1 for Comment 1",
    parent: 1
}, 
{
    id: 6,
    text: "Sub comment 2 for Comment 1",
    parent: 1
}, 
{
    id: 2,
    text: "Comment 2",
    parent: null
}, 
{
    id: 3,
    text: "Sub comment 1 for Comment 2",
    parent: 2
}, 
{
    id: 4,
    text: "Sub comment 2 for Comment 2",
    parent: 2
}, 

I've also thought and google about Django-mptt, but don't know how to implement it. Can you suggest to me, how to solve this problem and get JSON like in my example?

Answer 1

You might be able to annotate the queryset with a compound sort key from <parent>_<id> for children and just <id> for root comments and sort by that. In the example, the comments would have the sort keys 1, 1_5, 1_6, 2, 2_3, 2_4.

from django.db.models import CharField, Value as V
from django.db.models.functions import Concat

Comment.objects
    .annotate(sort_key=Case(
        When(parent__isnull=True, then=Concat('id', V('_0'), output_field=CharField())),
        When(parent__isnull=False, then=Concat('parent', V('_'), 'id', output_field=CharField())),
        output_field=CharField())
    .order_by('sort_key')

This of course is only a valid solution if the children can be sorted by id and need not be sorted by date or similar. If you need another sort order for the children you might need to annotate them explicitly with an index after sorting their order in the aggregation.

See:

• https://docs.djangoproject.com/en/3.0/topics/db/aggregation/
• https://docs.djangoproject.com/en/3.0/ref/models/conditional-expressions/#case
• https://docs.djangoproject.com/en/3.0/ref/models/database-functions/#concat

EDIT: Changed the sort key for parents to 1_0. Resolves the problem with the int vs char comparison. A cast would also work, of course.