CODEWITHSUNDEEP
pythondjangopostgresql
Mehdi Zare
Mehdi Zare

Reputation: 1381

Django selecting top n records per group using ORM

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?

Upvotes: 4

Views: 4843

Answers (1)

Alexandr Tatarinov
Alexandr Tatarinov

Reputation: 4034

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))

Upvotes: 12

Related Questions