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.