python - 执行三向连接

Question

我有三个相关的实体，如下所示:

• 城堡:属于玩家
• 玩家:是联盟的成员

这些是我的模型:

def ReferenceCol(tablename, nullable=False, pk_name='id', **kwargs):
    """Column that adds primary key foreign key reference.

    Usage: ::

        category_id = ReferenceCol('category')
        category = relationship('Category', backref='categories')
    """
    return db.Column(
        db.ForeignKey("{0}.{1}".format(tablename, pk_name)),
        nullable=nullable, **kwargs)

class Player(Model):

    __tablename__ = 'players'
    id = Column(db.Integer, primary_key=True)
    player_name = Column(db.String(80), unique=False, nullable=False)
    alliance_id = ReferenceCol('alliances', nullable=True)
    ...
    def __repr__(self):
        return '<Player({player_name!r})>'.format(player_name=self.player_name)


class Alliance(Model):

    __tablename__ = 'alliances'
    id = Column(db.Integer, primary_key=True)
    alliance_name = Column(db.String(80), unique=False, nullable=False)
    ...
    def __repr__(self):
        return '<Alliance({alliance_name!r})>'.format(alliance_name=self.alliance_name)


class Habitat(Model):

    __tablename__ = 'habitats'
    id = Column(db.Integer, primary_key=True)
    castle_name = Column(db.String(80), unique=False, nullable=True)
    player_id = ReferenceCol('players', nullable=True)
    ...
    def __repr__(self):
        return '<Habitat({castle_name!r})>'.format(castle_name=self.castle_name)

我可以轻松加入栖息地和玩家:

# Join habitats and players
for habitat, player in db.session.query(Habitat, Player).join(Player).all()[:3]:
    print habitat, player

以及玩家和联盟:

# Join players and alliances
for player, alliance in db.session.query(Player, Alliance).join(Alliance).all()[:3]:
    print player, alliance

但是如何连接所有三个表呢？我如何加入栖息地、玩家和联盟？我在找事做:

for castle, player, alliance in magic_join():
    print castle, player, alliance

Answer 1

您可以将多个模型传递给 join .顺序很重要:如果您查询 Habitat，您将只会获得拥有栖息地的玩家以及与玩家结盟的玩家。如果您使用外部连接查询 Alliance，您将获得所有联盟及其玩家和栖息地。

for a, p, h in session.query(Alliance, Player, Habitat).outerjoin(Player, Habitat):
    print(a.id, p.id, h.id)

---

与其查询多个对象，不如定义 relationships .查询 Alliance 并使用 eager loading无需额外查询即可加载关系。迭代联盟，并为每个联盟迭代其球员等。

class Alliance(Base):
    __tablename__ = 'alliance'
    id = sa.Column(sa.Integer, primary_key=True)

class Player(Base):
    __tablename__ = 'player'
    id = sa.Column(sa.Integer, primary_key=True)
    alliance_id = sa.Column(sa.ForeignKey(Alliance.id), nullable=False)
    alliance = orm.relationship(Alliance, backref='players')

class Habitat(Base):
    __tablename__ = 'habitat'
    id = sa.Column(sa.Integer, primary_key=True)
    player_id = sa.Column(sa.ForeignKey(Player.id), nullable=False)
    player = orm.relationship(Player, backref='habitats')

for a in session.query(Alliance).options(
    db.joinedload(Alliance.players).joinedload(Player.habitats)
):
    for p in a.players:
        for h in p.habitats:
            print(a.id, p.id, h.id)