CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
Sirion
Sirion

Reputation: 927

Count and Max after values() method on Django query

I have this Django model:

class Action(models.Model):
    id = models.AutoField(primary_key=True)
    game = models.ForeignKey(Game)
    step = models.ForeignKey(Step)
    from_player = models.ForeignKey(Player)
    to_player = models.ForeignKey(Player)
    type = models.ForeignKey(ActionType)
    timestamp = models.DateTimeField(default=timezone.now)

I want to do the following:

  1. Filter on game, step and type
  2. Find the player/players how has/have obtained the highest number of actions

to do so I tried:

v = Action.objects.filter(game=1, step=2)
v = v.filter(type=3)
v = v.values('to_player').order_by().annotate(count=Count('to_player'))
v = v.annotate(max=Max('count')).filter(count=F('max')) #try to select the max

but last line gives me (because line 3 returns a list of dictionaries):

Cannot compute Max('count'): 'count' is an aggregate

I know that probably something similar has already been answered but Django values() and aggregate() are a bit tricky to me. Which is the right way to do this?

Upvotes: 1

Views: 4000

Answers (2)

scriptmonster
scriptmonster

Reputation: 2771

The error gives the clue, you should use aggregate instead of annotate for max:

v = Action.objects.filter(game=1, step=2)
v = v.filter(type=3)
v = v.values('to_player').order_by().annotate(count=Count('to_player'))
v = v.aggregate(max=Max('count')).filter(count=F('max')) #try to select the max

I'm not sure if the last filter would work but worth to try.

Upvotes: 0

pcoronel
pcoronel

Reputation: 3971

You can get the highest count using Django's .latest() method. Though documented for dates, it also works on strings and integers.

This should get you the Action with the highest to_player count:

# combined the filters, no need to separate into two steps
v = Action.objects.filter(game=1, step=2, type=3)
v = v.annotate(count=Count('to_player'))
v = v.latest('count') # will return Action with the highest count

Upvotes: 3

Related Questions