python - 如何根据特定项目的位置显示结果

Question

我的表格如下所示:

User
| id | name  | 
| 1  | user1 |
| 2  | user2 |
| 3  | user3 |
| 4  | user4 |
| 5  | user5 |
| 6  | user6 |
| 7  | user7 |
| 8  | user8 |

Event
| id | name   |
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |

Team
| id | name  | player1 | player2 | player3 | player4 |
| 1  | teamA |    1    |    2    |         |         |
| 2  | teamB |    5    |    6    |         |         |

Schedule
| id | event_id | player1 | player2 | team1 | team2 | when_do_they_play   |
| 1  |     1    |    1    |    3    |       |       | 2019-09-20 11:22:33 |
| 2  |     2    |         |         |   1   |   2   | 2019-09-25 21:12:43 |

我使用以下查询从中选择数据

SELECT
  "Schedule_1".id AS schedule_id,
  "Event_1".name AS event_name,
  "User_1".name AS player1,
  "User_2".name AS player2,
  "Schedule_1".when_do_they_play AS "Schedule_1_when_do_they_play",
  "Team_1".name AS team1,
  "Team_2".name AS team2
FROM
  "Schedule" AS "Schedule_1"
  LEFT OUTER JOIN "User" AS "User_1" ON "User_1".id = "Schedule_1".player1
  LEFT OUTER JOIN "User" AS "User_2" ON "User_2".id = "Schedule_1".player2
  LEFT OUTER JOIN "Event" AS "Event_1" ON "Event_1".id = "Schedule_1".event_id
  LEFT OUTER JOIN "Team" AS "Team_1" ON "Team_1".id = "Schedule_1".team1
  LEFT OUTER JOIN "Team" AS "Team_2" ON "Team_2".id = "Schedule_1".team2
ORDER BY
  "Schedule_1".when_do_they_play

并使用 sqlalchemy:

u1 = aliased(User)
u2 = aliased(User)
t1 = aliased(Team)
t2 = aliased(Team)
s =  aliased(Schedule)
e =  aliased(Event)

scheduled_games = db.session.query(
    s.id.label('schedule_id'),
    e.name.label('event_name'),
    u1.name.label('player1'),
    u2.name.label('player2'),
    s.when_do_they_play,
    t1.name.label('team1'),
    t2.name.label('team2'),
)\
.outerjoin(u1, u1.id == s.player1)\
.outerjoin(u2, u2.id == s.player2)\
.outerjoin(e, e.id == s.event_id)\
.outerjoin(t1, t1.id == s.team1)\
.outerjoin(t2, t2.id == s.team2)\
.order_by(s.when_do_they_play)\
.all()

输出是这样的

| id | schedule_id | event_name | player1 | player2 | Schedule_1_when_do_they_play | team1 | team2 |
| 1  |      1      |   event1   |  user1  |  user3  |       2019-09-20 11:22:33    |       |       |
| 2  |      2      |   event2   |         |         |       2019-09-25 21:12:43    | teamA | teamB |

我想要做的是仅显示特定用户所在的这些记录。假设我是“user1”。如果是这样，我应该看到两条记录，因为我是“user1”，而且“user1”位于“teamA”中。但如果我是“user3”，那么我应该只看到第一行，因为“user3”到目前为止还不属于任何团队。这样的查询会是什么样子？

以下是可用于重现上述输出的示例 Flask-SQLAlchemy 模型:

class User(db.Model):
    __tablename__ = "User"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class Event(db.Model):
    __tablename__ = "Event"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class Team(db.Model):
    __tablename__ = "Team"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    player1 = db.Column(db.Integer, db.ForeignKey("User.id"))
    player2 = db.Column(db.Integer, db.ForeignKey("User.id"))
    player3 = db.Column(db.Integer, db.ForeignKey("User.id"))
    player4 = db.Column(db.Integer, db.ForeignKey("User.id"))

class Schedule(db.Model):
    __tablename__ = "Schedule"
    id = db.Column(db.Integer, primary_key=True)
    event_id = db.Column(db.Integer, db.ForeignKey("Event.id"))
    player1 = db.Column(db.Integer, db.ForeignKey("User.id"))
    player2 = db.Column(db.Integer, db.ForeignKey("User.id"))
    team1 = db.Column(db.Integer, db.ForeignKey("Team.id"))
    team2 = db.Column(db.Integer, db.ForeignKey("Team.id"))
    when_do_they_play = db.Column(db.DateTime)

以及一些批量插入来重新创建行:

from datetime import datetime

db.session.bulk_insert_mappings(User, [{"id": i, "name": f"user{i}"} for i in range(1, 9)])
db.session.bulk_insert_mappings(Event, [{"id": i, "name": f"event{i}"} for i in range(1, 4)])
db.session.bulk_insert_mappings(Team, [
    {"id": 1, "name": "teamA", "player1": 1, "player2": 2},
    {"id": 2, "name": "teamB", "player1": 5, "player2": 6},
])
db.session.bulk_insert_mappings(Schedule, [
    {"id": 1, "event_id": 1, "player1": 1, "player2": 3,
     "when_do_they_play": datetime(2019, 9, 20, 11, 22, 33)},
    {"id": 2, "event_id": 2, "team1": 1, "team2": 2,
     "when_do_they_play": datetime(2019, 9, 25, 21, 12, 43)},
])
db.session.commit()

