How to limit top N of each group in Django ORM by using Postgres Window functions or Lateral Joins?

Question

I have following Post, Category & PostScore Model.

class Post(models.Model):
    category = models.ForeignKey('Category', on_delete=models.SET_NULL, related_name='category_posts', limit_choices_to={'parent_category': None}, blank=True, null=True)
    status = models.CharField(max_length=100, choices=STATUS_CHOICES, default='draft')
    deleted_at = models.DateTimeField(null=True, blank=True)
    ...
    ...

class Category(models.Model):
    title = models.CharField(max_length=100)
    parent_category = models.ForeignKey('self', on_delete=models.SET_NULL,
                                        related_name='sub_categories', null=True, blank=True,
                                        limit_choices_to={'parent_category': None})
    ...
    ...

class PostScore(models.Model):
    post = models.OneToOneField(Post, on_delete=models.CASCADE, related_name='post_score')
    total_score = models.DecimalField(max_digits=8, decimal_places=5, default=0)
    ...
    ...

So what i want is to write a query which returns N number of posts (Posts) of each distinct category (Category) sorted by post score (denoted by total_score column in PostScore model) in descending manner. So that i have atmost N records of each category with highest post score.

So i can achieve the above mentioned thing by the following raw query which gives me top 10 posts having highest score of each category :

SELECT * 
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY "post"."category_id" 
           ORDER BY "postscore"."total_score" DESC) AS "rank"
    FROM
         "post"
    LEFT OUTER JOIN 
         "postscore" 
    ON
       ("post"."id" = "postscore"."post_id") 
    WHERE 
       ("post"."deleted_at" IS NULL AND "post"."status" = 'accepted') 
    ORDER BY 
        "postscore"."total_score" 
    DESC
) final_posts
WHERE 
    rank <= 10

What i have achieved so far using Django ORM:

>>> from django.db.models.expressions import Window
>>> from django.db.models.functions import Rank
>>> from django.db.models import F
>>> posts = Post.objects.annotate(
                                 rank=Window( expression=Rank(), 
                                 order_by=F('post_score__total_score').desc(),
                                 partition_by[F('category_id')]
                                 )). \
            filter(status='accepted', deleted_at__isnull=True). \
            order_by('-post_score__total_score')

which roughly evaluates to

>>> print(posts.query)
>>> SELECT *,
       RANK() OVER (PARTITION BY "post"."category_id" 
       ORDER BY "postscore"."total_score" DESC) AS "rank"
     FROM
          "post"
     LEFT OUTER JOIN 
          "postscore" 
     ON
         ("post"."id" = "postscore"."post_id") 
     WHERE 
         ("post"."deleted_at" IS NULL AND "post"."status" = 'accepted') 
     ORDER BY 
         "postscore"."total_score" 
     DESC

So basically what is missing that i need to limit each group (i.e category) results by using “rank” alias.

Would love to know how this can be done ?

I have seen one answer suggested by Alexandr on this question, one way of achieving this is by using Subquery and in operator . Although it satisfies the above condition and outputs the right results but the query is very slow.

Anyway this would be the query if I go by Alexandr suggestions:

>>> from django.db.models import OuterRef, Subquery
>>> q = Post.objects.filter(status='accepted', deleted_at__isnull=True, 
    category=OuterRef('category')).order_by('-post_score__total_score')[:10]
>>> posts = Post.objects.filter(id__in=Subquery(q.values('id')))

So i am more keen in completing the above raw query (which is almost done just misses the limit part) by using window function in ORM. Also, i think this can be achieved by using lateral join so answers in this direction are also welcomed.

score 1 · Answer 1 · answered Feb 13 '21 at 08:28

So I have got a workaround using RawQuerySet but the things is it returns a django.db.models.query.RawQuerySet which won't support methods like filter, exclude etc.

>>> posts = Post.objects.annotate(rank=Window(expression=Rank(), 
            order_by=F('post_score__total_score').desc(),
            partition_by=[F('category_id')])).filter(status='accepted', 
            deleted_at__isnull=True)
>>> sql, params = posts.query.sql_with_params()
>>> posts = Post.objects.raw(""" SELECT * FROM ({}) final_posts WHERE 
                                 rank <= %s""".format(sql),[*params, 10],)

I'll wait for the answers which provides a solution which returns a QuerySet object instead, otherwise i have to do by this way only.

How to limit top N of each group in Django ORM by using Postgres Window functions or Lateral Joins?

1 Answers1