序列化RAWSQL查詢Django

希望你做得很好。我需要序列化我的 RAW 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

模型.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)

視圖.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 等將被列印。

回復：

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

是否可以正常序列化？

uj5u.com熱心網友回復：

您可以將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')

此外，您還必須在代碼中進行一些其他更改：

1. 您必須分別將您的欄位重newsletter_id命名為。你可以在這里閱讀更多關于它的資訊。client_idnewsletterclient
2. 您必須在模型的外鍵中指定related_name。把它設定成這樣：newsletterMessagemessages

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

3. 您必須重命名您的filter欄位，因為它是保留關鍵字。

標籤：

上一篇：運行服務器時Django網頁顯示錯誤

下一篇：擴展用戶擁有多種藥物