SQLAlchemy multiple foreign keys in one mapped class to the same primary key

Question

Am trying to setup a postgresql table that has two foreign keys that point to the same primary key in another table.

When I run the script I get the error

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Company.stakeholder - 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.

That is the exact error in the SQLAlchemy Documentation yet when I replicate what they have offered as a solution the error doesn't go away. What could I be doing wrong?

#The business case here is that a company can be a stakeholder in another company. class Company(Base):     __tablename__ = 'company'     id = Column(Integer, primary_key=True)     name = Column(String(50), nullable=False)  class Stakeholder(Base):     __tablename__ = 'stakeholder'     id = Column(Integer, primary_key=True)     company_id = Column(Integer, ForeignKey('company.id'), nullable=False)     stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)     company = relationship("Company", foreign_keys='company_id')     stakeholder = relationship("Company", foreign_keys='stakeholder_id') 

I have seen similar questions here but some of the answers recommend one uses a primaryjoin yet in the documentation it states that you don't need the primaryjoin in this situation.

Answer 1

Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths

Changed in version 0.8: relationship() can resolve ambiguity between foreign key targets on the basis of the foreign_keys argument alone; the primaryjoin argument is no longer needed in this situation.

---

Self-contained code below works with sqlalchemy>=0.9:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import relationship, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base  engine = create_engine(u'sqlite:///:memory:', echo=True) session = scoped_session(sessionmaker(bind=engine)) Base = declarative_base()  #The business case here is that a company can be a stakeholder in another company. class Company(Base):     __tablename__ = 'company'     id = Column(Integer, primary_key=True)     name = Column(String(50), nullable=False)  class Stakeholder(Base):     __tablename__ = 'stakeholder'     id = Column(Integer, primary_key=True)     company_id = Column(Integer, ForeignKey('company.id'), nullable=False)     stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)     company = relationship("Company", foreign_keys=[company_id])     stakeholder = relationship("Company", foreign_keys=[stakeholder_id])  Base.metadata.create_all(engine)  # simple query test q1 = session.query(Company).all() q2 = session.query(Stakeholder).all() 

Answer 2

The latest documentation:

• http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#handling-multiple-join-paths

The form of foreign_keys= in the documentation produces a NameError, not sure how it is expected to work when the class hasn't been created yet. With some hacking I was able to succeed with this:

company_id = Column(Integer, ForeignKey('company.id'), nullable=False) company = relationship("Company", foreign_keys='Stakeholder.company_id')  stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False) stakeholder = relationship("Company",                             foreign_keys='Stakeholder.stakeholder_id') 

In other words:

… foreign_keys='CurrentClass.thing_id')