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.
