Skip to main content
 首页 » 编程设计

python之如何将多个查询减少为一个查询

2024年10月25日8telwanggs

如何使用 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)