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

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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()

Method 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')

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0