Annotation not displaying/working properly

Question

I asked this question about annotation to find the top 5 most injured players, but now I am having an issue with displaying the correct info and I figured it should be it's own question.

This is my current view:

def home(request):
    context={}
    most_recent = PlayerInjury.objects.all().order_by('-timestamp')[:5]
    news = News.objects.all()
    most_injured = PlayerInjury.objects.annotate(injury_count=Count('id')).order_by('-injury_count')[:5]
    print most_injured
    context['most_injured'] = most_injured
    context['most_recent'] = most_recent
    context['news'] =  news
    return render_to_response('dash/home.html', RequestContext(request, context))

models.py

class PlayerInjury(models.Model):
    player =  models.ForeignKey(Player)
    injury_type = models.ForeignKey(Injury)
    injury_date = models.DateField(verbose_name='Injured On', null=True, blank=True)
    description = models.CharField(verbose_name='Description', max_length=180, null=True, blank=True)
    status = models.ForeignKey(Status)
    projected_return = models.DateField(verbose_name='Projected Return Date', null=True, blank=True)
    hide = models.BooleanField(default=False)
    returned = models.BooleanField(default=False)
    timestamp = models.DateTimeField(auto_now_add=True)

class Player(models.Model):
    first_name = models.CharField(verbose_name='First Name', max_length=30, null=True, blank=True)
    last_name = models.CharField(verbose_name='Last Name', max_length=30, null=True, blank=True)
    team = models.ForeignKey(Team, related_name='Team played for')
    pob = models.CharField(verbose_name='Place of Birth', max_length=100, null=True, blank=True)
    dob = models.DateField()
    age =  models.IntegerField(null=True, blank=True)
    height = models.CharField(verbose_name='Height', max_length=10, null=True, blank=True)
    weight = models.CharField(verbose_name='Weight', max_length=10, null=True, blank=True)
    drafted_by = models.ForeignKey(Team, related_name='drafted by')

However when I print out most_injured I get:
- Player A
- Player B
- Player A

When really, I thought it should be displayed like:
- Player A
- Player B

Due to the fact player A has been hurt more then player B

Any suggestions?

I thought this is how it should work.
- Player A (2 Injuries)
- Player B (1 Injury)
- etc

What does your model for `Player` look like? – aganders3 Jan 24 '12 at 19:27 — aganders3, Jan 24 '12 at 19:27

score 1 · Accepted Answer · answered Jan 24 '12 at 19:44

1

Try doing this instead:

most_injured = Player.objects.annotate(injury_count=Count('playerinjury')).order_by('-injury_count')[:5]

answered Jan 24 '12 at 19:44

aganders3

5,838
26
30

ROFL. That's the exact code I gave him to use in my answer. Not sure why he decided to change it around, but kudos to you for getting points for giving the OP info he already had before he asked the question. ;) – Chris Pratt Jan 24 '12 at 19:58
Eeech, feels dirty, haha. I should have probably checked the previous post before addressing this one. Looks like Yuji and I were neck-and-neck as well. His answer is probably better, even, as it includes some explanation about why changing your answer was a bad idea. – aganders3 Jan 24 '12 at 20:16
@aganders3, that was funny! First time I've seen "just now" twice. – Yuji 'Tomita' Tomita Jan 24 '12 at 21:01

score 1 · Answer 2 · answered Jan 24 '12 at 19:44

You are querying individual PlayerInjury objects, so of course you have multiple players in your results.

If you Count by id, you will always get a count of one since there is only 1 object per id.

If you want player objects ordered by injuries, you need

Player.objects.annotate(count=Count('playerinjury')).order_by('-count')

Annotation not displaying/working properly

2 Answers2