如何在Django ORM中进行多次连接和聚合？

Question

我是Django/Python/ORM的新手！我对ORM中的连接（joins）有点搞不清楚。

这是我的模型：

class Courts(models.Model):
    id = models.AutoField(primary_key=True)
    location_name = models.CharField(max_length=100)
    number = models.IntegerField()
    def __unicode__(self):
        return "%s %s %s" % (self.id, self.location_name, self.number)

class Matches(models.Model):
    id = models.AutoField(primary_key=True)
    date = models.DateTimeField()
    court = models.ForeignKey(Courts)
    def __unicode__(self):
        return "%s %s" % (self.id, self.date)

class Participants(models.Model):
    id = models.AutoField(primary_key=True)
    match = models.ForeignKey(Matches)
    userid = models.ForeignKey(User)
    games_won = models.IntegerField()
    def __unicode__(self):
        return "%s %s %s" % (self.id, self.games_won, self.userid)

第一步是把所有的“参与记录”整理成下面这样的输出：

[match_id] [date]       [userid]  [games_won]  [court_location_name]  [court_number]
1          01-01-2011   mike      6            Queen                  5
1          01-01-2011   chris     4            Queen                  5
2          01-02-2011   bob       3            Queen                  6
2          01-02-2011   joe       4            Queen                  6
3          01-03-2011   jessie    5            Queen                  2
3          01-03-2011   john      5            Queen                  2

我应该写什么样的ORM脚本来实现这个？我对ORM中简单的连接是怎么工作的都很困惑，更别提要把三个表结合在一起了。

接下来，我想把数据汇总成最终看起来像这样的格式：

[match_id] [date]       [player1] [player2]  [p1wins] [p2wins] [game_winner] [court_location_name]  [court_number]
1          01-01-2011   mike      chris      6        4        mike          Queen                  5
2          01-02-2011   bob       joe        3        4        joe           Queen                  6
3          01-03-2011   jessie    john       5        5        draw          Queen                  2

这会改变我在视图中写的ORM脚本吗？这是我需要放在视图里还是模板里呢？

更新：

我想我可以使用 select_related()。所以我试了 Participants.objects.select_related()，然后得到了这个SQL语句：

SELECT "squash_participants"."id", "squash_participants"."match_id", "squash_participants"."userid_id", "squash_participants"."games_won", "squash_matches"."id", "squash_matches"."date", "squash_matches"."court_id", "squash_courts"."id", "squash_courts"."location_name", "squash_courts"."number", "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined" FROM "squash_participants" INNER JOIN "squash_matches" ON ("squash_participants"."match_id" = "squash_matches"."id") INNER JOIN "squash_courts" ON ("squash_matches"."court_id" = "squash_courts"."id") INNER JOIN "auth_user" ON ("squash_participants"."userid_id" = "auth_user"."id"

这看起来更像我想要的结果。现在我不知道怎么把这些数据提取到模板里。

更新2：

我的视图是这样的：

def index(request):
    matches_list = Participants.objects.all()
    return render_to_response('squash/base_matches.html', {'matches_list': matches_list}, context_instance = 
RequestContext(request))
    return HttpResponse(output)

而我的模板是这样的：

{% for matches in matches_list %}
    <tr>
        <td>{{ matches.id }}</td> 
        <td>{{ matches.date|date:"d-m-Y" }}</td>
        <td>{{ matches.date|date:"G:i" }}</td>
    </tr>
    {% endfor %}

它能正确显示所有的 Participants.id，但比如说比赛的ID、日期或场地却没有显示出来。

django orm 数据库连接模板渲染查询集 select_related 聚合查询视图逻辑

如何在Django ORM中进行多次连接和聚合？

1 个回答

撰写回答