menu

Questions & Answers

how to find available quantity of products Django ORM

Inaccurate results using Django ORM for calculating available quantity of products.

I'm facing a problem in finding the available quantity of products using the Django ORM. I've written a code that uses the ORM to annotate the queryset with the quantity produced, quantity sold and available quantity fields, but the results are inaccurate. However, when I use raw SQL to perform the same calculations, the results are accurate. I need help understanding why the ORM code is not working as expected and how to fix it.

Django ORM code for finding quantities of products:

   def get_queryset(self):
        queryset = super().get_queryset()
        queryset = queryset.annotate(Quantity_Produced=Sum(F('production__qunatity_produced')))
        queryset = queryset.annotate(Quantity_Sold=Sum(F('sales__qunatity_delivered')))
        queryset = queryset.annotate(Quantity_available=Sum(F('production__qunatity_produced'))
                                                            - Sum(F('sales__qunatity_delivered')))
        return queryset

The Output (Inaccurate):

    {
        "product_id": 1,
        "product_name": "Product 1",
        "weight": 10.0,
        "Quantity_Produced": 6300.0,
        "Quantity_Sold": 2600.0,
        "Quantity_available": 3700.0
    }

ORM's Raw SQL method for finding the available quantity of products:

    def get_queryset(self):
        queryset= models.Products.objects.raw('''
            SELECT
                *,
                (SELECT SUM(q.qunatity_produced) FROM production q WHERE q.product_id = p.product_id) AS Quantity_Produced ,
                (SELECT SUM(s.qunatity_delivered) FROM Sales s WHERE s.product_id = p.product_id) AS Quantity_Sold,
                sum((SELECT SUM(q.qunatity_produced) FROM production q WHERE q.product_id = p.product_id)
                -(SELECT SUM(s.qunatity_delivered) FROM Sales s WHERE s.product_id = p.product_id))as Quantity_available
            FROM
                products p
                group by Product_id
                order by Product_id
            ''')
        return queryset

The Output (Accurate):

    {
        "product_id": 1,
        "product_name": "Product 1",
        "weight": 10.0,
        "Quantity_Produced": 700.0,
        "Quantity_Sold": 260.0,
        "Quantity_available": 440.0
    }

Models:

class Products(models.Model):
    product_id = models.AutoField(primary_key=True)  
    product_name = models.CharField(max_length=255)
    weight = models.ForeignKey(Bags, models.DO_NOTHING)


class Production(models.Model):
    production_id = models.AutoField(primary_key=True)
    product = models.ForeignKey('Products', models.DO_NOTHING)
    date_of_production = models.DateField()
    qunatity_produced = models.FloatField()
    unit_price = models.FloatField()


class Sales(models.Model):
    sales_id = models.AutoField(primary_key=True)
    date_of_sale = models.DateField()
    customer = models.ForeignKey(Customers, models.DO_NOTHING)
    product = models.ForeignKey(Products, models.DO_NOTHING)
    qunatity_delivered = models.FloatField()
    unit_price = models.FloatField()
    total_amount = models.FloatField(default=0.0)
    payment_type = models.ForeignKey(Paymenttype, models.DO_NOTHING)
    description = models.TextField(blank=True, null=True)
    address= models.CharField(max_length=255)


Comments:
2023-01-25 00:55:06
Put model code in question
2023-01-25 00:55:06
all quantity will be calculate in serializers django-rest-framework.org/api-guide/fields please see detail documentation related to serializer
2023-01-25 00:55:06
@Mahammadhusainkadiwala question is updated. Is there an error in my use of the annotate function?
2023-01-25 00:55:06
Do serializers provide any built-in procedures for the mentioned task? @TanveerAhmad
Answers(0) :