Self referencing many-to-many relationship with extra column in association object

Question

I am new in Sqlalchemy and trying to achieve the following goal with relationship():

1. There is an User table which stores user data.
2. Every user is able to invite other user with an invite_code.
3. Every user keeps a list of invitation, every invitation includes the invite_code and the invitee User

I think the relationship between User and Invitation is one-to-many. Since Invitation contains User, then I think it is probably better to use self-referential relationship to represent the inviter-to-invitaions(invitees) relationship and use an association object to store the invite_code.

I checked the sqlalchemy documentation and the question, tried to implement the classed like this:

from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Invitation(Base):
    __tablename__ = 'invitation'

    invite_code = Column(Integer)
    inviter_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    invitee_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    
    invitee = relationship('User') #Need HELP here

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    inviters = relationship('User',
                            secondary='invitation',
                            primaryjoin=id==Invitation.invitee_id,
                            secondaryjoin=id==Invitation.inviter_id,
                            backref='invitees')

    invitations = relationship('Invitation')# Need HELP here

    def __repr__(self):
        return f'User: {self.name}'

if __name__ == '__main__':
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    Session = sessionmaker(engine)

    db = Session()

    inviter1 = User(name='inviter1')
    inviter2 = User(name='inviter2')

    invitee1= User(name='invitee1')
    invitee2 = User(name='invitee2')

    inviter1.invitees = [invitee1, invitee2]
    inviter2.invitees = [invitee1]

    db.add(inviter1)
    db.add(inviter2)
    db.add(invitee1)
    db.add(invitee2)
    db.commit()

    users = db.query(User).all()
    for user in users:
        print(user)
        print(' Inviter: ', user.inviters)
        print(' Invitee: ', user.invitees)
        print()

If the lines with comment #Need HELP here are deleted, I can get the corresponding inviters and invitees, but cannot get the invite_code. If the #Need HELP here code are added, the error is:

Exception has occurred: AmbiguousForeignKeysError
Could not determine join condition between parent/child tables on relationship Invitation.invitee - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

Is there a way to add extra data column in association object like association object for many-to-many relationship for self referential table?

Sorry for the too much text, I didn't find any reference document on the web.

Answer 1

Finally, I figured it out with the help of foreign_keys:

from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    sent_invitations = relationship('Invitation', foreign_keys='Invitation.inviter_id', back_populates='inviter', cascade='all, delete')
    received_invitations=relationship('Invitation', foreign_keys='Invitation.invitee_id', back_populates='invitee', cascade='all, delete')
    def __repr__(self):
        return f'User: {self.name}'


class Invitation(Base):
    __tablename__ = 'invitation'

    id = Column(Integer, primary_key=True)
    invite_code = Column(Integer)
    inviter_id = Column(Integer, ForeignKey('user.id'))
    invitee_id = Column(Integer, ForeignKey('user.id'))
    
    inviter=relationship('User', foreign_keys=[inviter_id], back_populates='sent_invitations')
    invitee=relationship('User', foreign_keys=[invitee_id], back_populates='received_invitations')

    def __repr__(self):
        return f'Invitation: {self.inviter} invited {self.invitee} with {self.invite_code}'


if __name__ == '__main__':
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    Session = sessionmaker(engine)

    db = Session()

    inviter1 = User(name='inviter1')
    inviter2 = User(name='inviter2')

    invitee1= User(name='invitee1')
    invitee2 = User(name='invitee2')

    invitation1 = Invitation(invite_code=50, inviter=inviter1, invitee=invitee1)
    invitation2 = Invitation(invite_code=20, inviter=inviter2, invitee=invitee2)
    invitation3 = Invitation(invite_code=22, inviter=inviter1, invitee=inviter2)
    invitation4 = Invitation(invite_code=44, inviter=invitee1, invitee=inviter2)
    db.add(inviter1)
    db.add(inviter2)
    db.add(invitee1)
    db.add(invitee2)
    db.commit()

    users = db.query(User).all()
    for user in users:
        print(user)
        print(' sent_invitation: ', user.sent_invitations)
        print(' received_invitation: ', user.received_invitations)
        print()

    invitations = db.query(Invitation).all()
    for invitation in invitations:
        print(invitation)

    db.delete(inviter1)
    db.delete(invitee2)
    db.commit()