Hi,
Thank you for your response, this works perfectly!
Patrick
On Thursday, February 2, 2017 at 4:07:21 AM UTC+11, pradam.programming
wrote:
>
> Hi Patrick,
> you can do like this:
> def total(self):
> return ContractItem.objects.filter(contract__subbudget__budge__in=
> self.budget_set.all()).aggregate(Sum('total'))['total__sum']
>
> try like this..!
>
> On Wed, Feb 1, 2017 at 7:14 PM, Patrick Joy <pat...@joytech.com.au
> <javascript:>> 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...@googlegroups.com <javascript:>.
>> To post to this group, send email to django...@googlegroups.com
>> <javascript:>.
>> 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
>>
>> <https://groups.google.com/d/msgid/django-users/f1a3bfb7-f342-423d-8790-fc0d5bbcf151%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>> 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+unsubscr...@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/4dbc5abe-e913-44d0-aaee-55679c673b46%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
