Django预取查询集中Aggregate无法正常工作的解决办法

错误原因 #

你遇到的 'dict' object has no attribute '_add_hints' 错误，根源在于：
aggregate() 方法执行后返回的是包含聚合结果的字典，而 Prefetch 要求传入的 queryset 参数必须是 Django 的 QuerySet 对象。当Django尝试对字典调用QuerySet专属的 _add_hints 方法时，自然触发属性错误。

解决方案 #

方案一：用Subquery+OuterRef在主查询中注解聚合值 #

这种方式可以将前期借贷总额直接作为字段注解到账户对象上，完全整合到主查询流程里，符合你减少查询次数的需求。

步骤1：导入必要的模块

from django.db.models import Sum, OuterRef, Subquery

步骤2：修改查询逻辑

# 定义子查询，计算每个账户的前期借贷总额
prior_balance_subquery = GeneralLedger.objects.filter(
    # 关联当前外层查询的账户ID
    associated_account_from_chart_of_accounts=OuterRef('pk'),
    # 保留原有的过滤条件
    Q(
        accounts_payable_line_item__property__pk__in=property_pks,
        journal_line_item__property__pk__in=property_pks,
        _connector=Q.OR,
    ),
    date_entered__date__lte=start_date
).values('associated_account_from_chart_of_accounts').annotate(
    prior_credit=Sum('credit_amount'),
    prior_debit=Sum('debit_amount')
).values('prior_credit', 'prior_debit')

# 获取custom_report，同时注解前期余额
custom_report = AccountTree.objects.select_related().prefetch_related(
    'account_tree_total', 'account_tree_regular',
    Prefetch('account_tree_header', queryset=AccountTreeHeader.objects.select_related(
        'associated_account_from_chart_of_accounts', 'associated_total_account_tree_account__associated_account_from_chart_of_accounts'
    ).prefetch_related(
        'associated_regular_account_tree_accounts',
        # 保留原有的range_gl预取逻辑
        Prefetch('associated_regular_account_tree_accounts__associated_account_from_chart_of_accounts__general_ledger',
                 queryset=GeneralLedger.objects.select_related(
                 ).filter(Q(
                     accounts_payable_line_item__property__pk__in=property_pks,
                     journal_line_item__property__pk__in=property_pks,
                     _connector=Q.OR,
                 ), date_entered__date__gte=start_date, date_entered__date__lte=end_date).order_by('date_entered'), to_attr='range_gl'),
    ).annotate(
        # 给账户添加前期余额注解字段
        prior_credit_amount=Subquery(prior_balance_subquery.values('prior_credit')[:1]),
        prior_debit_amount=Subquery(prior_balance_subquery.values('prior_debit')[:1])
    )),
).get(pk=custom_report.pk)

步骤3：修改取值逻辑

直接从账户对象上获取注解的字段即可：

header_accounts = custom_report.account_tree_header.all()

for header_account in header_accounts:
    for regular_account in header_account.associated_regular_account_tree_accounts.all():
        gl_account = regular_account.associated_account_from_chart_of_accounts
        gl_entries = gl_account.range_gl
        # 直接使用注解的字段，默认0避免None值问题
        prior_credit = gl_account.prior_credit_amount or 0
        prior_debit = gl_account.prior_debit_amount or 0

方案二：预计算聚合值存入字典，Python层面匹配 #

如果觉得Subquery逻辑复杂，可以先单独计算所有相关账户的前期余额，存入字典后在遍历取值，这种方式逻辑更直观。

步骤1：预计算前期余额字典

# 先获取所有需要计算的账户ID
account_ids = AccountTreeHeader.objects.filter(
    account_tree=custom_report
).prefetch_related('associated_regular_account_tree_accounts__associated_account_from_chart_of_accounts').values_list(
    'associated_regular_account_tree_accounts__associated_account_from_chart_of_accounts__pk', flat=True
).distinct()

# 计算这些账户的前期余额
prior_balances = GeneralLedger.objects.filter(
    associated_account_from_chart_of_accounts__pk__in=account_ids,
    Q(
        accounts_payable_line_item__property__pk__in=property_pks,
        journal_line_item__property__pk__in=property_pks,
        _connector=Q.OR,
    ),
    date_entered__date__lte=start_date
).values('associated_account_from_chart_of_accounts').annotate(
    prior_credit=Sum('credit_amount'),
    prior_debit=Sum('debit_amount')
)

# 转成字典，key为账户ID，value为借贷数据
prior_balance_dict = {
    item['associated_account_from_chart_of_accounts']: {
        'prior_credit': item['prior_credit'] or 0,
        'prior_debit': item['prior_debit'] or 0
    } for item in prior_balances
}

步骤2：获取custom_report（无需处理old_gl的Prefetch）

custom_report = AccountTree.objects.select_related().prefetch_related(
    'account_tree_total', 'account_tree_regular',
    Prefetch('account_tree_header', queryset=AccountTreeHeader.objects.select_related(
        'associated_account_from_chart_of_accounts', 'associated_total_account_tree_account__associated_account_from_chart_of_accounts'
    ).prefetch_related(
        'associated_regular_account_tree_accounts',
        Prefetch('associated_regular_account_tree_accounts__associated_account_from_chart_of_accounts__general_ledger',
                 queryset=GeneralLedger.objects.select_related(
                 ).filter(Q(
                     accounts_payable_line_item__property__pk__in=property_pks,
                     journal_line_item__property__pk__in=property_pks,
                     _connector=Q.OR,
                 ), date_entered__date__gte=start_date, date_entered__date__lte=end_date).order_by('date_entered'), to_attr='range_gl'),
    )),
).get(pk=custom_report.pk)

步骤3：遍历取值

header_accounts = custom_report.account_tree_header.all()

for header_account in header_accounts:
    for regular_account in header_account.associated_regular_account_tree_accounts.all():
        gl_account = regular_account.associated_account_from_chart_of_accounts
        gl_entries = gl_account.range_gl
        # 从字典中获取对应账户的前期余额
        balance_data = prior_balance_dict.get(gl_account.pk, {'prior_credit': 0, 'prior_debit': 0})
        prior_credit = balance_data['prior_credit']
        prior_debit = balance_data['prior_debit']

总结 #

不要在 Prefetch 的 queryset 参数中使用 aggregate()，因为它返回的是字典而非QuerySet。推荐优先使用方案一，通过Subquery将聚合逻辑整合到主查询中，最大程度减少数据库查询次数；如果对Subquery不熟悉，方案二的预计算字典方式也能解决问题，逻辑更易理解。

内容的提问来源于stack exchange，提问作者ViaTech