[Fixed]-Django ORM: window function with subsequent filtering

by

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

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

-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

Leave a comment