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