我可以在与SQLalchemy的同一表中指出两个外国钥匙吗？

Question

因此，我有两个表：

# "parent" table, each user has multiple "reports_received" and "reports_made"
class User(Base):
    __tablename__ = "users"

    id: str = Column("id", String(16), primary_key=True)

    reports_received = relationship("Report", back_populates="reported_user")
    reports_made = relationship("Report", back_populates="reporting_user")

# "child" table, each report has one "reported_user" and "reporting_user"
class Report(Base):
    __tablename__ = "reports"

    report_id: int = Column("report_id", Integer(), primary_key=True)

    reported_user_id = Column("reported_user_id", ForeignKey("users.id"))
    reporting_user_id = Column("reporting_user_id", ForeignKey("users.id"))

    reported_user = relationship("User",back_populates="reports_received", foreign_keys=[reported_user_id])
    reporting_user = relationship("User", back_populates="reports_made", foreign_keys=[reporting_user_id])

报告应具有record_user和reporting_user，都存在于“用户”表中。 但这就是我尝试创建一些用户并分配一些报告时得到的：

sqlalchemy.exc.biguulforeignkeyserror：无法确定关系用户上的父/子表之间的加入条件。Reports_received-有多个链接表的外键路径。指定“ forefer_keys”参数，提供了这些列的列表，这些列应算作包含对父表的外键引用。

我不明白为什么会给我这个错误，因为我已经指定了外国键。我是否缺少其他事情，或者做这样的事情没有多大意义？

Answer 1

Sqlalchemy只需要更多帮助就可以通过在用户类中指定forefer_keys =来调和关系。切换类声明的顺序并添加这些声明，就像

# "child" table, each report has one "reported_user" and "reporting_user"
class Report(Base):
    __tablename__ = "reports"

    report_id: int = Column("report_id", Integer(), primary_key=True)

    reported_user_id = Column("reported_user_id", ForeignKey("users.id"))
    reporting_user_id = Column("reporting_user_id", ForeignKey("users.id"))

    reported_user = relationship(
        "User",
        back_populates="reports_received",
        foreign_keys=[reported_user_id],
    )
    reporting_user = relationship(
        "User", back_populates="reports_made", foreign_keys=[reporting_user_id]
    )


# "parent" table, each user has multiple "reports_received" and "reports_made"
class User(Base):
    __tablename__ = "users"

    id: str = Column("id", String(16), primary_key=True)

    reports_received = relationship(
        "Report",
        back_populates="reported_user",
        foreign_keys=[Report.reported_user_id],
    )
    reports_made = relationship(
        "Report",
        back_populates="reporting_user",
        foreign_keys=[Report.reporting_user_id],
    )

让我们这样做一样

engine.echo = True
with Session(engine) as sess:
    gord = User(id="Gord")
    alexander = User(id="Alexander")
    sess.add(Report(reported_user=gord, reporting_user=alexander))
    sess.commit()

"""SQL emitted
INSERT INTO users (id) VALUES (?)
[generated in 0.00036s] [('Gord',), ('Alexander',)]
INSERT INTO reports (reported_user_id, reporting_user_id) VALUES (?, ?)
[generated in 0.00031s] ('Gord', 'Alexander')
"""