Django如何序列化原始的SQL查询？

Question

Django如何序列化原始的SQL查询？

3

希望您一切顺利。我需要对我的原始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

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')

如果我使用serializers.serialize()进行操作，那么响应中所有连接数据（消息表）都不存在。但是，如果打印print(qs.columns)，则会打印列send_datetime, status, etc.。

响应：

[
    {
        "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"
        }
    },
]

我需要类似于以下的东西：

[
    {
        "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
                },
            ]
        }
    },
]

它是否可以被正常序列化？

- barelydrown

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Oleksii Tambovtsev · Accepted Answer

您可以像这样使用Django REST Framework中的嵌套模型序列化器来实现您的目的：

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__"

在您的视图中使用NewsletterSerializer：

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')

您还需要对代码进行其他更改：

您需要将字段newsletter_id和client_id重命名为newsletter和client。您可以在这里了解更多信息。
您需要在Message模型中的newsletter外键中指定related_name。将其设置为messages，如下所示：

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

你需要重新命名你的filter字段，因为它是保留字。