Django ORM: move filter after annotate subquery

[Aivan Fouren]

This Django ORM statement:

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)

Leads to the following SQL query:

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;

As you can see, the `date_created__lte` filter gets applied on the inner query. Is it possible to control statement location preciser and move the filter outside, like `ord`?

I also asked this on StackOverflow, got an advice to use `Subquery` object, but as far as I know this class is used for filtering or annotated selecting values (SQL SELECT, WHERE), but I need a subquery to use inside FROM statement, so that I can postprocess calculated results.

[Aivan Fouren]

I found a way to achieve the results I want: applying a filter, `date_created__lte` in this example, outside of annotated query:

sub = Model.objects.all() \

  .annotate(ord=Window(

    expression=RowNumber(),

    partition_by=F('related_id'),

    order_by=[F('date_created').desc()] ) ) \

   .filter(ord=1)

result = Model.objects.all() \

  .filter(id__in=sub.values_list('id')) \

  .filter(date_created__lte=some_datetime)

However, this is not a code I want, it's bad from performance point of view due to HASH JOIN. Of course, I can write a raw SQL query, parse values by Django ORM, but this looks too heavy for a simple nested subquery. So, a better version is appreciated 🙏