django ListView - custom queryset by joinining with another model

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)
    product_type=models.CharField(max_length=2, choices=PRODUCT_TYPE, null=False,blank=False)
    wheel_position=models.CharField(max_length=1, choices=FRONT_BACK, null=False,blank=False)
    opening_stock=models.PositiveIntegerField(default=0)

    class Meta:
        ordering=['product_group','manufacturer']
        unique_together = ('product_group', 'manufacturer','product_type','wheel_position')


    def get_total_stock_in(self):
        sum=Stock.objects.filter(product=self.id, ttype='I').aggregate(Sum('quantity'))

        if sum['quantity__sum'] is not None:
            return sum['quantity__sum']
        else:
            return 0

    def get_total_stock_out(self):
        sum=Stock.objects.filter(product=self.id, ttype='O').aggregate(Sum('quantity'))
        if sum['quantity__sum'] is not None:
            return sum['quantity__sum']
        else:
            return 0

    def get_balance_stock(self):
        return (self.opening_stock+self.get_total_stock_in()
                - self.get_total_stock_out())

and

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)
    added_date=models.DateTimeField(blank=False, auto_now=True)

I have a ListView subclass to list all products.

class ProductList(ListView):
    model=Product
    paginate_by = 20

    def get_queryset(self):
        queryset = super(ProductList, self).get_queryset()
        queryset = queryset.prefetch_related('product_group','product_group__category','manufacturer')
        return queryset.order_by('product_group__category','product_group')

I need to display the total stock in, total stock out and available balance along with each product in the list-view.

Right now, I'm calling the functions get_total_stock_in(), get_total_stock_out() and get_balance_stock() defined in the Product model . But this creates a lot of repeated queries on the database.

How do I add these data into the queryset of ListView?

Thanks for any help.

score 0 · Accepted Answer · answered Aug 07 '17 at 14:37

Let me post the solution here.

I have got my solution with the help of another thread at django - annotate() - Sum() of a column with filter on another column

Here's my ListView right now.

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(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))
        )
        queryset = queryset.annotate(
        balance_stock = ExpressionWrapper(F('opening_stock') + F('stock_in_sum') - F('stock_out_sum'), output_field=DecimalField())
        )

        return queryset

django ListView - custom queryset by joinining with another model

1 Answers1