Django Efficient Use of Queryset

Question

I have the following view:

from itertools import chain, groupby
from django.db.models.aggregates import Sum
from django.shortcuts import render_to_response, get_object_or_404
from django.template.context import RequestContext
import operator
from accounts.models import GeneralLedger, Account, Journal
from receivables.models import Item as ReceivableItem
from payables.models import Item as PayableItem

ACCOUNT_TYPES = {
    'INC':1,
    'COGS':2,
    'EXP':3,
    'NCA':4,
    'CA':5,
    'NCL':6,
    'CL':7,
    'EQ':8,
}

def income_statement(request):
    """ General income statement"""

    # Get a aggregated total per account type
    debit_ledger = GeneralLedger.objects.values('debit_account','amount')
    credit_ledger = GeneralLedger.objects.values('credit_account','amount')
    debit_journal = Journal.objects.values('debit_account','amount')
    credit_journal = Journal.objects.values('credit_account','amount')
    debit_receivables = ReceivableItem.objects.values('debit_account','amount')
    credit_receivables = ReceivableItem.objects.values('credit_account','amount')
    debit_payables = PayableItem.objects.values('debit_account','amount')
    credit_payables = PayableItem.objects.values('credit_account','amount')
    general_ledger = chain(debit_ledger, credit_ledger, debit_journal, credit_journal, debit_receivables,
                     credit_receivables, debit_payables, credit_payables)
    generalledger = list(general_ledger)

    gross_total = 0
    net_profit = 0

    # For each general ledger item
    # get the account name (because value query set only returns ID)
    # get the account type so we're able to set the order for the template display
    for e in generalledger:
        if "debit_account" in e:
            account_detail = get_object_or_404(Account, pk=e["debit_account"])
            e['account'] = e['debit_account']
            e['amount'] = -e['amount']
        if "credit_account" in e:
            account_detail = get_object_or_404(Account, pk=e["credit_account"])
            e['account'] = e['credit_account']
        e["account_name"] = account_detail.name
        e["account_type"] = account_detail.type
        if account_detail.type == 'INC':
            e["order"] = ACCOUNT_TYPES['INC']
            # add to the gross total
            gross_total += e['amount']
        elif account_detail.type == 'COGS':
            # subtract from the gross total
            gross_total -= e['amount']
            e["order"] = ACCOUNT_TYPES['COGS']
        elif account_detail.type == 'EXP':
            # net profit is gross total minus the expenses
            net_profit = gross_total - e['amount']
            e["order"] = ACCOUNT_TYPES['EXP']

    generalledger = sorted(generalledger, key=operator.itemgetter('account'))

    groups = []
    uniquekeys = []
    for k, g in groupby(generalledger, operator.itemgetter('account')):
        groups.append(list(g))      # Store group iterator as a list
        uniquekeys.append(k)

    for group in groups:
        group_total = 0
        for subgroup in group:
            group_total += subgroup['amount']
            subgroup['total'] = group_total




    context_dict = {
        'GeneralLedger': groups,
        'Gross': gross_total,
        'Net': net_profit,
    }

    return render_to_response('accounts/income-statement.html', context_dict, RequestContext(request))

It works however I do not think it is very efficient as on each iteration it is calling the database. I have tried using objects.only(...) instead of objects.values(...) however then I cannot add the items I need to it. Is there a more efficient way of doing this?

Answer 1

Well, this could be heavily refactored and reduced to a fraction of what it is now, but that's not the crux of your question.

Eliminating the per-iteration queries is indeed the lowest hanging fruit in improving this. You should just include the relevant account detail fields in the queries, instead of querying them separately on each iteration.

debit_ledger = GeneralLedger.objects.values('debit_account', 'debit_account__name', 'debit_account__type', 'amount')

Then in the results, you'll have it already as e['debit__account__name'] and so on, so you can remove the per-iteration queries of Account