Join two models and group with SUM in Django

Shanzedul 0
Viewed 29

I have two model name ProductDetails and InventorySummary. I want to join this two model and wants to group with product name and SUM product quantity. The quantity should be multiplication with product price. My models are given bellow:

class ProductDetails(models.Model):
   id = models.AutoField(primary_key=True)
   product_name = models.CharField(max_length=100, unique=True)
   purchase_price = models.DecimalField(max_digits=7, decimal_places=2)
   dealer_price = models.DecimalField(max_digits=7, decimal_places=2)
   retail_price = models.DecimalField(max_digits=7, decimal_places=2)
   remarks = models.CharField(max_length=255, blank=True)

   def __str__(self):
       return self.product_name

class InventorySummary(models.Model):
    id = models.AutoField(primary_key=True)
    date = models.DateField(default=date.today)
    product_name = models.ForeignKey(ProductDetails, on_delete=models.CASCADE)
    quantity = models.IntegerField()

    def __str__(self):
        return str(self.product_name)

My views are given bellow:

def stockPrice(request):
    stock = InventorySummary.objects.select_related('product_name').
            values('product_name').annotate(quantity=Sum('quantity'))

return render(request, 'inventory_price.html', {'stock': stock})
django-models django
1 Answers

I can achieve this with the F expression. I'm not sure what you refer to by "product price", so I took purchase_price:

from django.db.models import Sum, F

stock = (
    InventorySummary.objects.values("product_name__product_name")
    .order_by("product_name__product_name")
    .annotate(quantity=Sum(F("product_name__purchase_price") * F("quantity")))
)
userZWph0
Related