sql - 表和双向/反向填充之间的多个外键路径

Question

我试图按照 SQLAlchemy 文档为用户和用户之间的消息建立一个简单的数据库。我希望每条消息都有两个指向用户表的外键，对应于发件人和收件人，并且我希望有对应关系直接给我对象，即用户有字段messagesSent，messagesReceived。

https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-many解释了如何获得双向关系，以及https://docs.sqlalchemy.org/en/14/orm/join_conditions .html解释了如何设置与两个外键字段的关系。两者都适合我，但我似乎无法找到将它们结合起来的方法。

代码：

class UserDB(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    email = Column(String)
    avatar = Column(String)

    #### messagesSent = relationship("MessageDB")
    #### messagesReceived = relationship("MessageDB")

class MessageDB(Base):
    __tablename__ = "messages"

    id = Column(Integer, primary_key=True, index=True)
    content = Column(String)
    date = Column(DateTime)

    sender_id = Column(Integer, ForeignKey("users.id"))
    sender = relationship("UserDB", foreign_keys=sender_id)

    recipient_id = Column(Integer, ForeignKey("users.id"))
    recipient = relationship("UserDB", foreign_keys=recipient_id)

这可行，但缺乏双向性。注意 UserDB 类中的注释行，这些是我想要回填的字段。如果我取消注释并添加back_populates到相应的对，映射器将不会初始化，ala

sqlalchemy.exc.InvalidRequestError: 
One or more mappers failed to initialize - can't proceed with initialization of other mappers. 
Triggering mapper: 'mapped class UserDB->users'. 
Original exception was: 
Could not determine join condition between parent/child tables on relationship UserDB.messagesSent - 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.

而且我不能在用户表中指定外键，因为它没有任何外键。我该如何进行这项工作？

标签： sqlsqlalchemyormforeign-keysrelational-database