python - Sqlalchemy error, multiple foreign keys references to the same table and column

Question

I already tried a solution from this question and this but failed (on of these solutions are present here), I don't know what to say additionally, logically both FK (sender and recipient) must be present in users, technically all looks are correct here

class User(Base):
    __tablename__ = "users"
    # # # META # # #
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)    
    # # # RELATIONSHIPS # # #
    messages = relationship("Message",  back_populates="users", cascade="all, delete")


class Message(Base):
    __tablename__ = "messages"

    id = Column(Integer, primary_key=True)
    sender = Column(Integer, ForeignKey('users.id'), nullable=False)
    recipient = Column(Integer, ForeignKey('users.id'), nullable=False)
    data = Column(String, nullable=False)
    created_datetime = Column(DateTime, server_default=func.now())
    # # # RELATIONSHIPS # # #
    senders = relationship("User", foreign_keys=[sender], back_populates="messages")
    recipients = relationship("User", foreign_keys=[recipient], back_populates="messages")


sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.messages - 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.
python-BaseException

What I tried:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    sent_messages = relationship("Message",  back_populates="users", cascade="all, delete")
    received_messages = relationship("Message",  back_populates="users", cascade="all, delete")


class Message(Base):
    __tablename__ = "messages"

    id = Column(Integer, primary_key=True)
    sender = Column(Integer, ForeignKey('users.id'), nullable=False)
    recipient = Column(Integer, ForeignKey('users.id'), nullable=False)
    data = Column(String, nullable=False)
    created_datetime = Column(DateTime, server_default=func.now())
    senders = relationship("User", foreign_keys=[sender], back_populates="messages")
    recipients = relationship("User", foreign_keys=[recipient], back_populates="messages")

Could not determine join condition between parent/child tables on relationship User.sent_messages - 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.

question from:https://stackoverflow.com/questions/65941555/sqlalchemy-error-multiple-foreign-keys-references-to-the-same-table-and-column

Answer 1

This works for me in version 1.4:

class Message(Base):
    __tablename__ = "messages"
    id = sa.Column(sa.Integer, primary_key=True)
    sender_id = sa.Column(
        sa.String(10), sa.ForeignKey("users.id"), nullable=False
    )
    recipient_id = sa.Column(
        sa.String(10), sa.ForeignKey("users.id"), nullable=False
    )
    data = sa.Column(sa.String, nullable=False)
    sender = relationship(
        "User", foreign_keys=[sender_id], back_populates="sent_messages"
    )
    recipient = relationship(
        "User", foreign_keys=[recipient_id], back_populates="received_messages"
    )

    def __repr__(self):
        return f"<Message(id={self.id}, data='{self.data}')>"


class User(Base):
    __tablename__ = "users"
    id = sa.Column(sa.String(10), primary_key=True)
    sent_messages = relationship(
        "Message",
        foreign_keys=[Message.sender_id],
        back_populates="sender",
        cascade="all, delete",
    )
    received_messages = relationship(
        "Message",
        foreign_keys=[Message.recipient_id],
        back_populates="recipient",
        cascade="all, delete",
    )

    def __repr__(self):
        return f"<User(id='{self.id}')>"


Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)

with sa.orm.Session(engine, future=True) as session:
    gord = User(id="gord")
    david = User(id="david")
    msg = Message(sender=gord, recipient=david, data="Hello, David!")
    session.add_all([gord, david, msg])
    session.commit()

    results = session.execute(sa.text("SELECT * FROM messages")).fetchall()
    print(results)
    # [(1, 'gord', 'david', 'Hello, David!')]

    print(david.received_messages)
    # [<Message(id=1, data='Hello, David!')>]