如何先按注解字段排序Django QuerySet再添加行号注解？

你遇到的问题核心在于：Django生成的SQL中，ROW_NUMBER()函数的OVER()子句没有指定排序逻辑，导致行号是基于数据库原始返回顺序生成的，而外层的ORDER BY只是最后对结果排序，这就造成行号和最终排序后的结果不匹配。

解决方案一：为Window函数指定排序规则 #

直接在RowNumber()对应的Window表达式中添加与外层一致的排序规则，让行号生成逻辑和最终排序逻辑对齐。修改后的代码如下：

from django.db.models import Case, When, Value, Q, Window, RowNumber, F

queryset.annotate(
    complex_order=Case(
        When(
            Q(foo__type=FooType.ONE.value) | Q(foo__isnull=True),
            then=Value(1),
        ),
        default=Value(0),
    ),
).annotate(
    number=Window(
        expression=RowNumber(),
        # 这里指定行号生成时的排序规则，和最终排序一致
        order_by=[F('-complex_order'), F('order'), F('id')]
    )
).order_by('-complex_order', 'order', 'id')

此时生成的SQL会将排序逻辑嵌入ROW_NUMBER()的OVER子句中：

SELECT "table"."id", ... 
CASE WHEN ("foo"."type" = ONE OR ("foo" IS NULL)) THEN 1 ELSE 0 END AS "complex_order", 
ROW_NUMBER() OVER (ORDER BY "complex_order" DESC, "table"."order" ASC, "table"."id" ASC) AS "number" 
FROM ...
ORDER BY "complex_order" DESC, "table"."order" ASC, "table"."id" ASC

这样行号会按照指定的排序规则生成，和最终结果的顺序完全匹配。

解决方案二：通过子查询/CTE先完成排序再添加行号 #

如果希望严格先执行排序再生成行号，可以将排序后的QuerySet包装为子查询或者CTE（公共表表达式），再基于这个结果添加行号：

子查询方式

from django.db.models import Subquery

# 先构建带排序注解的QuerySet
base_qs = queryset.annotate(
    complex_order=Case(
        When(
            Q(foo__type=FooType.ONE.value) | Q(foo__isnull=True),
            then=Value(1),
        ),
        default=Value(0),
    ),
).order_by('-complex_order', 'order', 'id')

# 基于子查询结果添加行号
final_qs = base_qs.model.objects.filter(
    id__in=Subquery(base_qs.values('id'))
).annotate(
    complex_order=Case(
        When(
            Q(foo__type=FooType.ONE.value) | Q(foo__isnull=True),
            then=Value(1),
        ),
        default=Value(0),
    ),
    number=Window(expression=RowNumber())
).order_by('-complex_order', 'order', 'id')

CTE方式（Django 1.11+支持）

from django.db.models import CTEQuerySet

# 创建CTE，包含排序注解和排序逻辑
cte = queryset.annotate(
    complex_order=Case(
        When(
            Q(foo__type=FooType.ONE.value) | Q(foo__isnull=True),
            then=Value(1),
        ),
        default=Value(0),
    ),
).order_by('-complex_order', 'order', 'id').cte()

# 基于CTE添加行号
final_qs = cte.queryset.annotate(
    number=Window(expression=RowNumber())
)

解决方案三：Python层面添加行号（适合小数据量） #

如果数据量不大，可以在数据库查询完成后，在Python遍历过程中添加行号，这种方式更简单直观：

sorted_qs = queryset.annotate(
    complex_order=Case(
        When(
            Q(foo__type=FooType.ONE.value) | Q(foo__isnull=True),
            then=Value(1),
        ),
        default=Value(0),
    ),
).order_by('-complex_order', 'order', 'id')

# 遍历QuerySet并添加行号
for idx, obj in enumerate(sorted_qs, start=1):
    obj.number = idx

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