Re: Aggregation and following relationships backwards multiple levels
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
> > 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 .
>> To post to this group, send email to django...@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
>>
>> <https://groups.google.com/d/msgid/django-users/f1a3bfb7-f342-423d-8790-fc0d5bbcf151%40googlegroups.com?utm_medium=email_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.
Aggregation and following relationships backwards multiple levels
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+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/f1a3bfb7-f342-423d-8790-fc0d5bbcf151%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Help required using pre_fetched data in aggregate()
Hi all,
Would appreciate some help on how to optimise the following piece of code.
I have two models, "invoice" and "invoice item". The invoice total is
calculated in invoice_total() by aggregation the sub_total and tax_total
from all invoice_items (see code below).
The problem I have is that even though I prefetch all the invoice_items in
the view, when invoice.invoice_total is called in a template 3 identical
queries will be run for each invoice. For example see some output from a
profile.
# All invoice items are prefetched correctly
SELECT ••• FROM `billing_invoice_item` WHERE
`billing_invoice_item`.`invoice_id` IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 51,
53, 54, 55, 56, 58, 59, 60, 61, 62, 65, 66, 67, 68, 69, 70, 72, 73, 74, 75,
76, 77, 78, 80, 81, 82, 84, 85, 87, 88, 89, 90, 91, 92, 93, 94, 96, 97, 98,
99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 113, 114, 115,
116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 128, 129, 130, 131,
132, 134, 135, 136, 137, 138, 140, 141, 142, 143, 144, 145, 146, 147, 148,
149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163,
164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178,
179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193,
194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208,
209, 210, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224,
225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239,
240, 241, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255,
256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270,
271, 272, 273, 274, 275, 276, 277)
but then after this the table is queried 3 more times
SELECT ••• FROM `billing_invoice_item` WHERE
`billing_invoice_item`.`invoice_id` = 277
SELECT ••• FROM `billing_invoice_item` WHERE
`billing_invoice_item`.`invoice_id` = 277
SELECT ••• FROM `billing_invoice_item` WHERE
`billing_invoice_item`.`invoice_id` = 277
I'm using django 1.5 and my code is below.
Any thoughts?
#models.py
class Invoice(models.Model):
customer = models.ForeignKey(Customer)
:
:
:
def invoice_total(self):
invoice_total = self.invoice_item_set.aggregate(Sum('sub_total'),
Sum('tax_total'))
return str(round(invoice_total['sub_total__sum'], 2))
class Invoice_Item(models.Model):
invoice = models.ForeignKey('Invoice')
sub_total = models.DecimalField(blank=True, null=True, max_digits=9,
decimal_places=2)
tax_total = models.DecimalField(blank=True, null=True, max_digits=9,
decimal_places=2)
:
:
:
#views.py
def invoice(request):
invoices = Invoice.objects.all().prefetch_related('invoice_item_set',
'journals', 'journals__transaction_set',
'journals__transaction_set__account', 'customer')
return render_to_response('billing/invoices.html', {'invoices':
invoices,}, context_instance=RequestContext(request))
--
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.
