Sqlalchemy Multiple Foreign Keys in secondary table

Question

I started off with this setup:

checklist_participants = Table('checklist_participants', base.metadata,
                               Column('checklist_id', Integer, ForeignKey('checklists.id', ondelete='cascade')),
                               Column('user_id', Integer, ForeignKey('users.id', ondelete='cascade'))
                               )

class Checklist(base):
    __tablename__ = 'checklists'

    id = Column(Integer, primary_key=True)
    participants = relationship('User', secondary='checklist_participants', back_populates='joined_checklists')

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    joined_checklists = relationship('Checklist', secondary='checklist_participants', back_populates='participants')

In order to give an extra column referring_user to the secondary table checklist_participants, I moved it into its own class like so:

# new class
class Participant(base):
    checklist_id = Column(Integer, ForeignKey('checklists.id', ondelete='cascade'), primary_key=True)
    checklist = relationship('Checklist', foreign_keys=checklist_id)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='cascade'), primary_key=True)
    user = relationship('User', foreign_keys=user_id)
    # new column
    referring_user_id = Column(Integer, ForeignKey('users.id', ondelete='set null'))
    referring_user = relationship('User', foreign_keys=referring_user_id)

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    joined_checklists = relationship('Checklist', secondary='checklist_participants', back_populates='participants')
    # new relationship definition
    referred_participants = relationship('Participant', back_populates='referring_user')

Simply moving the join table into its own class didn't cause any problems. But when I introduced the new column and tried to run a simple query:
session.query(User).filter(User.id == input_id).scalar()
I got this error:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Checklist.participants - there are multiple foreign key paths linking the tables via secondary table 'checklist_participants'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

What do I need to define on my models to fix this issue?

Answer 1

With the help of @IljaEverilä, I realized why the relationships were ambiguous. I couldn't figure out, how to tell Checklist.participants which foreign key on the join table was the one to use. Instead, I removed the secondary argument from my relationships and made Checklist and User relationships connect to Participant instead of referencing each other directly. That way, Checklist didn't need to know about the foreign keys used in Participant.
However, these changes moved the problem into the User class, where I was now referencing Participant twice. This time, I was able to define the foreign_keys argument on these two ManyToOne relationships to fix the ambiguity:

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    participants = relationship('Participant', back_populates='user', foreign_keys='Participant.user_id')
    referred_participants = relationship('Participant', back_populates='referring_user', foreign_keys='Participant.referring_user_id')


class Checklist(base):
    __tablename__ = 'checklists'

    id = Column(Integer, primary_key=True)
    participants = relationship('Participant', back_populates='checklist')

class Participant(base):
    __tablename__ = 'checklist_participants'

    checklist_id = Column(Integer, ForeignKey('checklists.id', ondelete='cascade'), primary_key=True)
    checklist = relationship('Checklist', foreign_keys=checklist_id)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='cascade'), primary_key=True)
    user = relationship('User', foreign_keys=user_id)
    referring_user_id = Column(Integer, ForeignKey('users.id', ondelete='set null'))
    referring_user = relationship('User', foreign_keys=referring_user_id)

Answer 2

Declarative version, worked for me:

 class Offer(Base):
    __tablename__ = "offers"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=True)
    user: Mapped["User"] = relationship(back_populates= "offers", foreign_keys=[user_id])

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    offers: Mapped[List["Offer"]] = relationship(back_populates= "user", foreign_keys=[Offer.user_id])