Django ORM Group by issue


This might be the duplicated issue but it hasn’t been resolved yet.
I have a model like this.

class Score(models.Model):

    id = models.BigAutoField(primary_key=True)
    venuemachine = models.ForeignKey(VenueMachine, related_name='scores', null=True,
    player = models.ForeignKey(User, blank=True, null=True, on_delete=models.CASCADE,
    score = models.BigIntegerField(blank=True, null=True)

And I am going to get top 20 scores on a venuemachine.
But I want to get only one score per player.
So those 20 scores are all from different players.

My queryset looks like this

scores = (


scores = (
    .values('id', 'player', 'venuemachine_id')

But I still get duplicate players.
So I printed out my SQL query from QuerySet and it looks like this.

SELECT "venuemachine_score"."id", "venuemachine_score"."player_id", "venuemachine_score"."venuemachine_id", MAX("venuemachine_score"."score") AS "max_score", "venuemachine_score"."id" AS "player_score_id" FROM "venuemachine_score" WHERE ("venuemachine_score"."is_shared" = True AND "venuemachine_score"."venuemachine_id" IN (SELECT U0."id" FROM "venuemachine_venuemachine" U0 WHERE U0."machine_id" = 1195)) GROUP BY "venuemachine_score"."id" ORDER BY "max_score" DESC  LIMIT 20

It still groups by id but it should group by player_id.
I am not sure how I can solve this.
This really drives me crazy.
Any help would be appreciated.


Could you please revisit this issue?

Quite frankly, at this point, I’d throw out the idea of trying to do it in one query. I’d get as close as I could get with the query and use the final filtering / organization in code. My hunch is you’ve spent far more time trying to find the “optimal” solution than you will ever save over the lifetime of the application.

I posted what I thought was a good basis for what you’re trying to do here at Django queryset chain annotate and distinct together - #6 by KenWhitesell. Is this along the right lines, or is there some difference between this and that that I’m not seeing?