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.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/74df5810-9bb7-4926-9904-73b86f06f174n%40googlegroups.com.

Re: Django ORM: move filter after annotate subquery

[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 🙏

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 
> <https://stackoverflow.com/q/76128230/5308802>, 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+unsubscr...@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.