I am trying to create sqlalchemy relationship between two tables which relies on a value of another table. My UserSports table has a relationship with the Baseball table (as well as other sports that they may have played). I also have a Sports enums table which joins to the Users table. The goal is to create a relationship between UserSports and Baseball using Sports enums. user_sports.id and baseball.user_sport_id is a one-to-one relationship. The sql would be something like:
SELECT *
FROM user_sports us
JOIN sports s ON s.id = us.sport_id
AND s.sport = 'baseball'
JOIN baseball b ON b.user_sport_id = us.id
class UserSports(Base):
__tablename__ = "user_sports"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
user_id: Mapped[int] = mapped_column(nullable=False)
sport_id: Mapped[int] = mapped_column(ForeignKey('states.id', nullable=False)
sports: Mapped['Sports'] = relationship(back_populates='user_sports', foreign_keys=[sport_id])
baseball: Mapped['Baseball'] = relationship(back_populates='user_sports', primaryjoin='and_(UserSports.id == Baseball.user_sport_id, UserSports.sport_id.has(sport='baseball'))'
class Baseball(Base):
__tablename__ = "baseball"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
user_id: Mapped[int] = mapped(column(ForeignKey('users.id', nullable=False)
position: Mapped[str] = mapped_column(TEXT, nullable=False)
team: Mapped[str] = mapped_column(TEXT, nullable=False)
number: Mapped[int] = mapped_column(nullable=False)
user_sports: Mapped['UserSports'] = relationship(back_populates='baseball', foreign_key='user_id')
class Sports(Base):
__tablename__ = "sports"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
sport: Mapped[str] = mapped_column(TEXT, nullable=False)
user_sports: Mappped[List["UserSports"]] = relationship(back_populates='sports', foreign_key='UserSports.sport_id')
When I run this I get the following error.
Property 'sport_id' is not an instance of ColumnProperty (i.e. does not correspond directly to a Column).
I know I am messing up somewhere with my primaryjoin, but I am not sure how to fix it. I do not want to pass the id from sport_id because I won’t know the id. I tried following the docs for help.
ogdoc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.