保存具有多个外键关系的Peewee查询

Question

我有一个使用Peewee和PostgreSQL的模型：

class Game(Model):
    class Meta:
        database = db
        db_table = 'd_games'

    game_id = PrimaryKeyField()
    location = CharField()

    home_team = ForeignKeyField(Team, related_name='home_games')
    away_team = ForeignKeyField(Team, related_name='away_games')

你可以看到，这个模型和一个Team模型有两个外键关系。

我想做的事情是：

games = Game.select(Game, Team).join(Team).limit(10)
for g in games:
    print '%s vs. %s' % (g.home_team.team_name, g.away_team.team_name)

不幸的是，虽然home_team的关系在最开始的查询中被保存了：

# from the query log
'SELECT t1."game_id", t1."game_date", t1."location", t1."home_team_id", t1."away_team_id", t2."team_id", t2."team_name"
FROM "d_games" AS t1 
INNER JOIN "d_team" AS t2 
ON (t1."home_team_id" = t2."team_id") 
LIMIT 10

但是away_team的关系却没有保存，所以每次我想打印g.away_team.team_name的时候，都会执行一个新的查询。

我该怎么做才能让home_team和away_team的关系都被保存呢？我试过

games = Game.select(Game, Team, Team).join(Team).switch(Game).join(Team).limit(10)

但这给我带来了一个table name "t2" specified more than once的错误，因为Peewee尝试执行的查询是

'SELECT t1."game_id", t1."game_date", t1."location", t1."home_team_id", t1."away_team_id", t2."team_id", t2."team_name", t2."team_id", t2."team_name"
FROM "d_games" AS t1 
INNER JOIN "d_team" AS t2 
ON (t1."home_team_id" = t2."team_id") 
INNER JOIN "d_team" AS t2 
ON (t1."home_team_id" = t2."team_id") 
LIMIT 10

Answer 1

在这些情况下，你需要使用 Model.alias()。下面是一个例子：

HomeTeam = Team.alias()
AwayTeam = Team.alias()
query = (Game
         .select(Game, HomeTeam, AwayTeam)
         .join(HomeTeam, on=(Game.home_team == HomeTeam.id))
         .switch(Game)
         .join(AwayTeam, on=(Game.away_team == AwayTeam.id))
         .order_by(Game.id))

这会得到以下结果：

SELECT 
    t1."id", t1."location", t1."home_team_id", t1."away_team_id", 
    t2."id", t2."name",
    t3."id", t3."name" 
FROM "d_games" AS t1 
INNER JOIN "team" AS t2 ON (t1."home_team_id" = t2."id") 
INNER JOIN "team" AS t3 ON (t1."away_team_id" = t3."id") 
ORDER BY t1."id

然后：

for game in query:
    print game.home_team.name, game.away_team.name