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, marco....@gmail.com 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 django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
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.
