Changeset 3f1e074 for remit

Timestamp:

Mar 24, 2010, 6:34:54 AM (15 years ago)

Author:

Alex Dehnert <adehnert@…>

Branches:

master, client

Children:

13e7c01

Parents:

ed4ccc7

git-author:

Alex Dehnert <adehnert@…> (03/24/10 06:34:54)

git-committer:

Alex Dehnert <adehnert@…> (03/24/10 06:34:54)

Message:

Calculate totals using annotate, not aggregate

For reports like "all the accounts ever", this can
increase performance by a factor of about three
and reduce the number of database queries by a
factor of about a hundred (aka the number of rows
in the table produced).

It is substantially more complicated, though.
The original code is in a "slow = False; if slow:"
block. In theory, it'd be good to keep both codepaths
working and test them against each other.

File:

• remit/finance_core/views.py (modified) (2 diffs)

remit/finance_core/views.py

@@ -15 +15 @@
     line_items = finance_core.models.LineItem.objects.all()
     term_name = 'All'
+    term_primary_Q = Q()
     if 'term' in request.REQUEST:
         term_obj = get_object_or_404(finance_core.models.BudgetTerm, slug=request.REQUEST['term'])
         term_name = term_obj.name
         line_items = line_items.filter(budget_term=term_obj)
+        term_primary_Q = Q(lineitem__budget_term=term_obj)
     if 'area' in request.REQUEST:
         base_area_obj = get_object_or_404(finance_core.models.BudgetArea, pk=request.REQUEST['area'])
@@ -29 +31 @@
     primary_name = 'Budget Areas'
     primary_axis = [
-        (area.indented_name(base_area_depth), Q(budget_area=area), ) for area in base_area_obj.get_descendants()
+        (area.pk, area.indented_name(base_area_depth), Q(budget_area=area), ) for area in base_area_obj.get_descendants()
     ]
+    primary_axis_objs = base_area_obj.get_descendants()
     secondary_name = 'Layers'
-    secondary_axis = [ (finance_core.models.layer_name(layer), Q(layer=finance_core.models.layer_num(layer)), ) for layer in finance_core.models.layers ]
+    secondary_axis = [
+        (
+            finance_core.models.layer_name(layer),
+            Q(layer=finance_core.models.layer_num(layer)),
+            Q(lineitem__layer=finance_core.models.layer_num(layer)),
+        )
+        for layer in finance_core.models.layers
+    ]
 
-    primary_axis.append(('Total', Q(), ))
-    secondary_axis.append(('Total', Q(), ))
+    secondary_axis.append(('Total', Q(), Q()))
 
-    primary_labels = [ primary[0] for primary in primary_axis ]
+    primary_labels = [ ]
+    arcprimary = {}
+    table = []
+    zero = Decimal('0.00')
+    for num, (pk, label, qobj, ) in enumerate(primary_axis):
+        primary_labels.append(label)
+        arcprimary[pk] = num
+        table.append([zero]*len(secondary_axis))
+    print arcprimary
+
     secondary_labels = [ secondary[0] for secondary in secondary_axis ]
 
-    zero = Decimal('0.00')
-    def total_amount(queryset):
-        amount = queryset.aggregate(Sum('amount'))['amount__sum']
-        if amount is None: return zero
-        else: return amount
-    table = [ # Primary axis
-            [ # Secondary axis
-                total_amount(line_items.filter(primary[1], secondary[1]))
-            for secondary in secondary_axis]
-        for primary in primary_axis]
+    def lineitem_total(obj):
+        if obj.lineitem__amount__sum is None: return zero
+        else: return obj.lineitem__amount__sum
+    for num, (label, qobj_lineitem, qobj_primary) in enumerate(secondary_axis):
+        secondary_results = (primary_axis_objs.filter(qobj_primary, term_primary_Q).annotate(Sum('lineitem__amount')))
+        for cell in secondary_results:
+            print cell, cell.pk, arcprimary[cell.pk], num, table[arcprimary[cell.pk]]
+            table[arcprimary[cell.pk]][num] = lineitem_total(cell)
+
+    slow = False
+    if slow:
+        # This uses a simpler but probably slower method
+        # In theory, if we grow unit tests, comparing this method with
+        # the one above using annotate would be a good idea
+        def total_amount(queryset):
+            amount = queryset.aggregate(Sum('amount'))['amount__sum']
+            if amount is None: return zero
+            else: return amount
+        table = [ # Primary axis
+                [ # Secondary axis
+                    total_amount(line_items.filter(primary[1], secondary[1]))
+                for secondary in secondary_axis]
+            for primary in primary_axis]
 
     debug = False