如何用Django ORM的annotate为Sale关联匹配日期的IncomeStream值作为CPA

可以纯通过Django ORM的annotate实现该需求，核心思路是结合子查询和外层字段引用实现带日期范围条件的匹配，具体实现如下：

1. 导入依赖模块 #

需要用到Django ORM的Q、OuterRef、Subquery三个工具：

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

2. 构造匹配规则的子查询 #

先定义针对IncomeStream的筛选逻辑，匹配当前Sale对应产品、且日期范围包含Sale创建时间的记录：

# 子查询：匹配当前Sale对应的有效IncomeStream
valid_income_stream = IncomeStream.objects.filter(
    # 关联同一个Product
    product=OuterRef("product"),
    # from_date为空等价于负无穷，否则要求小于等于Sale创建时间
    Q(from_date__isnull=True) | Q(from_date__lte=OuterRef("created_at")),
    # to_date为空等价于正无穷，否则要求大于等于Sale创建时间
    Q(to_date__isnull=True) | Q(to_date__gte=OuterRef("created_at")),
# 仅取value字段，且因为无范围重叠，取第一条即可保证唯一
).values("value")[:1]

3. 给Sale查询新增cpa字段 #

通过annotate将子查询结果注入为cpa字段：

sales_queryset = Sale.objects.annotate(
    cpa=Subquery(valid_income_stream)
)

效果验证 #

你给出的示例数据会按照规则正确匹配：

• created_at为2019-01-01的TEST产品Sale，会匹配到from为NULL、to为2021-10-09的IncomeStream，cpa值为5$
• created_at为2021-11-01的TEST产品Sale，会匹配到from为2021-10-10、to为NULL的IncomeStream，cpa值为10$

注意事项 #

该方案兼容Django 1.11及以上版本，返回的cpa字段类型和IncomeStream.value的MoneyField类型完全一致，无需额外类型转换。

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