解决SQLAlchemy中多外键指向同一实体时的歧义关系问题

from sqlalchemy import ForeignKey, create_engine, Column, String
from sqlalchemy.orm import mapped_column, relationship, Mapped, DeclarativeBase, sessionmaker

class Base(DeclarativeBase):
    pass

class UserEntity(Base):
    __tablename__ = "user"

    id: Mapped[str] = mapped_column(primary_key=True)
    # 尝试建立与MessageEntity的关系，但未指定使用哪个外键
    last_message: Mapped[str] = 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(String)
    # 两个外键都指向user.id
    from_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    to_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))

    # from_user 关系明确指定了 foreign_keys
    from_user: Mapped["UserEntity"] = relationship("UserEntity", back_populates="last_message", foreign_keys=[from_user_id], uselist=False)

from sqlalchemy import ForeignKey, create_engine, Column, String
from sqlalchemy.orm import mapped_column, relationship, Mapped, DeclarativeBase, sessionmaker

class Base(DeclarativeBase):
    pass

class UserEntity(Base):
    __tablename__ = "user"

    id: Mapped[str] = mapped_column(primary_key=True)
    # 明确指定 last_message 关系应使用 MessageEntity.from_user_id
    # 注意：当MessageEntity类尚未完全定义时，需要使用字符串形式引用
    last_message: Mapped["MessageEntity"] = 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(String)
    from_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))
    to_user_id: Mapped[str] = mapped_column(ForeignKey('user.id'))

    # from_user 关系已经明确指定了 foreign_keys
    from_user: Mapped["UserEntity"] = relationship(
        "UserEntity", 
        back_populates="last_message", 
        foreign_keys=[from_user_id], 
        uselist=False
    )

# 示例用法
if __name__ == "__main__":
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    # 创建用户
    user1 = UserEntity(id="user1")
    user2 = UserEntity(id="user2")
    session.add_all([user1, user2])
    session.commit()

    # 创建消息
    message1 = MessageEntity(id="msg1", content="Hello user2", from_user_id="user1", to_user_id="user2")
    message2 = MessageEntity(id="msg2", content="Hi user1", from_user_id="user2", to_user_id="user1")
    session.add_all([message1, message2])
    session.commit()

    # 刷新以建立关系
    session.refresh(user1)
    session.refresh(user2)
    session.refresh(message1)
    session.refresh(message2)

    print(f"User1's last message (from_user): {user1.last_message.content if user1.last_message else 'None'}")
    print(f"Message1's sender (from_user): {message1.from_user.id}")

    # 验证 to_user_id 仅作为数据，不建立ORM关系
    # user1.to_messages 或 message1.to_user 这样的关系并未定义
    print(f"Message1's receiver ID (to_user_id): {message1.to_user_id}")

    session.close()