peewee自引用的许多设计和查询问题

Question

我是Python和peewee的新手，这是我在这里的第一篇文章。你知道吗

此外，我几乎失明，所以请原谅任何打字错误：）

在整理了peewee的文件，搜索了这里和其他地方的许多帖子之后， 我仍然有关于如何自我参照，多对多的设计在peewee工作的问题。你知道吗

我的目标是查询一个体育数据库。最后的代码完成了大部分工作，但仍有两个问题没有解决（请参阅下面的）。你知道吗

编辑：我解决了下面关于特定锦标赛筛选的问题#1（我将很快发布更新的程序）。。你知道吗

编辑#2:我要收回！经过进一步测试，我意识到我的#1解决方案不起作用：（

编辑#3:我终于回答了自己的问题：）
所以。。。我修复了
语句中的几个拼写错误 下面的问题（在表值中）
我会在我的答案中公布解决方案。

问题陈述

下面是测试数据表（表最终将包含更多字段）

        Tournaments
+------------+------------+
| tournament | tournament |
|    id      |    name    |  
+------------+------------+
|    101     | US Open    |
|    102     | US Closed  |
+------------+------------+

      Players
+--------+--------+
| player | player |
|   id   |  name  |
+--------+--------+
|    1   | John   |
|    2   | Tony   |
|    3   | Ken    |
|    4   | George |
+--------+--------+

               Matches
+------------+--------+-------+-------+
| tournament | winner | loser |       |
|     id     |   id   |  id   | event |
+------------+--------+-------+-------+
|    101     |   1    |   4   | Semis |
|    101     |   2    |   3   | Semis |
|    101     |   1    |   2   | Final |
|    102     |   1    |   2   | Semis |
|    102     |   3    |   4   | Semis |
|    102     |   1    |   3   | Final |
+------------+--------+-------+-------+

期望的结果是显示玩家1在游戏中获胜 美国封闭式锦标赛（pid=1，tid=102）：

+----------+------------+----------+
| Loser id | Loser name |  Event   |
+----------+------------+----------+
|    2     |   Tony     |  Semis   |
|    3     |   Ken      |  Final   |
+----------+------------+----------+

Answer 1

我的答案涵盖了最初的要求，并进一步在结果表中添加了锦标赛信息。你知道吗

下面是程序输出，后面是完整的程序。你知道吗

程序输出

                 John's wins in the US Open
event      w_pid  tname      pid    id    l_pid  pname      tid
   -     -     -    -         -     -    -
Semis          1  US Open      4     1        4  George     101
Final          1  US Open      2     3        2  Tony       101
                 John's wins in the US Closed
event      w_pid  tname        pid    id    l_pid  pname      tid
   -     -      -    -         -     -    -
Semis          1  US Closed      2     4        2  Tony       102
Final          1  US Closed      3     6        3  Ken        102

解决方案

from peewee import *
from pprint import pprint
import tabulate

db = SqliteDatabase('app.db')

class BaseModel(Model):
    class Meta:
        database = db

class Tournament(BaseModel):
    tid       = IntegerField(primary_key=True)
    tname     = CharField()

class Player(BaseModel):
    pid   = IntegerField(primary_key=True)
    pname = CharField()

class Match(BaseModel):
    w_pid = ForeignKeyField(Player) # Winner pid
    l_pid = ForeignKeyField(Player) # Loser pid
    tid   = ForeignKeyField(Tournament)
    event = CharField()

db.drop_tables([Player, Match, Tournament])
db.create_tables([Player, Match, Tournament])

#   - Tournaments
Tournament.create(tid=101, tname="US Open")
Tournament.create(tid=102, tname="US Closed")

#   - Players
Player.create(pid=1, pname="John")
Player.create(pid=2, pname="Tony")
Player.create(pid=3, pname="Ken")
Player.create(pid=4, pname="George")

#   - US Open Matches
Match.create(tid=101, w_pid=1, l_pid=4, event="Semis")
Match.create(tid=101, w_pid=2, l_pid=3, event="Semis")
Match.create(tid=101, w_pid=1, l_pid=2, event="Final")

#   - US Closed Matches
Match.create(tid=102, w_pid=1, l_pid=2, event="Semis")
Match.create(tid=102, w_pid=3, l_pid=4, event="Semis")
Match.create(tid=102, w_pid=1, l_pid=3, event="Final")

def show_wins(tid, pid):
    Winner = Player.alias()
    wins = (Player
        .select(Player, Match, Tournament)
        .join(Match, on=(Player.pid == Match.l_pid))
        .join(Tournament, on=(Tournament.tid == Match.tid))
        .where(Tournament.tid == tid)
        .switch(Winner)
        .join(Winner, on=(Winner.pid == Match.w_pid))
        .where(Winner.pid == pid)
        .dicts()
    )
    header = wins[0].keys()
    rows =  [x.values() for x in wins]
    print(tabulate.tabulate(rows, header))

print("                 John's wins in the US Open")
show_wins(101, 1)

print("                 John's wins in the US Closed")
show_wins(102, 1)

db.close()