Get sum of foreign key attribute using django

Question

class sales_line(models.Model):
    sales_id = models.CharField(max_length=200)
    item = models.CharField(max_length=200)
    qty =   models.IntegerField()

    def __unicode__(self):
        return self.sales_id


class Loss(models.Models):
    sale_id = models.ForeignKey(sales_line)

how can i know how much quantity is lost if the sales_id is in loss table , then the sale is lost

Burhan Khalid · Accepted Answer · 2014-01-31 14:09:04Z

3

You need to use Sum from aggregates:

from django.db.models import Sum
lost_sales_id = Loss.objects.all().values_list('sale_id__sales_id', flat=True)
total = sales_line.objects.filter(sales_id__in=lost_sales_id).annotate(Sum('qty'))

You really need to optimize your models:

class SalesLine(models.Model):
    sales_id = models.CharField(max_length=200)
    item = models.CharField(max_length=200)
    qty = models.IntegerField()

    def __unicode__(self):
        return unicode(self.sales_id)

class Loss(models.Model):
    sale = models.ForeignKey(SalesLine)

Now you can do this:

lost_sales_id = Loss.objects.all().values_list('sale__pk', flat=True)
SalesLine.objects.filter(pk__in=lost_sales_id).annotate(Sum('qty'))

answered Jan 31, 2014 at 14:09

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Exelian Over a year ago

Why not pass the Loss.objects.all() queryset straight in the filter?

Burhan Khalid Over a year ago

Because that will return a list of Loss objects.

rgm Over a year ago

what can't i use aggregate here instead of annotate. it will return sum of qty.

rgm Over a year ago

I got my answer with aggregate

Collectives™ on Stack Overflow

Get sum of foreign key attribute using django

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related