Answer 1

给定一个代表用户的 user_id 整数值，您需要检查 4 个条件:

1. Schedule.player1 == user_id
2. Schedule.player2 == user_id
3. Schedule.team1 in (以 user_id 作为玩家的所有团队的 ID)
4. Schedule.team2 in (以 user_id 作为玩家的所有团队的 ID)

这些标准需要进行“或”运算。

您可以定义查询以首先选择团队 ID 并将其合并为 scalar select :

player_teams = db.session.query(Team.id).filter(
    db.literal(user_id).in_(
        [Team.player1, Team.player2, Team.player3, Team.player4]
    )
).as_scalar()

这让我们可以重新使用用户所在的团队集，以将行限制为匹配的团队。然后我们可以过滤 scheduled_games 查询:

scheduled_games_query = (
    db.session.query(
        s.id.label('schedule_id'),
        e.name.label('event_name'),
        u1.name.label('player1'),
        u2.name.label('player2'),
        s.when_do_they_play,
        t1.name.label('team1'),
        t2.name.label('team2'),
    )
    .outerjoin(u1, u1.id == s.player1)
    .outerjoin(u2, u2.id == s.player2)
    .outerjoin(e, e.id == s.event_id)
    .outerjoin(t1, t1.id == s.team1)
    .outerjoin(t2, t2.id == s.team2)
    .order_by(s.when_do_they_play)
)

schedule_for_user = scheduled_games_query.filter(db.or_(
    # criteria 1 and 2 as one expression, user_id in (s.player1, s.player2)
    db.literal(user_id).in_([s.player1, s.player2]),
    # criteria 3, team1 is a team user_id is a player of
    s.team1.in_(player_teams),
    # criteria 4, team1 is a team user_id is a player of
    s.team2.in_(player_teams),
))

使用 SQLite 作为数据库引擎，这会生成以下查询:

SELECT
  "Schedule_1".id AS schedule_id,
  "Event_1".name AS event_name,
  "User_1".name AS player1,
  "User_2".name AS player2,
  "Schedule_1".when_do_they_play AS "Schedule_1_when_do_they_play",
  "Team_1".name AS team1,
  "Team_2".name AS team2
FROM
  "Schedule" AS "Schedule_1"
  LEFT OUTER JOIN "User" AS "User_1" ON "User_1".id = "Schedule_1".player1
  LEFT OUTER JOIN "User" AS "User_2" ON "User_2".id = "Schedule_1".player2
    LEFT OUTER JOIN "Event" AS "Event_1" ON "Event_1".id = "Schedule_1".event_id
    LEFT OUTER JOIN "Team" AS "Team_1" ON "Team_1".id = "Schedule_1".team1
    LEFT OUTER JOIN "Team" AS "Team_2" ON "Team_2".id = "Schedule_1".team2
WHERE
    ? IN ("Schedule_1".player1, "Schedule_1".player2) OR
    "Schedule_1".team1 IN (
        SELECT "Team".id
        FROM "Team"
        WHERE
            ? IN ("Team".player1, "Team".player2, "Team".player3, "Team".player4)
    ) OR
    "Schedule_1".team2 IN (
        SELECT "Team".id
        FROM "Team"
        WHERE
            ? IN ("Team".player1, "Team".player2, "Team".player3, "Team".player4)
    )
ORDER BY
    "Schedule_1".when_do_they_play

如果你把它变成一个函数:

scheduled_games_query = (
    db.session.query(
        s.id.label('schedule_id'),
        e.name.label('event_name'),
        u1.name.label('player1'),
        u2.name.label('player2'),
        s.when_do_they_play,
        t1.name.label('team1'),
        t2.name.label('team2'),
    )
    .outerjoin(u1, u1.id == s.player1)
    .outerjoin(u2, u2.id == s.player2)
    .outerjoin(e, e.id == s.event_id)
    .outerjoin(t1, t1.id == s.team1)
    .outerjoin(t2, t2.id == s.team2)
    .order_by(s.when_do_they_play)
)

def schedule_for_user(user_id):
    player_teams = db.session.query(Team.id).filter(
        db.literal(user_id).in_(
            [Team.player1, Team.player2, Team.player3, Team.player4]
        )
    ).as_scalar()

    return scheduled_games_query.filter(db.or_(
        # criteria 1 and 2 as one expression, user_id in (s.player1, s.player2)
        db.literal(user_id).in_([s.player1, s.player2]),
        # criteria 3, team1 is a team user_id is a player of
        s.team1.in_(player_teams),
        # criteria 4, team1 is a team user_id is a player of
        s.team2.in_(player_teams),
    ))

然后使用您的示例数据和循环我可以生成所需的输出:

>>> for user_id in (1, 3):
...     print("User id:", user_id)
...     for row in schedule_for_user(user_id):
...         print(*(v if v is not None else '' for v in row), sep="\t")
...     print()
... 
User id: 1
1   event1  user1   user3   2019-09-20 11:22:33
2   event2          2019-09-25 21:12:43 teamA   teamB

User id: 3
1   event1  user1   user3   2019-09-20 11:22:33