我有一个简单的模型来为任何数量的基于分数的游戏保持分数:
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']
...
我的Django Rest Framework API视图如下,旨在为我提供特定游戏的当前排行榜:
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)
问题是我在弄清楚如何获取前10个最高分数时遇到了麻烦,但是使用ORM而不是手动获取所有分数然后遍历它们以确保我只获得每个用户的最高分数。
尽管我为获得所需结果而进行的可悲尝试是可行的,但它必须非常低效,并且必须有一种更好的方法可以仅利用ORM的功能来获得这些结果。
感谢您的任何提前帮助。
与一起使用注释和聚合Max
。在以下方面:
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')
这大致转换为GROUP BY
带有Max
运算符的查询。
SELECT "user_id", MAX("value") AS "max_value"
FROM "score"
WHERE "game_id" = 1231
GROUP BY "user_id"
ORDER BY "max_value" DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句