15

Answering this question, I found out that window functions are not allowed to combine with filter (technically, they are, but filter clause affects the window). There is a hint to wrap window function in an inner query, so that final SQL looks like this (as I understand):

SELECT * FROM (
    SELECT *, *window_function* FROM TABLE)
WHERE *filtering_conditions*

The question is: how can I write this query with Django ORM?

Roman Yakubovich
  • 893
  • 1
  • 7
  • 18
  • I'm having the same issue. It is possible in postgres (see bottom of documentation), but I have no clue how to translate this into the ORM. Has anyone an idea? – ilse2005 Apr 21 '20 at 10:10
  • @ilse2005 The given expressions is more like a *Subquery* in the form of *`SELECT ... FROM (SUBQUERY_EXPRESSION) as foo`*. Fortunately, Django has the [**`Subquery(...)`**](https://docs.djangoproject.com/en/3.0/ref/models/expressions/#subquery-expressions) expression, but it doesn't provide the functionality to specify the ***`FROM`*** clause – JPG Apr 22 '20 at 18:04
  • as far as I understood, this is a question of [*How to get a subquery in FROM clause in Django ORM?*](https://stackoverflow.com/questions/38633076/how-to-get-a-subquery-in-from-clause-in-django-orm) rather than a Window expression issue. – JPG Apr 22 '20 at 18:08

3 Answers3

13

Another solution is Common Table Expressions (CTE), and with the help of django-cte, you could achieve what you want:

cte = With(
    YouModel.objects.annotate(
        your_window_function=Window(...),
    )
)

qs = cte.queryset().with_cte(cte).filter(your_window_function='something')

Which translates roughly to:

WITH cte as (
    SELECT *, WINDOW(...) as your_window_function
    FROM yourmodel
) 
SELECT * 
FROM cte
WHERE cte.your_window_function = 'something'
SebCorbin
  • 1,645
  • 1
  • 12
  • 23
3

There are developers interested in solving it but it's not something possible with the ORM right now.

One proposed solution would be to add a QuerySet.subquery() or .wrap() method that pushes the queryset within a subquery so it can then be filtered.

Update, Django 4.2 will support it.

Simon Charette
  • 5,009
  • 1
  • 25
  • 33
  • Could you elaborate on this more? Do you have any examples on filtering on a queryset annotated with a window function? – Stuart Buckingham Mar 05 '21 at 20:36
  • The ORM doesn't current support it but the idea would be to add a `subquery` method that wraps the current queryset in `SELECT * FROM (queryset_query) as window_subquery` so a subsequent filter can generate a `WHERE` against `window_subquery`. – Simon Charette Mar 06 '21 at 00:11
-1

You need to use raw query. In order to do multiple queries at one. for further information django documentation

for p in Person.objects.raw('''
    SELECT * FROM (SELECT *, *window_function* FROM TABLE)
    WHERE *filtering_conditions*'''):
    print(p)
# John Smith
# Jane Jones

Other thing you can do is the following.

model.py

class Category(models.Model):
    name = models.CharField(max_length=100)


class Hero(models.Model):
    # ...
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

    benevolence_factor = models.PositiveSmallIntegerField(
        help_text="How benevolent this hero is?",
        default=50
    )

querySet.py

hero_qs = Hero.objects.filter(category=OuterRef("pk"))
.order_by("-benevolence_factor")

Category.objects.all()
.annotate(most_benevolent_hero=Subquery(hero_qs.values('name')[:1]))

Generated SQL would look like this..

SELECT "entities_category"."id",
   "entities_category"."name",

  (SELECT U0."name"
   FROM "entities_hero" U0
   WHERE U0."category_id" = ("entities_category"."id")
   ORDER BY U0."benevolence_factor" DESC
   LIMIT 1) AS "most_benevolent_hero"
FROM "entities_category"
Eddwin Paz
  • 2,842
  • 4
  • 28
  • 48
  • Thanks! But using a raw sql is unfortunately not an option. Also adding an extra field is not a great solution for my use case. – ilse2005 Apr 24 '20 at 08:34