回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我有一个我正在尝试建立的查询。查询似乎是分部分工作的,查询的两个独立部分都返回正确数量的元素。但是,组合查询返回的结果集为空,这是不正确的。</p>
<p>注意:我知道查询1和查询2不需要和,但我想确保和查询1和查询2工作正常。</p>
<p><strong>查询1:</p>
<ul>
<li><p>SQLAlchemy查询</p>
<pre><code>session.query(Lobby).filter(
and_(
Lobby.id == spectator_table.c.lobby_id,
spectator_table.c.player_id == player.steamid
)
).all()
</code></pre></li>
<li><p>生成的SQL</p>
<pre><code>SELECT lobby.id AS lobby_id, lobby.name AS lobby_name, lobby.owner_id AS lobby_owner_id
FROM lobby, spectator
WHERE lobby.id = spectator.lobby_id AND spectator.player_id = ?
</code></pre></li>
</ul>
<p>查询2:</p>
<ul>
<li><p>SQLAlchemy查询</p>
<pre><code>session.query(Lobby).filter(
and_(
Lobby.id == Team.lobby_id,
LobbyPlayer.team_id == Team.id,
LobbyPlayer.player_id == player.steamid
)
).all()
</code></pre></li>
<li><p>生成的SQL</p>
<pre><code>SELECT lobby.id AS lobby_id, lobby.name AS lobby_name, lobby.owner_id AS lobby_owner_id
FROM lobby, team, lobby_player
WHERE lobby.id = team.lobby_id AND lobby_player.team_id = team.id AND lobby_player.player_id = ?
</code></pre></li>
</ul>
<p><strong>组合查询</strong>:</p>
<ul>
<li><p>SQLAlchemy查询</p>
<pre><code>session.query(Lobby).filter(
or_(
and_(
Lobby.id == Team.lobby_id,
LobbyPlayer.team_id == Team.id,
LobbyPlayer.player_id == player.steamid
), and_(
Lobby.id == spectator_table.c.lobby_id,
spectator_table.c.player_id == player.steamid
)
)
).all()
</code></pre></li>
<li><p>生成的SQL</p>
<pre><code>SELECT lobby.id AS lobby_id, lobby.name AS lobby_name, lobby.owner_id AS lobby_owner_id
FROM lobby, team, lobby_player, spectator
WHERE lobby.id = team.lobby_id AND lobby_player.team_id = team.id AND lobby_player.player_id = ? OR lobby.id = spectator.lobby_id AND spectator.player_id = ?
</code></pre></li>
</ul>
<hr/>
<p><strong>模型</strong></p>
<pre><code>spectator_table = Table('spectator', Base.metadata,
Column('lobby_id', Integer, ForeignKey('lobby.id'), primary_key=True),
Column('player_id', Integer, ForeignKey('player.steamid'),
primary_key=True
),
)
class Lobby(Base):
__tablename__ = 'lobby'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
owner_id = Column(Integer, ForeignKey('player.steamid'), nullable=False,
unique=True
)
teams = relationship("Team", backref="lobby",
cascade='save-update,merge,delete'
)
spectators = relationship("Player", secondary=spectator_table)
class Team(Base):
__tablename__ = 'team'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
lobby_id = Column(Integer, ForeignKey('lobby.id'), nullable=False)
players = relationship("LobbyPlayer", backref="team",
cascade='save-update,merge,delete,delete-orphan'
)
class LobbyPlayer(Base):
__tablename__ = 'lobby_player'
team_id = Column(Integer, ForeignKey('team.id'), primary_key=True)
player_id = Column(Integer, ForeignKey('player.steamid'), primary_key=True)
player = relationship("Player", uselist=False)
cls = Column(Integer)
class Player(Base):
__tablename__ = 'player'
steamid = Column(Integer, primary_key=True)
</code></pre>
<hr/>
<p>谢谢你的帮助!</p>