如何通过Django ORM跨子模型计算指定客户的收入份额

需求与问题描述 #

• 计算特定客户在指定贷款中的各类收入份额，规则：
  • 当Income.proportions = 'EQUAL'时，客户与贷款的所有申请人平分该收入；
  • 当Income.proportions = 'SPECIFIED'时，使用IncomeOwner中该客户对应的percent字段计算份额。
• 当前实现中，SPECIFIED场景下无法精准获取当前传入客户的percent值，需要优化。

相关模型代码 #

class Client(Model):
   ...

class Loan(Model):
    applicants = ManyToManyField(Client)

class Income(Model):
    loan = ForeignKey(Loan)
    amount = DecimalField()
    income_type = CharField(choices=['INVESTMENT', ...])
    proportions = CharField(default='EQUAL', choices=['EQUAL', 'SPECIFIED'])

class IncomeOwner(Model):
    income = ForeignKey(Income, related_name='owners')
    client = ForeignKey(Client)
    percent = DecimalField()

当前实现的问题点 #

原代码中applicant_share_percent的Case语句直接使用F('owners__percent')，会取出该Income关联的所有IncomeOwner的percent值，无法精准定位到当前传入的客户：

applicant_share_percent = Case(
    When(proportions='SPECIFIED', then=F('owners__percent')),  # 这里会拿到所有关联owner的percent，不符合需求
    default=equal_share_percent
)

解决方案：使用SubQuery精准筛选 #

确实需要通过SubQuery来获取指定客户对应的percent值，修改后的代码如下：

from django.db.models import Subquery, OuterRef

def get_income(loan, client):
    # 构造SubQuery：筛选当前Income（OuterRef('pk')）和指定client对应的percent
    client_percent_subquery = Subquery(
        IncomeOwner.objects.filter(
            income=OuterRef('pk'),
            client=client
        ).values('percent')[:1]
    )

    equal_share_percent = ExpressionWrapper(100 / F('num_applicants'), output_field=PercentageValueField())
    applicant_share_percent = Case(
        When(proportions='SPECIFIED', then=client_percent_subquery),
        default=equal_share_percent
    )

    applicant_amount = ExpressionWrapper(
        F('applicant_percent') / 100 * F('amount'), output_field=models.DecimalField(decimal_places=2)
    )
    
    loan_income_summary = Income.objects.filter(
        loan=loan
    ).annotate(
        num_applicants=Count('applicants', output_field=models.IntegerField()),
    ).annotate(
        applicant_percent=applicant_share_percent
    ).aggregate(
        total_investment=Sum(
            applicant_amount,
            filter=Q(income_type='INVESTMENT'),
            output_field=models.DecimalField(decimal_places=2)
        )
    )
    return loan_income_summary

代码说明 #

1. Subquery结合OuterRef('pk')，关联外层查询的Income实例，同时过滤出指定client的IncomeOwner记录；
2. values('percent')[:1]确保只返回单个percent值（因为一个Income对应一个客户只能有一条IncomeOwner记录）；
3. 这样在SPECIFIED场景下，就能精准获取当前客户对应的百分比，避免了多关联带来的错误数据。

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