Serializing RAW SQL query Django

Question

Hope you doing well. I need to serialize my RAW SQL query:

SELECT nn.*,nm.*FROM notifications_newsletter nn LEFT JOIN notifications_message nm ON nn.id=nm.newsletter_id_id ORDER by nm.status DESC

models.py

from django.db import models


class Newsletter(models.Model):
    start_datetime = models.DateTimeField()
    text = models.TextField(blank=True)
    filter = models.ForeignKey('Filter', null=True, on_delete=models.SET_NULL)
    end_datetime = models.DateTimeField()


class Message(models.Model):
    send_datetime = models.DateTimeField('%Y-%m-%d %H:%M:%S', auto_now=True)
    status = models.BooleanField(default=False)
    newsletter_id = models.ForeignKey('Newsletter', on_delete=models.CASCADE)
    client_id = models.ForeignKey('Client', on_delete=models.CASCADE)

views.py

from django.core.serializers import serialize
from django.http import HttpResponse

from .models import Newsletter


def some_view(request):
    sql = 'SELECT nn.*,nm.*FROM notifications_newsletter nn ' \
          'LEFT JOIN notifications_message nm ' \
          'ON nn.id=nm.newsletter_id_id ORDER by nm.status DESC'
    qs = Newsletter.objects.raw(sql)
    qs_json = serialize('json', qs)
    return HttpResponse(qs_json, content_type='application/json')

If I do it with serializers.serialize() all joined data (message table) doesn't exist in response. But if print(qs.columns) the columns send_datetime, status, etc. will be printed.

Response:

[
    {
        "model": "notifications.newsletter",
        "pk": 42,
        "fields": {
            "start_datetime": "2022-01-21T21:56:09Z",
            "text": "This is test message for 900 operator code.",
            "filter": 1,
            "end_datetime": "2022-01-21T18:00:00Z"
        }
    },
] 

I need something like:

[
    {
        "model": "notifications.newsletter",
        "pk": 43,
        "fields": {
            "start_datetime": "2022-01-21T22:03:26Z",
            "text": "This is test message for 904 operator code.",
            "filter": 2,
            "end_datetime": "2022-01-21T18:00:00Z",
            "messages": [
                {
                    "send_datetime": "2022-01-21T22:03:26Z",
                    "status": 0,
                    "newsletter_id": 43,
                    "client_id": 1
                },
            ]
        }
    },
]

Is it possible to serialize it normally?

Answer 1

You can use nested model serializers from Django REST Framework for your purposes like this:

from rest_framework import serializers

class MessageSerializer(serializers.ModelSerializer):
    class Meta:
        model = Message
        fields = "__all__"

class NewsletterSerializer(serializers.ModelSerializer):
    messages = MessageSerializer(many=True)

    class Meta:
        model = Newsletter
        fields = "__all__"

And use NewsletterSerializer in your view:

from django.core.serializers import serialize
from django.http import HttpResponse

from .models import Newsletter


def some_view(request):
    sql = 'SELECT nn.*,nm.*FROM notifications_newsletter nn ' \
          'LEFT JOIN notifications_message nm ' \
          'ON nn.id=nm.newsletter_id_id ORDER by nm.status DESC'
    qs = Newsletter.objects.raw(sql)
    qs_json = NewsletterSerializer(qs, many=True).data
    return HttpResponse(qs_json, content_type='application/json')

Also you have to do some other changes in your code:

1. You have to rename your fields newsletter_id, client_id to newsletter, client respectively. You can read more about it here.
2. You have to specify related_name in your newsletter foreign key in the Message model. Set it to messages like this:

newsletter = models.ForeignKey('Newsletter', on_delete=models.CASCADE, related_name='messages')

3. You have to rename your filter field because it is the reserved keyword.