ModelViewSet Queryset Filter with table based value with another table

Question

I have two tables in my database ProductStock and Sales.And I am storing the Product info in the ProductStock table and Sales info inside the Sales table.

Based on the total sum of sold quantity I want to filter the data, and return the data only whose quantity is greater than 0.

for eg.

PRODUCT STOCK DATA

ID	PRODUCT	QUANTITY
1	Django Course	50
2	Social Codia	50

SALES DATA

PRODUCT_STOCK_ID	QUANTITY
1	5
1	45
2	35
2	10

Here the sold quantity of product 1 is 5+45=50, that means there is no stock left in the database for product 1.

QUESTION IN SHORT : How can i fetch all these products whose quantity is greater than current_quantity - sold_quantity greater than 0.

My ProductStock Model

class ProductStock(Core):
    medical = models.ForeignKey(Medical, on_delete=models.CASCADE)
    distributer = models.ForeignKey(Distributer,on_delete=models.DO_NOTHING)
    product = models.ForeignKey(Product,on_delete=models.CASCADE,related_name='product')
    variant = models.ForeignKey(Attribute,on_delete=models.DO_NOTHING)
    batch = models.CharField(max_length=20,null=True, blank=True)
    purchase_price = models.CharField(max_length=10,null=False, blank=False)
    price = models.CharField(max_length=10,null=False,blank=False)
    quantity = models.IntegerField(null=False,blank=False)    
    location = models.ForeignKey(Location, on_delete=models.DO_NOTHING)
    low_stock = models.IntegerField(default=10,null=False,blank=False)
    expire_date = models.DateTimeField(null=False,blank=False)

My SALE Model

class Sale(Core):
    product_stock = models.ForeignKey(ProductStock,on_delete=models.CASCADE,related_name='sales')
    medical = models.ForeignKey(Medical,on_delete=models.CASCADE,related_name='medical')
    quantity = models.IntegerField(default=1,null=False,blank=False)
    price = models.CharField(max_length=10,null=False,blank=False)
    discount = models.FloatField(max_length=10,default=0)

ProductStockViewSet

class ProductStockViewSet(ModelViewSet):
    serializer_class = ProductStockSerializer
    permission_classes  = [IsAuthenticated]
    authentication_classes = [JWTAuthentication]

    def get_queryset(self):
        return ProductStock.objects.all()

To deal with the issue I have overridden the list of ProductStockViewSet, by using a for loop fetching every single product's total sold quantity, and by subscripting it with actual quantity i am getting the current quantity.

But I know this could not be a good solution to fetch data like these.

def list(self,request,pk=None):
    products = ProductStock.objects.all()
    for product in products:
        quantities = Sale.objects.filter(product_stock=product).aggregate(quantities = Sum('quantity')).get('quantities')
        if quantities is not None:
            current_quantity = product.quantity - quantities
            print(f'QUANTITY : {product.quantity} ==== SOLD QUANTITY : {quantities} ==== CURRENT QUANTITY : {current_quantity}' ,)
            product.quantity = current_quantity
            
    serializer = ProductStockSerializer(products,many=True)
    return Response(serializer.data)

Any suggestions would be appreciated.

-Thanks

Answer 1

the query you wanted:

from django.db.models import F, Sum

ProductStock.objects.alias(
    quantity_sold=Sum('sales__quantity')
).filter(
    quantity_sold__lt=F('quantity')
)

* default values for null and blank parameters of fields is False, so no need to pass null=False, blank=False to every field you make.