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
    }

Back to Top