django aggregating values from related table

561 views django
0

I am using Django to serve REST APIs to my front end app.

I have a SaleInvoice table and SaleLineItems table.

I created a SerializerMethodField (sub_total) to calculate a value based on values in fields of SaleLineItems table.

When I make a post/patch request to SaleLineItems, I am able to get the sub_total in response.data.

Now, I need a grand_total in SaleInvoice table/serializer which can aggregate all sub_total values from SaleLineItems records which have the same Sale Invoice id.

If I do

SaleLineItems.objects.filter(sale_invoice=saleInvoiceId).aggregate(Sum('sub_total'))

I get an error telling me that I cannot use 'sub_total' but can use other regular fields of the SaleLineItems table.

Kindly help.

models.py

...

class SaleInvoice(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    serial_number = models.IntegerField(blank=True, null=True)
    amount_before_freight = models.FloatField(blank=True, null=True, default=0)
    freight = models.FloatField(blank=True, null=True, default=0)
    amount_after_freight = models.FloatField(blank=True, null=True, default=0)

    def __str__(self):
        return str(self.id)




class SaleLineItems(models.Model):
    sale_invoice = models.ForeignKey(SaleInvoice, on_delete=models.CASCADE)
    product_name = models.ForeignKey(Product, on_delete=models.PROTECT)
    product_qty = models.FloatField()
    product_rate = models.FloatField(blank=True, null=True, default=0)
    product_disc = models.FloatField(blank=True, null=True, default=0)


...

serializers.py

...

class SaleLineItemsSerializer(serializers.ModelSerializer):
    def get_sub_total(self, instance):
        return (
            Decimal(instance.product_qty)
            * Decimal(instance.product_rate)
            * Decimal(1-(instance.product_disc/100))
            ).quantize(Decimal("1.00"))

    sub_total = serializers.SerializerMethodField()

    class Meta:
        model = SaleLineItems
        fields = "__all__"

...

answered question

You're defining your get_subtotal at the serializer level, and Django ORM knows nothing about Django Rest Framework - define your field in the model to use in aggregate.

1 Answer

10

Update your SaleLineItems model to be:

class SaleLineItems(models.Model):
    sale_invoice = models.ForeignKey(SaleInvoice, on_delete=models.CASCADE)
    product_name = models.ForeignKey(Product, on_delete=models.PROTECT)
    product_qty = models.FloatField()
    product_rate = models.FloatField(blank=True, null=True, default=0)
    product_disc = models.FloatField(blank=True, null=True, default=0)

    @property
    def sub_total(self):
        return (
            Decimal(self.product_qty)
            * Decimal(self.product_rate)
            * Decimal(1-(self.product_disc/100))
            ).quantize(Decimal("1.00"))

The way you defined it will not allow Django know about your aggregated property, as it lives in serializer and Django models know nothing about Django Rest Framework serializers. The query is used at Django level (Django ORM), not Django Rest Framework level.

posted this

Have an answer?

JD

Please login first before posting an answer.