Django annotate() on two step relationship confusion

Question

I have the following 3 models:

Category:
  date_start
  date_end
  active: bool

Player:
  name: str
  age: int
  category = models.ForeignKey(Category)

PlayerContact:
  contact_result: int
  player = models.ForeignKey(Player)

In this case I have:

2 Categories
10 Players per Category
1 to 3 Players in each Category with a contact_result = 3

How do I annotate a Category queryset to get the amount of players with a contact_result=3?

I've tried this:

Categories.objects.annotate(
    Count(
        'player', 
        filter=Q(player__playercontact__contact_result=3)
    )
) # returns all players for each Category

Categories.objects.annotate(
    Count('player__playercontact__contact_result')
) # returns players with a contact_result but it's not filtered

Something similar to this:

<CategoryQuerySet [<Category: Category object>, <Category: Category object>]>

# where each Category object is annotated by the count() of Players with,
# a PlayerContact's contact_result = 3

score 1 · Answer 1 · answered Apr 09 '18 at 19:12

1

instead of annotate, try count chained to the filter

Categories.objects.filter(Q(player__playercontact__contact_result=3)).count()

answered Apr 09 '18 at 19:12

yoyoyoyo123

2,362
2
22
36

You also can ommit the `Q` object if you wish. `Categories.objects.filter(player__playercontact__contact_result=3).count()` – Ralf Apr 09 '18 at 19:17
This returned the amount of players with that flag, what I need are both Categories with **that** annotated to each one, I edited the question to make it clearer. – Onilol Apr 09 '18 at 19:18
I have a feeling you need .values('SomeField') in the annotation after filter. https://docs.djangoproject.com/en/2.0/topics/db/aggregation/#values – yoyoyoyo123 Apr 09 '18 at 19:24

score 0 · Answer 2 · answered Apr 09 '18 at 21:00

I ended up doing this:

cats_and_numbers = [(cat, PlayerContact.objects.filter(contact_result=3,
                    player__category_id=cat.id).count()) for cat in queryset]

won't mark this as correct tho since I'm quite sure there is a proper way to do it.

Edit:

If this was actually the best way to do it, I'd probably create a manager to reduce visual noise, something like: PlayerContact.objects.denied() # denied is just a random word, to illustrate.

Django annotate() on two step relationship confusion

2 Answers2