Conditional Aggregation of Foreign Key fields

I would like to get the count of foreign key objects with django, the foreign key itself will change conditionally. So, something like the example below.

Game.objects.annotate(
            filled=models.Case(
                models.When(
                    GreaterThan(
                        models.F("size_max"),
                        (
                            models.Count(
                                models.Case(
                                    models.When(
                                        participant_type=1, then="players"
                                    ),
                                    models.When(
                                        participant_type=2, then="teams",
                                    ),
                                ),
                            ),
                        ),
                    ),
                    then=1,
                ),
                default=0,
            )
        )

What I'd like to achieve is this: players and teams are reverse foreign keys to Game. I want to check whether the size_max field of Game exceeds the count of players or teams depending on the participant_type. How would I go about achieving this? Any help would be appreciated.

The above query results in an error - it introduces a GROUP BY with the model name in it. So, something like GROUP BY ('Game'), "game"."id" which I have no clue why this happens.

Back to Top