如何使用DRF OrderingFilter实现排序并解决distinct匹配报错

问题背景 #

需要基于DRF内置OrderingFilter，针对DateTimeField类型字段实现模型排序。手动编写排序逻辑可正常运行，替换为官方OrderingFilter后触发数据库错误，相关代码如下：

模型定义（Models.py） #

from django.db import models
from typing import Union

class TimeStampedModel(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    class Meta:
        abstract = True

class Author(models.Model):
    name = models.CharField(max_length=255)

class DropTemplate(TimeStampedModel):
    title = models.TextField(null=True, blank=True)
    author = models.ForeignKey(Author, on_delete=models.PROTECT)

    @property
    def earliest_start_time(self) -> Union[None, str]:
        earliest_collection = (
            self.collectiontemplates.filter(drop_template_id=self.id, visible_start_time__isnull=False)
            .only("visible_start_time")
            .order_by("visible_start_time")
            .first()
        )
        if earliest_collection is None:
            return None
        else:
            return earliest_collection.visible_start_time


class CollectionTemplate(TimeStampedModel):
    drop_template = models.ForeignKey(
        DropTemplate, related_name="collectiontemplates", on_delete=models.PROTECT
    )
    visible_start_time = models.DateTimeField(null=True, blank=True)

正常运行的手动排序实现 #

from rest_framework import viewsets
from django.db.models import F
from .models import DropTemplate
from .serializers import DropTemplateSerializer
from django_filters.rest_framework import DjangoFilterBackend

class DropTemplateViewSet(viewsets.ModelViewSet):
    serializer_class = DropTemplateSerializer
    filter_backends = [DjangoFilterBackend]
    filterset_fields = ["status"]

    def get_queryset(self):
        drop_templates = DropTemplate.objects.annotate(start_time=F("collectiontemplates__visible_start_time")).filter(author__id=self.kwargs["author_pk"]).distinct("id")
        if "ordering" in self.request.query_params:
            return drop_templates.order_by("id", f"{self.request.query_params['ordering']}")
        else:
            return drop_templates

说明：使用distinct("id")是因为一对多关联表注解后，QuerySet会生成重复的DropTemplate记录，需要按主键去重。手动排序时显式将id放在order_by字段最前，符合PostgreSQL的DISTINCT ON语法规则。

触发报错的OrderingFilter实现 #

from rest_framework.filters import OrderingFilter

class DropTemplateViewSet(viewsets.ModelViewSet):
    serializer_class = DropTemplateSerializer
    filter_backends = [DjangoFilterBackend, OrderingFilter]
    filterset_fields = ["status"]
    ordering_fields = ["title", "start_time"]
    ordering = ["start_time"]

    def get_queryset(self):
        return DropTemplate.objects.annotate(start_time=F("collectiontemplates__visible_start_time")).filter(author__id=self.kwargs["author_pk"]).distinct("id")

抛出的数据库错误：

SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT COUNT(*) FROM (SELECT DISTINCT ON ("author_droptem... ^ )

报错根因 #

PostgreSQL中使用DISTINCT ON(字段列表)语法时，强制要求ORDER BY子句的开头字段必须和DISTINCT ON指定的字段顺序、内容完全一致。
DRF原生OrderingFilter执行排序时，只会将配置的允许排序字段直接拼接到order_by中，不会自动将distinct("id")指定的主键字段放在排序字段最前端；同时DRF生成分页count查询时，会保留排序逻辑，最终触发SQL语法错误。

修复方案 #

方案1：自定义OrderingFilter适配DISTINCT ON规则 #

重写OrderingFilter的filter_queryset方法，强制将distinct("id")指定的主键字段放在排序参数最前面，和手动实现的逻辑完全对齐：

from rest_framework.filters import OrderingFilter

class DistinctOnOrderingFilter(OrderingFilter):
    def filter_queryset(self, request, queryset, view):
        qs = super().filter_queryset(request, queryset, view)
        # 仅针对distinct("id")的查询做适配
        if qs.query.distinct_fields == ("id",):
            original_order = list(qs.query.order_by)
            # 排除已存在的id排序字段，将id放在排序第一位
            cleaned_order = [f for f in original_order if f.lstrip("-") != "id"]
            qs = qs.order_by("id", *cleaned_order)
        return qs

替换ViewSet中配置的排序过滤器即可：

class DropTemplateViewSet(viewsets.ModelViewSet):
    serializer_class = DropTemplateSerializer
    # 替换为自定义过滤器
    filter_backends = [DjangoFilterBackend, DistinctOnOrderingFilter]
    filterset_fields = ["status"]
    ordering_fields = ["title", "start_time"]
    ordering = ["start_time"]

    def get_queryset(self):
        return DropTemplate.objects.annotate(
            start_time=F("collectiontemplates__visible_start_time")
        ).filter(
            author__id=self.kwargs["author_pk"]
        ).distinct("id")

方案2：优化注解逻辑从根源避免DISTINCT #

当前使用F("collectiontemplates__visible_start_time")做注解时，会因为一对多关联生成重复记录，和模型属性earliest_start_time取最早开始时间的逻辑也不一致。改用Min聚合注解后，每个DropTemplate只会生成一条聚合结果，不需要再使用distinct("id")，从根源避免语法冲突：

from django.db.models import Min

class DropTemplateViewSet(viewsets.ModelViewSet):
    serializer_class = DropTemplateSerializer
    filter_backends = [DjangoFilterBackend, OrderingFilter]
    filterset_fields = ["status"]
    ordering_fields = ["title", "start_time"]
    ordering = ["start_time"]

    def get_queryset(self):
        # 用Min聚合取最早的visible_start_time，和模型earliest_start_time逻辑一致，无重复记录
        return DropTemplate.objects.annotate(
            start_time=Min("collectiontemplates__visible_start_time")
        ).filter(
            author__id=self.kwargs["author_pk"]
        )

方案2为更优实现，既符合业务逻辑取最早开始时间的需求，又避免了DISTINCT ON带来的排序限制。

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