CODEWITHSUNDEEP
django
tsantor
tsantor

Reputation: 544

Django - How to get the top 10 highest scores, but limiting it to a single score per user?

I have a simple model to keep scores for any number of score based games:

class Score(models.Model):
    game = models.ForeignKey(Game, related_name='leaderboards')
    value = models.IntegerField(db_index=True)
    uom = models.CharField('Unit of Measurement', max_length=10)

    user = models.ForeignKey(settings.AUTH_USER_MODEL)

    class Meta:
        ordering = ['-value']

    ...

My Django Rest Framework API view is as follows which is intended to get me the current leaderboard for a particular game:

class LeaderboardView(APIView):
    ...

    def get(self, request, pk):
        game = get_object_or_404(Game, pk=pk)

        # Get all scores order by highest to lowest
        scores = (Score.objects.select_related('user').
                  filter(game=game).
                  order_by('-value'))

        # Create and ordered dict to preserve order added
        # and only add user scores who have not been added yet
        top10 = collections.OrderedDict()
        for obj in scores:
            if obj.user.pk not in top10 and len(top10) < 10:
                top10[obj.user.pk] = obj

        # Turn ordered dict back into a QuerySet list
        results = []
        for obj in top10.itervalues():
            results.append(obj)

    serializer = ScoreSerializer(results, many=True)
    return Response(serializer.data)

The problem is I'm having trouble figuring out how to get the top 10 highest scores, but limiting it to a single score per user (eg - their highest) using the ORM rather than manually getting all scores and then looping through them to ensure I only grab the highest score per user.

While my pathetic attempt to get the results I needs works, it must be very inefficient and there has to be a better way I can get these results using just the power of the ORM.

Thanks for any help in advance.

Upvotes: 2

Views: 1539

Answers (1)

Saurabh Kumar
Saurabh Kumar

Reputation: 5945

Use annotation and aggregation with Max. Something on the line of:

from django.db.models.aggregates import Max

leaderboard_data = Score.objects.filter(game=game) \
                                .values('user') \
                                .annotate(max_value=Max('value')) \
                                .order_by('-max_value')

This roughly converts to a GROUP BY query with Max operator. 

SELECT "user_id", MAX("value") AS "max_value" 
FROM "score"
WHERE "game_id" = 1231
GROUP BY "user_id" 
ORDER BY "max_value" DESC

Upvotes: 3

Related Questions