ORM - Subquery / Window

[marco....@gmail.com]

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

[marco....@gmail.com]

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!