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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/9afee8b7-ccb3-4143-8fc5-af05626c6e8en%40googlegroups.com.
