CODEWITHSUNDEEP
djangodjango-rest-framework
Rounak Jain
Rounak Jain

Reputation: 499

django aggregating values from related table

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

UPDATE AFTER COMMENTS:

class SaleInvoiceSerializer(serializers.ModelSerializer):
    def get_grand_total(self, saleInvoiceId):
        return SaleLineItems.objects.filter(sale_invoice=saleInvoiceId).aggregate(Sum('subTotal'))
    grand_total = serializers.SerializerMethodField()

    class Meta:
        model = SaleInvoice
        fields = "__all__"

Update2:

Removed grand_total from serializers.py

Added the below property in models.py for SaleInvoice model

Also added related_name='items' for SaleLineItems model

@property
def grandTotal(self):
    sum = 0
    for some in self.items.all():
        sum += some.sub_total
    return sum

This gets me the desired result but I have no clue if this is the right approach. So posted it here.

Upvotes: 0

Views: 90

Answers (1)

dmitryro
dmitryro

Reputation: 3516

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.

Then your serializer can be like:

class SaleLineItemsSerializer(serializers.ModelSerializer):
    class Meta:
        model = SaleLineItems
        fields = ('id','sale_invoice','product_name','product_qty',
                  'product_rate', 'product_disc', 'sub_total',)

Notice the new field sub_total listed in the serializer.

In your views.py create a viewset:

class  SaleLineItemsList(generics.ListAPIView):
    queryset = SaleLineItems.objects.all()
    serializer_class = SaleLineItemsSerializer

In your urls.py add for Django 2.0 and later add:

    path('salelineitems/', SaleLineItemsList.as_view()),

For Django 1.7- 1.11 add:

    url(r'^salelineitems/',SaleLineItemsList.as_view()),

Upvotes: 1

Related Questions