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

Appears to me that you need to do it in a few queries (at least 2). What have you tried and any observations if you did try something? — P S Solanki, Aug 27 '22 at 14:09
Yeah. You can seen that overridden list method. To solve the I had tries that. — mufazmi, Aug 27 '22 at 16:37
But right now I am thinking to update the sales count any stock table. Instead of fetching all record then subscript it. Will it be good solution to solve the issue? Thanks — mufazmi, Aug 27 '22 at 16:39

score 1 · Accepted Answer · answered Aug 31 '22 at 11:22

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.

answered Aug 31 '22 at 11:22

Amrez

585
1
5
20

We have modified our schema. So not using these condition anymore – mufazmi Sep 02 '22 at 02:20
What is your suggestion. which one option will be good. 1) Decreasing the stock available quantity on sell. 2) Fetching the thousand of sold product quantity then perform mathematics to get the current available quantity. – mufazmi Sep 02 '22 at 02:23
@mufazmi personally, i prefer the second option, so i can keep history of sales better. I think quantity field of sales was a good choice. – Amrez Sep 02 '22 at 06:57
1

Thankyou @Amrez For your feedback. I also love the second option. But i think it will take longer time to fetch the record than first option. right.? – mufazmi Sep 02 '22 at 07:44
1

@mufazmi i think compared to the main query, its time can be ignored. But right, the second option should takes longer – Amrez Sep 02 '22 at 10:22

ModelViewSet Queryset Filter with table based value with another table

1 Answers1