我有一个我正在尝试建立的查询。查询似乎是分部分工作的,查询的两个独立部分都返回正确数量的元素。但是,组合查询返回的结果集为空,这是不正确的。
注意:我知道查询1和查询2不需要和,但我想确保和查询1和查询2工作正常。
查询1:
SQLAlchemy查询
session.query(Lobby).filter(
and_(
Lobby.id == spectator_table.c.lobby_id,
spectator_table.c.player_id == player.steamid
)
).all()
生成的SQL
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 = ?
查询2:
SQLAlchemy查询
session.query(Lobby).filter(
and_(
Lobby.id == Team.lobby_id,
LobbyPlayer.team_id == Team.id,
LobbyPlayer.player_id == player.steamid
)
).all()
生成的SQL
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 = ?
组合查询:
SQLAlchemy查询
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()
生成的SQL
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 = ?
模型
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)
谢谢你的帮助!
托尼,我从来不知道self_group(),非常棒!
特隆保罗, 可以使用self_group()添加paren around和clauseElement,例如:
为了详细说明Tony的答案:self_group()对于分组“二进制”表达式以外的子句非常有用,它可以与任何子句元素一起使用。
我看到了你在SA 0.7.9上遇到的同样的问题
似乎发生的事情是括号并没有按您所希望的方式正确应用。我使用self_group()来实现这个功能,但问题是你不应该使用它。这是文件 self_group。我认为应该有一个比我更好的答案,但是,这应该让你去。
下面是生成的sql,我相信这正是您所追求的。
相关问题 更多 >
编程相关推荐