Django: nesting related columns in a json array

Objective

I want to dynamically structure my menu links based on the site pages that I create in the database and its relationship with other pages so that I can generate the appropriate main menu links and dropdowns based on those relationships, formatting the query result as JSONField() using django-rest-framework (DRF).

I generate and fetch the results via a DRF API class.

This is the kind of navigation that I want:

enter image description here

The level of dropdown can be indefinite, depends on whether I define it in the database.

Expected Result

Based on the desired navigation above, this is how my API should return the result:

[{
    "title": "Home",
    "path": "/",
    "depth": 1
    "child": null,
}, {
    "title": "Government",
    "path": "/government/",
    "child": [{
        "title": "Provincial Profile",
        "path": "/government/provincial-profile/",
        "depth": 2,
        "child": null
    }, {
        "title": "Governor",
        "path": "/government/governor/",
        "depth": 2,
        "child": null
    }, {
        "title": "Offices",
        "path": "/government/offices/",
        "depth": 2,
        "child": [{
            "path": "/government/offices/provincial-governors-office",
            "title": "Provincial Governor's Office",
            "depth": 3,
            "child": null
        }]
    }]
}]

Model Structure

I have 2 models involved in generating the main menu and dropdowns

  1. CmsPage - defines the page property of the page i.e. title of page, the link it navigates to, etc.
  2. CmsPageTree - defines the hierarchy of the pages (which are menu links, which are dropdowns and the dropdown level). The depth field is what shall determine the level of the link

CmsPageModel.py

class CmsPage(models.Model):
    title = models.CharField(max_length=60)
    page_title = models.CharField(max_length=60)
    meta_title = models.CharField(max_length=60, blank=True, null=True)
    meta_description = models.CharField(max_length=160, blank=True, null=True)
    path = models.CharField(max_length=255, blank=True, null=True)
    approved = models.BooleanField(default=False)
    published = models.BooleanField(default=False)
    is_navigation = models.BooleanField(default=False)
    approved_by = models.ForeignKey(
        User, on_delete=models.DO_NOTHING, related_name="cmspage_approved_by")
    created_by = models.ForeignKey(
        User, on_delete=models.DO_NOTHING, related_name="cmspage_created_by")
    updated_by = models.ForeignKey(
        User, on_delete=models.DO_NOTHING, related_name="cmspage_updated_by", blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(blank=True, null=True)

CmsPageTreeModel.py

class CmsPageTree(models.Model):
    parent = models.ForeignKey(
        CmsPage, on_delete=models.CASCADE, related_name='cmspagetree_parent')
    child = models.ForeignKey(
        CmsPage, on_delete=models.CASCADE, related_name='cmspagetree_child')
    depth = models.IntegerField()

Serializer Structure

class CmsPageSerializer(serializers.Serializer):
    title = serializers.CharField(read_only=True)
    page_title = serializers.CharField(read_only=True)
    meta_title = serializers.CharField(read_only=True)
    meta_description = serializers.CharField(read_only=True)
    path = serializers.CharField(read_only=True)
    approved = serializers.CharField(read_only=True)
    published = serializers.CharField(read_only=True)
    is_navigation = serializers.CharField(read_only=True)

    class Meta:
        model = CmsPage
        fields = [
            'title',
            'page_title',
            'meta_title',
            'meta_description',
            'path',
            'approved',
            'published',
            'is_navigation'
        ]

class CmsPageTreeSerializer(serializers.Serializer):
    title = serializers.CharField(read_only=True)
    path = serializers.CharField(read_only=True)
    child = serializers.JSONField()
    depth = serializers.IntegerField(read_only=True)

    class Meta:
        model = CmsPageTree
        fields = [
            'title',
            'path',
            'child',
            'depth'
        ]

API Structure

Using the serializer structure above, I write my API class like so:

class CmsPageTree_API(generics.ListCreateAPIView):
    try:
        queryset = CmsPageTree.objects.all()
        serializer_class = CmsPageTreeSerializer
        permission_classes = [IsAuthenticatedOrReadOnly]
        authentication_classes = (TokenAuthentication,)
    except Exception as e:
        traceback.print_exc()

    def get_queryset(self):
        x = CmsPage.objects.raw("""
            select
                distinct cp.id,
                cp.title,
                cp.path as path,
                pcpt.depth as depth,
                jsonb_agg(jsonb_build_object(
                'title',ccp.title,
                'path',ccp.path
                )) as child
            from web_app.cms_page cp
            join web_app.cms_page_tree pcpt on pcpt.parent_id = cp.id
            left join web_app.cms_page ccp on ccp.id = pcpt.child_id 
            group by(
                cp.id,
                pcpt.depth
            );
        """)
        return x

Current Result

However, my API class does not seem to give me the desired result. Instead, it gives me this:

[{
    "title": "Home",
    "path": "/",
    "depth": 1,
    "child": [{
        "path": null,
        "depth": 1,
        "title": null
    }]
}, {
    "title": "Offices",
    "path": "/government/offices/",
    "depth": 3,
    "child": [{
        "path": "/government/offices/provincial-governors-office",
        "depth": 3,
        "title": "Provincial Governor's Office"
    }]
}, {
    "title": "Government",
    "path": "/government/",
    "depth": 2,
    "child": [{
        "path": "/government/provincial-profile/",
        "depth": 2,
        "title": "Provincial Profile"
    }, {
        "path": "/government/governor/",
        "depth": 2,
        "title": "Governor"
    }, {
        "path": "/government/offices/",
        "depth": 2,
        "title": "Offices"
    }]
}]

Tools used:

  • Django
  • Django Rest Framework
  • PostgreSQL