Django高效使用Queryset

Question

我有以下观点：

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))

它可以工作，但是我认为它不是非常有效，因为它在每次迭代中都调用数据库。我尝试过使用 objects.only(...) 而不是objects.values(...) 但是我无法向其中添加我需要的项目。有没有更有效的方法来做到这一点？

Answer 1

好吧，这可能会被大量重构并减少到现在的一小部分，但这不是你问题的关键。

消除每次迭代查询确实是改进这一点最容易实现的目标。您应该只在查询中包含相关的帐户详细信息字段，而不是在每次迭代中单独查询它们。

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

然后在结果中，您将已经将其作为 e['debit__account__name'] 等，因此您可以删除 Account 的每次迭代查询