As suggested i'm writing what i've already tried:
Raw SQL using the query written in the first message, selecting only the id
field and passing it to the ORM as id__in=ids. Slow as hell, unusable.
Declared a WIndow function to use as filter:
Article.objects.annotate(max_rev=Window(expression=Max("revision"),
partition_by=F("pn"))).filter(revision=F("max_rev"))
But Django complained that i cannot use a window function in a where clause
(that's correct).
Then i've tried to use the window as subquery:
window_query = Article.objects.annotate(max_rev=Window(expression=Max(
"revision"), partition_by=F("pn")))
result = Article.objects.filter(revision= Subquery(window_query)
I've tried also with OuterRef, to use the max_rev annotation as a join, no
luck.
I'm out of ideas!
Il giorno giovedì 7 giugno 2018 14:44:41 UTC+2, [email protected] ha
scritto:
>
> Hello everyone,
>
> i want to extract the records with Max("revision") from a table like this:
>
> pn1 rev1 description
> pn1 rev2 description
> pn2 rev1 anotherdescription
> pn1 rev3 description
> pn2 rev2 anotherdescription
>
> The first column is a part number, the second is its revision index (which
> is created every time the part number is modifyied).
> That is quite easy in pure SQL:
>
> SELECT
> id,
> pn,
> revision,
> description
> FROM (SELECT
> id,
> pn,
> revision,
> MAX(revision)
> OVER (
> PARTITION BY pn ) max_rev,
> description
> FROM en_articles) maxart
> WHERE revision = max_rev;
>
> I cannot understand how to do the same with Django's ORM, i've tried every
> combination of Subquery/Window without getting anywhere.
> Does anyone know how to do it?
>
> Thanks in advance
> Marco
>
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/104dab1e-c12c-47f7-8dbc-3c2a1931aaeb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
