5

Basically, I want to do this but with django 2.0.

If I try:

Purchases.objects.filter(.....).annotate(my_max=Window( expression=Max('field_of_interest'), partition_by=F('customer') ) )

I get back all the rows but with the my_max property added to each record.

r_zelazny
  • 517
  • 3
  • 19

1 Answers1

6

If you are using PostgreSQL:

Purchases.objects.filter(.....).order_by(
    'customer', '-field_of_interest'
).distinct('customer')

UPDATE: Window expressions are not allowed in filter, so following methods does not work. Please refer to this answer for up-to-date solution

or with Window expression

Purchases.objects.filter(.....).annotate(my_max=Window(
    expression=Max('field_of_interest'),
    partition_by=F('customer')
    )
).filter(my_max=F('field_of_interest'))

but latter can yield multiple rows per customer if they have the same field_of_interest

Another Window, with single row per customer

Purchases.objects.filter(.....).annotate(row_number=Window(
        expression=RowNumber(),
        partition_by=F('customer'),
        order_by=F('field_of_interest').desc()
        )
    ).filter(row_number=1)
Alexandr Tatarinov
  • 3,946
  • 1
  • 15
  • 30
  • 2
    one followup: when I try the last query I get an error: `django.db.utils.NotSupportedError: Window is disallowed in the filter clause.` Each row has the correct `row_number` property but right now I am using python not the db to get rid of all records with a row_number > 1. Any way around this? – r_zelazny Jul 03 '18 at 16:39
  • Oh, that's unexpected to me. I think if it is unsupported then unfortunately it is unsupported.. – Alexandr Tatarinov Jul 03 '18 at 23:12