Django/DRF查询Order列表时关联最新OrderStatus的实现问题

我有一个返回Order对象列表的API端点，模型定义如下：

Order模型：

class Order(models.Model):
    item_name = models.CharField(max_length=120)

每个Order对应多条OrderStatus记录（类似包裹追踪，状态变更时创建带时间戳的新记录），OrderStatus模型：

class OrderStatus(models.Model):
    status = models.CharField(max_length=30)
    timestamp = models.DateTimeField(auto_now_add=True)
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="order_statuses")

现在需要查询Order列表时，返回每个Order关联的最新OrderStatus，期望返回格式：

{
    "count": 2,
    "next": null,
    "previous": null,
    "results": [
        {
            "item_name": "Something",
            "order_status": {
                "status": "Processing",
                "timestamp": "2023-09-25T12:31:12Z"
            }
        },
        {
            "item_name": "Other Thing",
            "order_status": {
                "status": "Completed",
                "timestamp": "2023-08-15T10:41:32Z"
            }
        }
    ]
}

试过用prefetch_related()，但它适合反向查询OrderStatus关联Order的场景，满足不了当前需求，求可行实现方法。

方法1：annotate+子查询（推荐，无N+1查询） #

通过Django的子查询功能，直接在Order查询阶段关联最新的OrderStatus记录，避免额外的数据库请求。

视图代码： #

from django.db.models import OuterRef, Subquery
from rest_framework import generics
from .models import Order, OrderStatus
from .serializers import OrderSerializer

class OrderListView(generics.ListAPIView):
    serializer_class = OrderSerializer

    def get_queryset(self):
        # 构造子查询：筛选当前Order的最新状态记录
        latest_status_subquery = OrderStatus.objects.filter(
            order=OuterRef('pk')
        ).order_by('-timestamp')[:1]

        # 给每个Order附加最新状态的字段
        return Order.objects.annotate(
            latest_status=Subquery(latest_status_subquery.values('status')),
            latest_timestamp=Subquery(latest_status_subquery.values('timestamp'))
        )

序列化器代码： #

from rest_framework import serializers
from .models import Order

class OrderSerializer(serializers.ModelSerializer):
    order_status = serializers.SerializerMethodField()

    class Meta:
        model = Order
        fields = ['item_name', 'order_status']

    def get_order_status(self, obj):
        if hasattr(obj, 'latest_status') and hasattr(obj, 'latest_timestamp'):
            return {
                'status': obj.latest_status,
                'timestamp': obj.latest_timestamp
            }
        return None

方法2：自定义Prefetch对象 #

如果偏好使用prefetch_related，可以自定义Prefetch规则，只预取每个Order的最新状态记录，而非全部状态。

视图代码： #

from django.db.models import Prefetch
from rest_framework import generics
from .models import Order, OrderStatus
from .serializers import OrderSerializer

class OrderListView(generics.ListAPIView):
    serializer_class = OrderSerializer

    def get_queryset(self):
        # 定义预取规则：只获取每个Order的最新1条状态
        latest_status_prefetch = Prefetch(
            'order_statuses',
            queryset=OrderStatus.objects.order_by('-timestamp')[:1],
            to_attr='latest_order_status'
        )

        return Order.objects.prefetch_related(latest_status_prefetch)

序列化器代码： #

from rest_framework import serializers
from .models import Order

class OrderSerializer(serializers.ModelSerializer):
    order_status = serializers.SerializerMethodField()

    class Meta:
        model = Order
        fields = ['item_name', 'order_status']

    def get_order_status(self, obj):
        if obj.latest_order_status:
            status = obj.latest_order_status[0]
            return {
                'status': status.status,
                'timestamp': status.timestamp
            }
        return None

方法3：序列化器直接查询（仅小数据量场景） #

如果数据量很小，可以直接在序列化器中查询最新状态，但这种方法会触发N+1查询（每个Order单独查一次OrderStatus），不推荐大数据量使用。

序列化器代码： #

from rest_framework import serializers
from .models import Order

class OrderSerializer(serializers.ModelSerializer):
    order_status = serializers.SerializerMethodField()

    class Meta:
        model = Order
        fields = ['item_name', 'order_status']

    def get_order_status(self, obj):
        latest_status = obj.order_statuses.order_by('-timestamp').first()
        if latest_status:
            return {
                'status': latest_status.status,
                'timestamp': latest_status.timestamp
            }
        return None

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