Count() with annotate

Hi everyone,

I thought I understood how to count numbers of entries but am getting strange results.

  counts = Strike.objects.annotate(player_strikes = Count('player'))
  [print(count.player_strikes, count.player.name) for count in counts]

gives output like:

1 zrqml
1 zrqml
1 chris
1 Kusch
1 Kusch
1 USS ALABAMA
1 Kusch
...

so one item for each strike rather than one per player with the count totals. I tried setting distinct=True but that didn’t help. I would like a list of players with the number of strikes they have. What am I missing?

The model is straightforward:

class Strike(models.Model):
  STRIKE_ACTIVITY = (
    ('TW', 'TW'),
    ('TB', 'TB'),
    ('Tickets', 'Tickets'),
    ('Other', 'Other'),
  )
  player = models.ForeignKey(Player, on_delete=models.CASCADE)
  strike_date = models.DateField()
  ishard = models.BooleanField(default=True)
  comments = models.TextField(max_length=200, blank=True)
  activity = models.CharField(max_length=10, choices=STRIKE_ACTIVITY)

You want to base your query on Player, not Strike. Strike is the “many” side of the many-to-one relationship.
You want the count of Strike per Player, not the count of Player per Strike (which is always going to be 1.)

counts = Player.objects.annotate(strikes = Count('strike'))

Duh! Thanks so much. I should have realized that.