如何在Django中将kpi方法改写为模型关联关系

步骤1：给PositionKPI添加唯一约束 #

首先确保每个Position和Metric的组合唯一，让每个EmployeeKPI能对应唯一的PositionKPI：

class PositionKPI(BaseModel):
    position = models.ForeignKey(Position, on_delete=models.CASCADE)
    metric = models.ForeignKey(Metric, on_delete=models.CASCADE)
    expectation = models.FloatField()

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['position', 'metric'], 
                name='unique_position_metric'
            )
        ]

步骤2：给EmployeeKPI添加关联字段 #

在EmployeeKPI中新增ForeignKey字段关联PositionKPI，禁用数据库层面的外键约束（因为关联依赖两个字段匹配），同时限制后台可选范围：

class EmployeeKPI(BaseModel):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    metric = models.ForeignKey(Metric, on_delete=models.CASCADE)
    value = models.FloatField()

    kpi = models.ForeignKey(
        PositionKPI,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        db_constraint=False,
        limit_choices_to=lambda self: {
            'position': self.employee.position,
            'metric': self.metric
        } if self.employee and self.metric else {}
    )

步骤3：用信号自动填充关联字段 #

通过Django信号在保存EmployeeKPI时自动匹配对应PositionKPI，无需手动设置：

from django.db.models.signals import pre_save
from django.dispatch import receiver

@receiver(pre_save, sender=EmployeeKPI)
def auto_set_kpi(sender, instance, **kwargs):
    if instance.employee and instance.metric:
        instance.kpi = PositionKPI.objects.filter(
            position=instance.employee.position,
            metric=instance.metric
        ).first()

替代方案：用属性替代数据库字段 #

如果不想新增数据库字段，可改用property实现，同时支持ORM预加载避免N+1查询：

class EmployeeKPI(BaseModel):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    metric = models.ForeignKey(Metric, on_delete=models.CASCADE)
    value = models.FloatField()

    @property
    def kpi(self):
        if not hasattr(self, '_kpi_cache'):
            self._kpi_cache = PositionKPI.objects.filter(
                position=self.employee.position,
                metric=self.metric
            ).first()
        return self._kpi_cache

# 预加载示例（避免重复查询）
from django.db.models import Prefetch

employee_kpis = EmployeeKPI.objects.all().prefetch_related(
    Prefetch(
        'employee__position__positionkpi_set',
        queryset=PositionKPI.objects.all(),
        to_attr='_position_kpis'
    )
)

# 手动映射缓存
for ekpi in employee_kpis:
    ekpi._kpi_cache = next(
        (pkpi for pkpi in ekpi.employee.position._position_kpis if pkpi.metric == ekpi.metric),
        None
    )

以上方案均可实现你需要的关联逻辑，对应给出的SQL查询效果。

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