Django selecting top n records per group using ORM

Question

Following this question, I'm trying to get top 10 records per each group_by critera, but Django return this error:

from django.db.models import F, Window
from django.db.models.functions import RowNumber

Purchases.objects.annotate(row_number=Window(
        expression=RowNumber(),
        partition_by=F('customer'),
        order_by=F('field_of_interest').desc()
        )
    ).filter(row_number=10)

raise NotSupportedError(
django.db.utils.NotSupportedError: Window is disallowed in the filter clause.

When I remove .desc(), error message changes to:

ValueError: order_by must be either an Expression or a sequence of expressions.

I'm using PostgreSql. Is it a bug or am I wrong somewhere in my query?

Does this help? Django ticket #30104 [Need filtering by Window expression](https://code.djangoproject.com/ticket/30104) — Steven Rumbalski, Mar 01 '20 at 19:11
Thanks, @StevenRumbalski. Is there any other way to get top 10 records for each group_by value? — Mehdi Zare, Mar 01 '20 at 19:14
Can you look at this answer https://stackoverflow.com/a/19924129/7320045? Does this solve your problem? — Osman Omar, Mar 02 '20 at 02:53

score 12 · Answer 1 · answered Mar 02 '20 at 13:24

We can select top n per group with help of Subquery.

Firstly, let's get top n Purchases per customer

top_n_purchases_per_customer = Purchases.objects.filter(
    customer=OuterRef('customer')
).order_by('-field_of_interest')[:10]

Next, we can select Purchases with matching ids from the top 10 per each customer.

top_n_purchases = Purchases.objects.filter(
    id__in=Subquery(top_n_purchases_per_customer.values('id'))
)

The generated query makes use of correlated subquery, so can become slow. Make sure to use index for field_of_interest and customer (preferably combined index by both fields (see index_together in Django docs))

Django selecting top n records per group using ORM

1 Answers1

Linked