Question 88833 in PostgreSQL Question posted in
The official documentation can be found here.

Django ORM: move filter after annotate subquery – Postgresql

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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)

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 answer is appreciated 🙏


  2. 0

    In your case, you can move the date_created__lte filter before the annotate method to make sure it is applied to the base query. This will improve the query performance too because the annotate method works on a smaller, filtered dataset.

    Model.objects.filter(date_created__lte=some_datetime) 
    .annotate(
        ord=Window(
            expression=RowNumber(),
            partition_by=F('related_id'),
            order_by=[F("date_created").desc()]
        )
    ) 
    .filter(ord=1)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search