Re: Aggregation and following relationships backwards multiple levels

from django.db.models import Q
def total(self):
        subbudgets = SubBudget.objects.filter()
        contracts = Contract.objects.filter(subbudget__in=subbudgets)
        return ContractItem.objects.filter(contract__subbudget__budget__in=self.budget_set.all()).aggregate(Sum('total'))['total__sum']

On Wed, Feb 1, 2017 at 7:14 PM, Patrick Joy <patrick@joytech.com.au> wrote:

Hi all,

Would appreciate some advice on this, I'm having trouble working out the best way to aggregate across multiple foreign key relationships. I have come up with the solution below however I'm not sure if this is the correct way to handle this situation. Any advice would be appreciated.

Thanks


As an example I have a model structure that is 5 levels deep with foreign keys between each level, cost information is recorded at the lowest level (ContractItem)

--- Project
   |--- Budget
      |--- SubBudget
         |--- Contract
            |--- ContractItem - $100

If I want to aggregate the total cost up to the top project level I do it in multiple steps like this:

class Project(models.Model):
    name = models.CharField(max_length=50)

    def total(self):
        subbudgets = SubBudget.objects.filter(budget__in=self.budget_set.all())
        contracts = Contract.objects.filter(subbudget__in=subbudgets)
        return ContractItem.objects.filter(contract__in=contracts).aggregate(Sum('total'))['total__sum']


Is there a better way of doing this?


Full working code:

class Project(models.Model):
    name = models.CharField(max_length=50)

    def total(self):
        subbudgets = SubBudget.objects.filter(budget__in=self.budget_set.all())
        contracts = Contract.objects.filter(subbudget__in=subbudgets)
        return ContractItem.objects.filter(contract__in=contracts).aggregate(Sum('total'))['total__sum']
      
    def __str__(self):
        return self.name

class Budget(models.Model):
    project = models.ForeignKey(Project)
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

    def total(self):
        contracts = Contract.objects.filter(subbudget__in=self.subbudget_set.all())
        return ContractItem.objects.filter(contract__in=contracts).aggregate(Sum('total'))['total__sum']


class SubBudget(models.Model):
    budget = models.ForeignKey(Budget)
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

    def total(self):
        return ContractItem.objects.filter(contract__in=self.contract_set.all()).aggregate(Sum('total'))['total__sum']


class Contract(models.Model):
    subbudget = models.ForeignKey(SubBudget)
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

    def total(self):
        return self.contractitem_set.aggregate(Sum('total'))['total__sum']

class ContractItem(models.Model):
    contract = models.ForeignKey(Contract)
    total = models.DecimalField(default=0.00, decimal_places=2, max_digits=12)
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f1a3bfb7-f342-423d-8790-fc0d5bbcf151%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAGGVXBMTGigxzNwU7p0%2BvX90DyHO%2BeBfV2J5HzinMggDV2KyqA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.