CODEWITHSUNDEEP
djangopostgresqldjango-modelsdjango-rest-frameworkdjango-orm
Mohan
Mohan

Reputation: 4829

How to write subquery in From clause in django ORM

I wnat to write this query using django orm

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

My current ORm query

EmpSalary.objects.values('depname', 'empno', 'salary', 'enroll_date').annotate(
pos= Window(
                expression=RowNumber(),
                partition_by=[F('depname')],
                order_by=F('salary').desc(),
            )
)

The above ORM queryset roughly translates to the inner query

SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos
     FROM empsalary

I want to know how i can get the whole inner query in the FROM clause of the outer query.

Upvotes: 7

Views: 1846

Answers (1)

dirkgroten
dirkgroten

Reputation: 20672

Indeed, it's not possible to use a Window query inside a WHERE clause. So instead, use a Subquery:

from django.db.models import OuterRef, Subquery

top_salaries = EmpSalary.objects.filter(
    depname=OuterRef('depname')
).order_by('-salary')[:3]
result = EmpSalary.objects.filter(
    pk__in=Subquery(top_salaries.values('pk'))
).values('depname', 'empno', 'salary', 'enroll_date')

Upvotes: 2

Related Questions