Django ORM Group by issue

Using Django
1

Hi

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,
            on_delete=models.CASCADE)
    ...
    player = models.ForeignKey(User, blank=True, null=True, on_delete=models.CASCADE,
            related_name='scores')
    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
		.objects
		.filter(venuemachine_id__in=vm_ids)
)

...

scores = (
    scores
    .values('id', 'player', 'venuemachine_id')
    .annotate(max_score=Max('score'))
    .order_by('-max_score')[:count]
)

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.

Thanks!

2

@KenWhitesell
Could you please revisit this issue?

3

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.

4

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?