如何使用 djangos ORM 构建单个查询,以便为每个玩家获得低于 1.000 的最佳分数?我正在使用 django 1.7 rc2 和 python 3.4。
模型.py:
from django.db import models
class Player(models.Model):
pass
class Score(models.Model):
player = models.ForeignKey(Player)
score = models.PositiveIntegerField(default=0)
到目前为止我的解决方案:
测试.py
from django.test import TestCase
from bar.models import Player, Score
class BarTest(TestCase):
def test_do_it(self):
p1 = Player.objects.create()
p2 = Player.objects.create()
Score.objects.create(player=p1, score=100)
Score.objects.create(player=p1, score=12000)
Score.objects.create(player=p2, score=10000)
Score.objects.create(player=p2, score=500)
Score.objects.create(player=p2, score=900)
players = Player.objects.all()
for player in players:
score = Score.objects.filter(player=player, score__lte=1000).order_by('score').last()
if score:
print(player.id, score.id, score.score)
我不想在 python 中遍历每个玩家并对每个玩家进行查询,而是只用一个查询(不编写原始 sql)来解决这个问题。
请您参考如下方法:
使用select_related()你可以一次获取所有分数和他们的球员:
from collections import defaultdict
scores = defaultdict(list)
# Fetch all scores (with score <= 1000), including their players.
for score in Score.objects.filter(score__lte=1000).select_related('player'):
# Group scores by their player.
scores[score.player.id].append(score)
# Get max score for each player.
for p in scores:
max_score = max(scores[p], key=lambda s: s.score)
print(p, max_score.id, max_score.score)
