Defining two foreign keys in one entity targeting one other entity in SQLAlchemy

Question

I have the entities set up as follows

from sqlalchemy import ForeignKey
from sqlalchemy.orm import mapped_column, relationship, Mapped, DeclarativeBase

class Base(DeclarativeBase):
    pass

class UserEntity(Base):
    __tablename__ = "user"

    id: Mapped[str] = mapped_column(primary_key=True)
    last_message = relationship("MessageEntity", back_populates="from_user", uselist=False)

class MessageEntity(Base):
    __tablename__ = "message"

    id: Mapped[str] = mapped_column(primary_key=True)
    content: Mapped[str] = mapped_column()
    from_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    to_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    # relationships
    from_user = relationship("UserEntity", back_populates="last_message", foreign_keys=[from_user_id], uselist=False)

This describes that some users can have their last message, that is in a one-to-one relationship with the from_user column in the MessageEntity. Now I want to store to_user_id inside MessageEntity without any relationship with UserEntity, it should just store a User's ID, but now when I add this field I get

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

I'm not sure which relationship SQLAlchemy is trying to infer by having a to_user_id foreign key, but I only want it to store a simple id, with no strings attached. Is this possible?

Answer 1

It seems that you need to specify the foreign_keys on both sides of the relationship.

The first definition uses a string because the class is not defined yet, foreign_keys="[MessageEntity.from_user_id]", which is mentioned at the bottom of handling-multiple-join-paths.

• Also note that you can either include the list symbols inside the string: foreign_keys="[MessageEntity.from_user_id]"
• Or exclude the list symbols: foreign_keys="MessageEntity.from_user_id"
• BUT you cannot leave it outside if you are using strings, ie. DOES NOT WORK: foreign_keys=["MessageEntity.from_user_id"]

class UserEntity(Base):
    __tablename__ = "user"

    id: Mapped[str] = mapped_column(primary_key=True)
    last_message = relationship("MessageEntity", back_populates="from_user", uselist=False, foreign_keys="[MessageEntity.from_user_id]")

class MessageEntity(Base):
    __tablename__ = "message"

    id: Mapped[str] = mapped_column(primary_key=True)
    content: Mapped[str] = mapped_column()
    from_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    to_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    # relationships
    from_user = relationship("UserEntity", back_populates="last_message", foreign_keys=[from_user_id], uselist=False)