DRF中Annotate字段高效排序的两大实现问题

问题背景 #

你定义了如下Django模型：

class AAA(models.Model):
    name = models.CharField(max_length=300, null=False, blank=False)

class BBB(models.Model):
    aaa = models.ForeignKey(AAA, on_delete=models.CASCADE, null=False, blank=False)
    content = models.CharField(max_length=300, null=False, blank=False)

class CCC(models.Model):
    aaa = models.ForeignKey(AAA, on_delete=models.CASCADE, null=False, blank=False)
    name = models.CharField(max_length=300, null=False, blank=False)

并在视图中配置了排序与分页：

class AAAListCreateView(generics.ListAPIView):
    class AAAPagination(CursorPagination):
        page_size = 2

    queryset = AAA.objects.all().annotate(
        b_count=Count("bbb", distinct=True),
        c_count=Count("ccc", distinct=True),
        total=F('b_count')+('c_count')
    )
    serializer_class = AAAListSerializer
    pagination_class = AAAPagination
    filter_backends = [DjangoFilterBackend, OrderingFilter]
    filterset_class = AAAListFilterSet
    ordering_fields = ['b_count', 'c_count', 'total']
    ordering = 'b_count'

针对你提出的两个问题，解决方案如下：

1. 让所有排序字段默认按降序排列 #

默认的OrderingFilter需要手动加-前缀实现降序，要实现默认降序，只需自定义一个OrderingFilter子类，重写get_ordering方法，自动为未指定方向的字段添加降序前缀：

from rest_framework.filters import OrderingFilter

class DefaultDescOrderingFilter(OrderingFilter):
    def get_ordering(self, request, queryset, view):
        ordering = super().get_ordering(request, queryset, view)
        if not ordering:
            # 无自定义排序时，把默认排序转成降序
            return [f"-{field}" for field in view.ordering]
        # 处理每个排序字段：无+/-前缀则默认加-
        processed_ordering = []
        for field in ordering:
            if field.startswith(('+', '-')):
                processed_ordering.append(field)
            else:
                processed_ordering.append(f"-{field}")
        return processed_ordering

然后在视图中替换默认的OrderingFilter：

class AAAListCreateView(generics.ListAPIView):
    # ... 保留原有其他代码 ...
    filter_backends = [DjangoFilterBackend, DefaultDescOrderingFilter]
    ordering_fields = ['b_count', 'c_count', 'total']
    ordering = 'b_count'  # 会自动转为 '-b_count' 作为默认排序

这样用户请求时，传?ordering=b_count就会按降序排列，若要升序只需传?ordering=+b_count即可。

2. 优化annotate查询，仅统计当前排序需要的字段 #

目前提前annotate所有字段会导致每次查询都计算b_count、c_count和total，即使用户只按其中一个字段排序。可以重写视图的get_queryset方法，根据当前请求的排序参数，动态添加需要的annotate字段：

from django.db.models import Count, F
from rest_framework import generics

class AAAListCreateView(generics.ListAPIView):
    class AAAPagination(CursorPagination):
        page_size = 2

    # 仅保留基础queryset，不提前annotate
    queryset = AAA.objects.all()
    serializer_class = AAAListSerializer
    pagination_class = AAAPagination
    filter_backends = [DjangoFilterBackend, DefaultDescOrderingFilter]
    filterset_class = AAAListFilterSet
    ordering_fields = ['b_count', 'c_count', 'total']
    ordering = 'b_count'

    def get_queryset(self):
        queryset = super().get_queryset()
        # 获取当前请求的排序参数，无参数时用默认排序
        ordering = self.request.query_params.get(self.ordering_param, self.ordering)
        # 拆解排序字段，去掉方向前缀
        order_fields = [field.lstrip('+-') for field in ordering.split(',')]
        
        # 根据需要的字段动态添加annotate
        annotate_kwargs = {}
        if 'b_count' in order_fields:
            annotate_kwargs['b_count'] = Count("bbb", distinct=True)
        if 'c_count' in order_fields:
            annotate_kwargs['c_count'] = Count("ccc", distinct=True)
        if 'total' in order_fields:
            # 计算total需要先确保b_count和c_count已被annotate
            if 'b_count' not in annotate_kwargs:
                annotate_kwargs['b_count'] = Count("bbb", distinct=True)
            if 'c_count' not in annotate_kwargs:
                annotate_kwargs['c_count'] = Count("ccc", distinct=True)
            annotate_kwargs['total'] = F('b_count') + F('c_count')
        
        return queryset.annotate(**annotate_kwargs)

这样处理后，查询只会计算当前排序用到的字段，比如用户按total排序时才会计算b_count、c_count和total，按b_count排序时只计算b_count，大幅减少不必要的统计查询，提升效率。

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