python - sqlalchemy 一对多关系，其中同一个外键在模型中使用了两次

Question

我有一个像这样的简单模型

class ChatMessage(Base):
    __tablename__ = 'chat_messages'
   id = Column(Integer, ForeignKey('messages.id'))
   sender_id = Column(Integer, ForeignKey('users.id'))
   receiver_id = Column(Integer, ForeignKey('users.id'))

我尝试添加与用户相关的消息。

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    chat_messages = relationship("ChatMessage", backref="participants",
                             foreign_keys="[ChatMessage.sender_id, ChatMessage.receiver_id]")

它给出:

InvalidRequestError:一个或多个映射器未能初始化 - 无法继续其他映射器的初始化。原始异常是:无法确定关系 User.chat_messages 上的父/子表之间的连接条件 - 有多个外键路径链接表。指定“foreign_keys”参数，提供应计为包含对父表的外键引用的那些列的列表。

Answer 1

在创建两个类之后设置特殊关系。

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

engine = sa.create_engine('sqlite:///test.db', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base(bind=engine)


class User(Base):
    __tablename__ = 'users'
    id = sa.Column(sa.Integer, primary_key=True)


class ChatMessage(Base):
    __tablename__ = 'chat_messages'
    id = sa.Column(sa.Integer, primary_key=True)
    sender_id = sa.Column(sa.Integer, sa.ForeignKey(User.id))
    receiver_id = sa.Column(sa.Integer, sa.ForeignKey(User.id))
    content = sa.Column(sa.String, nullable=False)

    __table_args__ = (
        sa.CheckConstraint(receiver_id != sender_id),
    )

    sender = relationship(User, foreign_keys=sender_id, backref='sent_chat_messages')
    receiver = relationship(User, foreign_keys=receiver_id, backref='received_chat_messages')


User.chat_messages = relationship(ChatMessage, primaryjoin=sa.or_(
    User.id == ChatMessage.sender_id,
    User.id == ChatMessage.receiver_id
), backref=backref('participants', uselist=True, viewonly=True), viewonly=True)

使用

viewonly=True 是因为此关系使用自定义连接，如果数据更改，SQLAlchemy 将不知道该怎么做。例如，如果您附加到参与者，它会将用户添加为发送者还是接收者？