Re: Django ORM: move filter after annotate subquery

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 🙏

On Friday, April 28, 2023 at 4:52:16 PM UTC+3 Aivan Fouren wrote:

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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9afee8b7-ccb3-4143-8fc5-af05626c6e8en%40googlegroups.com.