django annotate question

Question

I have the following model:

class Pick(models.Model):
league = models.ForeignKey(League)
user = models.ForeignKey(User)
team = models.ForeignKey(Team)
week = models.IntegerField()
result = models.IntegerField(default=3, help_text='loss=0, win=1, tie=2, not started=3, in progress=4')

I'm trying to get generate a standings table based off of the results, but I'm unsure how to get it done in a single query. I'm interested in getting, for each user in a particular league, a count of the results that = 1 (as win), 0 (as loss) and 2 as tie). The only thing I can think of is to do 3 separate queries where I filter the results and then annotate like so:

Pick.objects.filter(league=2, result=1).annotate(wins=Count('result'))
Pick.objects.filter(league=2, result=0).annotate(losses=Count('result'))
Pick.objects.filter(league=2, result=2).annotate(ties=Count('result'))

Is there a more efficient way to achieve this?

Thanks!

Daniel Roseman · Accepted Answer · 2011-09-04 05:44:41Z

1

The trick to this is to use the values method to just select the fields you want to aggregate on.

Pick.objects.filter(league=2).values('result').aggregate(wins=Count('result'))

answered Sep 4, 2011 at 5:44

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user417918 Over a year ago

Thanks for the reply, although I'm afraid I don't quite see how using values will help me get down to a single query for wins, losses and ties. Would you mind expanding your example a bit more?

Collectives™ on Stack Overflow

django annotate question

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related