python - 我将如何使用 generics.ListAPIView 和 serializers.ModelSerializer 在 Django Rest Framework 中复制这个 SQL 查询

Question

实际上找不到任何像我想要使用案例语句和使用 DRF Django Rest Framework 左连接的东西，是的，这可以在我正在处理的项目的前端完成，但我不必让例如，在加载产品列表时，前端可能会发送 100 个请求。

我没有什么可以真正添加到这个的，但我已经尝试了许多不同的方法来做下面

SELECT 
    p.itemno,
    CASE
        WHEN cp.price IS NULL THEN p.HighSell
        ELSE cp.price
    END AS price
FROM
    api_product AS p
        LEFT JOIN
    api_customerprices AS cp ON p.itemno = cp.itemno
            AND cp.customerno = 'Examplecust'
WHERE
    p.FreeStock > 0
        or restockDate > '1900-01-01'

这是我的模型：

class Product(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    itemno = models.CharField(max_length=100)
    description = models.TextField(null=True)
    colour = models.CharField(max_length=100, null=True)
    manufacturerCode = models.CharField(max_length = 100, null=True)
    RRP = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    SSP = models.DecimalField(max_digits=6, decimal_places=2,null=True)
    FreeStock = models.IntegerField(null=True)
    ItemSpec1 = models.CharField(max_length=100, null=True)
    ItemSpec2 = models.CharField(max_length=100, null=True)
    ItemSpec3 = models.CharField(max_length=100, null=True)
    ItemSpec4 = models.CharField(max_length=100, null=True)
    ItemSpec5 = models.CharField(max_length=100, null=True)
    ItemSpec6 = models.CharField(max_length=100, null=True)
    ItemSpec7 = models.CharField(max_length=100, null=True)
    ItemSpec8 = models.CharField(max_length=100, null=True)
    ItemSpec9 = models.CharField(max_length=100, null=True)
    ItemSpec10 = models.CharField(max_length=100, null=True)
    TI = models.IntegerField(null=True)
    HI = models.IntegerField(null=True)
    Item_Height = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    Item_Length = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    Item_Width = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    ProductPaging_Height = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    ProductPaging_Length = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    ProductPaging_Width = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    CartonHeight = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    CartonLength = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    CartonWidth = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    palletQty = models.IntegerField(null=True)
    cartonQty = models.IntegerField(null=True)
    restockDate = models.DateField(null=True)
    IPG = models.CharField(max_length=100, null=True)
    CatalogueTheme = models.CharField(max_length=100, null=True)
    Analysis2 = models.CharField(max_length=100, null=True)
    Electrical_or_Housewares = models.CharField(max_length=100, null=True)
    HighSell = models.DecimalField(max_digits=6, decimal_places=2, null=True)
    Analysis1 = models.CharField(max_length=100, null=True)
    Image = models.TextField(null=True, blank=True)
    MarketingText = models.TextField(null=True, blank=True)
    SearchTerms = models.TextField(null=True, blank=True)
    ItemVariant = models.CharField(max_length=100)
    Categories = models.CharField(max_length=249, null=True)

    def __str__(self):
        return self.itemno

class CustomerPrices(models.Model):
    customerNo = models.CharField(max_length=20)
    itemno = models.CharField(max_length=20)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    startDate = models.DateField()
    endDate = models.DateField()

    def __str__(self):
        return self.customerNo

这是我的序列化器

class OauthProdListSerializer(serializers.ModelSerializer):
    class Meta:
        model = Product
        fields = (
            'id',
            'itemno',
            'description',
            'colour',
            'RRP',
            'SSP',
            'manufacturerCode',
            'FreeStock',
            'restockDate',
            'Image',
            'HighSell',
            'ItemVariant',
            'Categories'
        )

class OCustomerPricesSerializer(serializers.ModelSerializer):
    class Meta:
        model = CustomerPrices
        fields = (
            'id',
            'customerNo',
            'itemno',
            'price'
        )

标签： pythondjangopython-3.xdjango-rest-framework

Answer 1

找到了一种执行我想要的方法，结果发现我缺少关于 SerializerMethodField() 方法的知识，一旦我发现我很快就掌握了它。

class ProdListSerializer(ModelSerializer):
price = SerializerMethodField()
class Meta:
    model = Product
    fields = [
        'id',
        'itemno',
        'description',
        'colour',
        'RRP',
        'SSP',
        'manufacturerCode',
        'FreeStock',
        'restockDate',
        'Image',
        'ItemVariant',
        'Categories',
        'price'
    ]

def get_price(self, obj):
    itemno = obj.itemno
    customerNo = self._context['view'].request.query_params.get('customerNo', '')
    if customerNo:
        customerPrice = CustomerPrices.objects.filter(
            Q(customerNo=customerNo) &
            Q(itemno=itemno)
        ).values('price').first()
        if customerPrice:
            return customerPrice
        else:
            return Product.objects.filter(itemno=itemno).values('HighSell').first()
    else:
        return Product.objects.filter(itemno=itemno).values('HighSell').first()