CODEWITHSUNDEEP
django
kmypwn
kmypwn

Reputation: 487

Django Ordering First Column Groupings by Max Value in Second Column Per Group

Given a Model in Django with fields/values as shown:

Group Votes
A 5
B 2
C 6
B 7
A 3
C 4

it's easy to use

class Meta:
        ordering = ['group', '-votes']

to return

Group Votes
A 5
A 3
B 7
B 2
C 6
C 4

How could this be modified so that the Group is also ordered by the max Votes value within each Group as shown:

Group Votes
B 7
B 2
C 6
C 4
A 5
A 3

Upvotes: 0

Views: 146

Answers (1)

datosula
datosula

Reputation: 1586

You may use Window functions which will let you to calculate maximum per group for each record and later use it for sorting, like this:

YourModel.objects.annotate(
    max_per_group = Window(
        expression = Max('votes'),
        partition_by = F('group')
    )
).order_by(
    '-max_per_group','group','-votes'
)

But if you need to place all this inside your model you should use Custom Manager, override get_queryset add Window annotation to queryset and use the annotated value in Meta's ordering, like this:

class YourModelObjectsManager(models.Manager):
    def get_queryset(self):
        return models.Manager.get_queryset(self).annotate(
            max_per_group = Window(
                expression = Max('votes'),
                partition_by = F('group')
            )
        )

class YourModel(models.Model):
    ....
    
    objects = YourModelObjectsManager()
    
    
    class Meta:
        ordering = ['-max_per_group','group','-votes']

But, please, pay attention that including this kind of annotation in your model will lead to additional calculations on the database side each time you access your model (and this will be more significant if your model contains a lot of records). Thus, if ordering is a special case i.e. you need it for example in only one view or just in Django's admin, I would recommend to use annotate and order_by for those special cases.

Upvotes: 1

Related Questions