如何控制Django ORM过滤器位置？将date_created__lte移至annotate后

现有一段Django ORM代码：

Model.objects.all() \
    .annotate(
        ord=Window(
            expression=RowNumber(),
            partition_by=F('related_id'),
            order_by=[F("date_created").desc()]
        )
    ) \
    .filter(ord=1) \
    .filter(date_created__lte=some_datetime)

它生成的SQL如下：

SELECT * 
FROM (
    SELECT 
      id, related_id, values, date_created,
      ROW_NUMBER() OVER (
        PARTITION BY related_id
        ORDER BY date_created DESC
      ) AS ord
    FROM model_table
    WHERE date_created <= '2022-02-24 00:00:00+00:00'
)
WHERE ord = 1

可以看到date_created__lte=some_datetime过滤器被自动放到了子查询的WHERE子句里，现在需要把这个过滤器移到外层，和ord=1处于同一层级，让最终SQL变成这样：

SELECT * 
FROM (
    SELECT 
      id, related_id, values, date_created,
      ROW_NUMBER() OVER (
        PARTITION BY related_id
        ORDER BY date_created DESC
      ) AS ord
    FROM model_table
)
WHERE ord = 1 AND date_created <= '2022-02-24 00:00:00+00:00'

实现方法 #

要精确控制过滤器位置，需要把带窗口函数的逻辑作为子查询，在外层查询中同时应用两个过滤条件，具体代码如下：

from django.db.models import Subquery, OuterRef

# 定义包含窗口函数的子查询，用于获取每条记录的ord值
subquery = Model.objects.annotate(
    ord=Window(
        expression=RowNumber(),
        partition_by=F('related_id'),
        order_by=F("date_created").desc()
    )
).filter(id=OuterRef('id')).values('ord')

# 外层查询：先关联子查询的ord字段，再同时过滤ord和date_created
result = Model.objects.annotate(
    ord=Subquery(subquery)
).filter(
    ord=1,
    date_created__lte=some_datetime
)

或者另一种写法，先构建完整的子查询集，再在外层过滤：

# 先构建带窗口函数的完整子查询
sub_queryset = Model.objects.annotate(
    ord=Window(
        expression=RowNumber(),
        partition_by=F('related_id'),
        order_by=[F("date_created").desc()]
    )
)

# 外层通过id关联子查询，同时应用两个过滤条件
result = Model.objects.filter(
    id__in=sub_queryset.values('id')
).annotate(
    ord=Subquery(sub_queryset.filter(id=OuterRef('id')).values('ord'))
).filter(
    ord=1,
    date_created__lte=some_datetime
)

这两种写法都会让date_created__lte和ord=1同时出现在外层查询的WHERE子句中，满足需求。

注意：这种方式会生成嵌套子查询，实际使用时要根据数据量评估性能，必要时给date_created、related_id字段添加索引优化查询速度。

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