SQLalchemy: I need to build relationships for three tables: one-to-many, many-to-many, many-to-one

Question

I need to build tables like in the picture, it turns out to be a very complex structure where I understand what relationships need to be used, but I don't understand how to do it example pic

class ChampionshipsTable(Base):
    __tablename__ = "championships"

    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = mapped_column(Integer(), unique=True)
    championship_name = mapped_column(String(50))


class TournamentsTable(Base):
    __tablename__ = "tournaments"

    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = mapped_column(ForeignKey("championships.CId"))
    TId: Mapped[int] = mapped_column(Integer(), unique=True)
    tournament_name: Mapped[str] = mapped_column(String(50))

class MatchesTable(Base):
    __tablename__ = "matches"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = mapped_column(ForeignKey("championships.CId"))
    TId: Mapped[int] = mapped_column(ForeignKey("tournaments.TId"))
    match_id: Mapped[int] = mapped_column(Integer(), unique=True)

Is there any universal solution?

I try to build tables like in many-to-many example in sqlalchemy like that:

class ChampionshipsTable(Base):
    __tablename__ = "championships"

    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = relationship("TournamentsTable", secondary="matches", back_populates="championships")
    championship_name = mapped_column(String(50))


class TournamentsTable(Base):
    __tablename__ = "tournaments"

    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = relationship("ChampionshipsTable", back_populates="tournaments")
    TId: Mapped[int] = relationship("MatchesTable", backref="tournaments")
    tournament_name: Mapped[str] = mapped_column(String(50))

class MatchesTable(Base):
    __tablename__ = "matches"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    CId: Mapped[int] = mapped_column(ForeignKey("championships.id"))
    TId: Mapped[int] = mapped_column(ForeignKey("tournaments.id"))
    match_id: Mapped[int] = mapped_column(Integer(), unique=True)

and thats what i got: sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper[TournamentsTable(tournaments)]' has no property 'championships'. If this property was indicated from other mappers or configure events, ensure registry.configure() has been called. I dont know what to do

Answer 1

I think this is just these relationships:

• championships 1-to-many tournaments
• tournaments 1-to-many matches
• championships 1-to-many matches

You need to use Mapped for relationships, like this:

class Championship(Base):
    __tablename__ = "championships"

    id: Mapped[int] = mapped_column(primary_key=True)
    name = mapped_column(String(50), nullable=False)

    tournaments: Mapped[list["Tournament"]] = relationship("Tournament", back_populates="championship")
    # Matches across ALL tournaments.
    matches: Mapped[list["Match"]] = relationship("Match", back_populates="championship")


class Tournament(Base):
    __tablename__ = "tournaments"

    id: Mapped[int] = mapped_column(primary_key=True)
    c_id: Mapped[int] = mapped_column(ForeignKey("championships.id"), nullable=False)
    name: Mapped[str] = mapped_column(String(50), nullable=False)
    championship: Mapped["Championship"] = relationship("Championship", back_populates="tournaments")
    # Matches only within this tournament.
    matches: Mapped[list["Match"]] = relationship("Match", back_populates="tournament")

class Match(Base):
    __tablename__ = "matches"

    id: Mapped[int] = mapped_column(primary_key=True)
    c_id: Mapped[int] = mapped_column(ForeignKey("championships.id"), nullable=False)
    t_id: Mapped[int] = mapped_column(ForeignKey("tournaments.id"), nullable=False)
    championship: Mapped[Championship] = relationship(Championship, back_populates="matches")
    tournament: Mapped[Tournament] = relationship(Tournament, back_populates="matches")