django - annotate() - Sum() of a column with filter on another column

Question

I have the following two models.

class Product(models.Model):
    product_group=models.ForeignKey('productgroup.ProductGroup', null=False,blank=False)
    manufacturer=models.ForeignKey(Manufacturer, null=False,blank=False)
    opening_stock=models.PositiveIntegerField(default=0)

    class Meta:
        unique_together = ('product_group', 'manufacturer')

and

TRANSACTION_TYPE=(('I','Stock In'),('O','Stock Out'))
class Stock(models.Model):
    product=models.ForeignKey('product.Product', blank=False,null=False)
    date=models.DateField(blank=False, null=False,)
    quantity=models.PositiveIntegerField(blank=False, null=False)
    ttype=models.CharField(max_length=1,verbose_name="Transaction type",choices=TRANSACTION_TYPE, blank=False)

I need to list all products with stock_in_sum=Sum(of all stock ins) , stock_out_sum=Sum(of all stock outs) and blance_stock=opening_stock+stock_in_sum - stock_out_sum

This is what I've achieved so far.

class ProductList(ListView):
    model=Product

    def get_queryset(self):
        queryset = super(ProductList, self).get_queryset()
        queryset = queryset.prefetch_related('product_group','product_group__category','manufacturer')
        queryset = queryset.annotate(stock_in_sum = Sum('stock__quantity'))
        queryset = queryset.annotate(stock_out_sum = Sum('stock__quantity'))

I need to get

1. stock_in_sum as the sum(quantity) where ttype='I'
2. stock_out_sum as the sum(quantity) where ttype='O'
3. blance_stock as product.opening_stock + stock_in_sum - stock_out_sum

along with each Product object.

How do I achieve this?

Thanks.

Bandit

Answer 1

You could use conditional aggregation

queryset = queryset.annotate(
    stock_in_sum = Sum(Case(When(stock__ttype='I', then=F('stock__quantity')), output_field=DecimalField(), default=0)),
    stock_out_sum = Sum(Case(When(stock__ttype='O', then=F('stock__quantity')), output_field=DecimalField(), default=0)))
)

To make the sums, and after that compute the balance with F() expression

queryset = queryset.annotate(balance_stock=F('opening_stock') + F('stock_in_sum') - F('stock_out_sum'))

You can also chain the different operation instead of multiple assignations:

queryset = queryset.prefetch_related(...).annotate(...).annotate(...)

Anytime

Thanks! It asked me to use ExpressionWrapper(). Got the first two data as follows queryset = queryset.annotate( stock_in_sum = Sum(Case(When(stock__ttype='I', then= ExpressionWrapper(F('stock__quantity'), output_field=DecimalField())))) ) queryset = queryset.annotate( stock_out_sum = Sum(Case(When(stock__ttype='O', then= ExpressionWrapper(F('stock__quantity'), output_field=DecimalField())))) ) – Bandit

But queryset = queryset.annotate( balance_stock = ExpressionWrapper(F('opening_stock') + F('stock_in_sum') - F('stock_out_sum'), output_field=DecimalField()) ) gives None for all rows with either of stock_in_sum or stock_out_sum is None. – Bandit

Looks like I need to return 0 instead of None somewhere, but don't know how. – Bandit

I edited the answer to add default values so it will not try to add None, but 0. – Khiva

Brilliant! That gives the exact result I wanted. No more duplicate queries :) – Bandit

How do I get count of related rows in Stock, filtered by ttype=I/O, along with this annotate? stock_in_count = stock_in_count = Count(Case(When(stock__ttype='I', then=1), output_field=DecimalField(), default=0)), ??? – Bandit