Python – SQLAlchethe Query and_/or_ Issue

I have a query that I'm trying to build. The query seems to work in parts, both separate parts of the query return the correct number of elements. However, the combined query returns an empty result set, which is incorrect.

Note: I know the and_'s are not needed for Query 1 and 2, but I wanted to make sure that and_ was working as I expected it to.

Query 1:

• SQLAlchemy Query

  session.query(Lobby).filter(
      and_(
          Lobby.id == spectator_table.c.lobby_id,
          spectator_table.c.player_id == player.steamid
      )
  ).all()
  

• Generated 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 = ?
  

Query 2:

• SQLAlchemy Query

  session.query(Lobby).filter(
      and_(
          Lobby.id == Team.lobby_id,
          LobbyPlayer.team_id == Team.id,
          LobbyPlayer.player_id == player.steamid
      )
  ).all()
  

• Generated 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 = ?
  

Combined Query:

• SQLAlchemy Query

  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()
  

• Generated 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 = ?
  

The Models

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)

Thanks for the help!