Can I have two foreign keys refering to different records in the same table with SQLAlchemy?

user_15007665 0
Viewed 33

So I have two tables:

# "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])

Reports should have a reported_user and a reporting_user, both being present in the "users" table. But this is what I get when I try to create some users and assign them some reports:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.reports_received - 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 can't understand why it's giving me this error, since I've already specified the foreign keys. Is there something else I'm missing or does doing something like this not make much sense?

sqlalchemy foreign-keys python
1 Answers

SQLAlchemy just needs a little more help to reconcile the relationships by specifying the foreign_keys= in the User class as well. Switching the order of the class declarations and adding those, as in

# "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],
    )

lets us do this

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')
"""
user_21443900
Related