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 <[email protected]
> <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 [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <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 [email protected].
To post to this group, send email to [email protected].
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.
