Ufdjrw

I've got a relationship set up across three separate classes using SQLAlchemy, with an association table for a many-to-many relationship. Minimal example:

from sqlalchemy import * 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import configure_mappers, relationship

Base = declarative_base()

teams_users = Table(
 'teams_users', Base.metadata,
 Column('team_id', ForeignKey('teams.id')),
 Column('user_id', ForeignKey('users.id'))
)

class User(Base):
 __tablename__ = 'users'
 # No autoincrement, since we're using externally-generated UIDs
 id = Column(Integer, primary_key=True, autoincrement=False)
 teams = relationship('Team', secondary=teams_users, back_populates="users") 

class Team(Base):
 __tablename__ = 'teams'
 id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
 game_id = Column(Integer, ForeignKey('games.id'), nullable=False)
 games = relationship("Game", foreign_keys='games.id')
 users = relationship("User", secondary='teams_users', back_populates="teams")

class Game(Base):
 __tablename__ = 'games'
 id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
 team1_id = Column(Integer, ForeignKey('teams.id'))
 team2_id = Column(Integer, ForeignKey('teams.id'))
 team1 = relationship("Team", back_populates="games", foreign_keys=team1_id, uselist=False)
 tean2 = relationship("Team", back_populates="games", foreign_keys=team2_id, uselist=False)

# done declaring, trigger the error
configure_mappers()

Attempting to query any of these relations returns a 'Table' object has no attribute 'id' error:

Traceback (most recent call last):
 File "...", line 35, in <module>
 configure_mappers()
 File "/.../sqlalchemy/orm/mapper.py", line 3033, in configure_mappers
 mapper._post_configure_properties()
 File "/.../sqlalchemy/orm/mapper.py", line 1832, in _post_configure_properties
 prop.init()
 File "/.../sqlalchemy/orm/interfaces.py", line 183, in init
 self.do_init()
 File "/.../sqlalchemy/orm/relationships.py", line 1655, in do_init
 self._process_dependent_arguments()
 File "/.../sqlalchemy/orm/relationships.py", line 1680, in _process_dependent_arguments
 setattr(self, attr, attr_value())
 File "/.../sqlalchemy/ext/declarative/clsregistry.py", line 281, in __call__
 x = eval(self.arg, globals(), self._dict)
 File "<string>", line 1, in <module>
AttributeError: 'Table' object has no attribute 'id'

My goal in constructing it this way is that I could easily check which Teams each User has ever been a part of.

Additionally, Game has two foreign keys into Team because the use case for this project supports arbitrarily sized teams, but only ever two teams. This allows me to have a result of "team1 won" and immediately have a reference to the winning users for stat tracking and historical reference.

What am I doing wrong here?

To define the relationship between a game and the two teams that play in it, you need to only give the games table foreign keys; a team can play in multiple games, a one-to-many relationship; remove the games_id column altogether. The exception you got is a bit of a red herring, but it fails to properly configure the foreign_keys='games.id' argument in a relationship that doesn't need that foreign key.

The relationship configuration on the Team class is a little tricky here as the Team.games attribute would have to relate to either foreign key. This is covered in the documentation under Handling Multiple Join Paths; you were almost there but there is no uselist parameter needed here:

class Game(Base):
 __tablename__ = 'games'
 id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
 team1_id = Column(Integer, ForeignKey('teams.id'))
 team2_id = Column(Integer, ForeignKey('teams.id'))
 team1 = relationship("Team", foreign_keys=team1_id)
 team2 = relationship("Team", foreign_keys=team2_id)

Note that I omitted the back_populates references here, because two relationships updating a single relationship on the other site leads one or the other of the two foreign keys being updated with the other value, leading to a game between the same team on either side!

The inverse relationship attribute, Team.games, requires a custom primaryjoin because you are looking for games where either team1_id or team2_id is the foreign key pointing back. Use the foreign() annotation to help SQLAlchemy determine when to update the relationship (it'll watch for the foreign key changes), and use a lambda to defer resolving the columns:

class Team(Base):
 __tablename__ = 'teams'
 id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
 # game_id = Column(Integer, ForeignKey('games.c.id'), nullable=False)
 games = relationship(
 "Game",
 primaryjoin=lambda: or_(
 Team.id == foreign(Game.team1_id),
 Team.id == foreign(Game.team2_id)
 ),
 viewonly=True,
 )
 users = relationship("User", secondary='teams_users', back_populates="teams")

You could also make primaryjoin a string containing the expression now being executed in a lambda, so 'or_(Team.id == foreign(Game.team1_id), Team.id == foreign(Game.team2_id))'.

Again, no back_populates, this type of relationship can't automatically update relationships between loaded objects. If you need to see these relationships reflected before committing, you'll need to issue a session flush. I also added viewonly=True, because you can't map mutations to the Team.games list to updates in the database (what would adding a new game to the list mean, that this team is team 1 or team 2?).

You may want to add a custom constraint table to ensure that games never take place between the same team on both sides:

class Game(Base):
 # ...
 __table_args__ = (
 CheckConstraint(team1_id != team2_id, name='different_teams'),
 )

A quick demonstration of the relationships:

from itertools import combinations

engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

teams = [Team() for _ in range(3)]
session.add_all(teams)
user = User(id=42, teams=teams)
session.add(user)

games = [Game(team1=t1, team2=t2) for t1, t2 in combinations(teams, 2)]
session.add_all(games)
session.commit()

for team in user.teams:
 print('Team:', team.id, 'games:', [g.id for g in team.games])
for game in session.query(Game):
 print(f'Game game.id: team game.team1.id vs game.team2.id')

which outputs:

Team: 2 games: [1, 3]
Team: 1 games: [1, 2]
Team: 3 games: [2, 3]
Game 1: team 1 vs 2
Game 2: team 1 vs 3
Game 3: team 2 vs 3