如何为Django QuerySet标注多值？获取指定日期商品最近两次价格

针对每日25000+Item的场景，要避免N+1查询和逐个Item遍历，结合PostgreSQL的JSON聚合能力，用Django ORM可实现一次性查询出所有结果。核心思路是利用ArraySubquery结合PostgreSQL的jsonb_build_object和array_agg，将每个Item的最近2条Price记录打包为JSON数组。

完整代码实现 #

from django.db.models import Subquery, OuterRef, Func, F
from django.db.models.fields import ArrayField, JSONField

# 自定义函数：调用PostgreSQL的jsonb_build_object构建JSON对象
class JsonBuildObject(Func):
    function = 'jsonb_build_object'
    template = "%(function)s(%(expressions)s)"

# 指定目标查询日期
target_date = "2023-04-13"

# 第一步：筛选出指定日期有Price记录的Item
target_items = Item.objects.filter(
    price__timestamp__date=target_date
).distinct()

# 第二步：构建子查询，获取每个Item的最近2条Price记录（按时间倒序）
latest_prices_subquery = Subquery(
    Price.objects.filter(
        item=OuterRef('pk')
    ).order_by('-timestamp')[:2].annotate(
        price_entry=JsonBuildObject(
            'price__id', F('id'),
            'price__per_item', F('per_item')
        )
    ).values('price_entry'),
    output_field=ArrayField(JSONField())
)

# 第三步：关联子查询，聚合最终结果
result = target_items.annotate(
    latest_prices=latest_prices_subquery
).values('id', 'latest_prices')

# 转换为列表格式（直接可用）
final_result = list(result)

代码说明 #

1. 筛选指定日期的Item：通过price__timestamp__date=target_date过滤出当天有Price记录的Item，distinct()避免因多条Price导致Item重复。
2. 子查询构建JSON数组：
   • 针对外层每个Item（OuterRef('pk')），筛选其所有Price并按timestamp倒序排列，取前2条。
   • 用JsonBuildObject将Price的id和per_item打包为JSON对象，键名直接对应你期望的price__id和price__per_item。
   • 借助ArraySubquery将这些JSON对象聚合为数组，作为latest_prices字段返回，解决了之前子查询返回多行的报错问题。
3. 性能优化：全程仅执行2次SQL查询，完全无需遍历每个Item或单独查询Price，适配大数量级场景。

测试结果示例 #

查询2023-04-13时，final_result会输出：

[
{'id': '1i', 'latest_prices': [{'price__id': '8p', 'price__per_item': 12.0}, {'price__id': '4p', 'price__per_item': 11.0}]},
{'id': '2i', 'latest_prices': [{'price__id': '9p', 'price__per_item': 3.5}, {'price__id': '6p', 'price__per_item': 2.0}]},
{'id': '3i', 'latest_prices': [{'price__id': '10p', 'price__per_item': 15.0}, {'price__id': '7p', 'price__per_item': 14.5}]}
]

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