SQLAlchemy multiple foreign keys in one mapped class to the same primary key
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 theforeign_keys
argument alone; theprimaryjoin
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, ForeignKeyfrom sqlalchemy.orm import relationship, scoped_session, sessionmakerfrom sqlalchemy.ext.declarative import declarative_baseengine = 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 testq1 = session.query(Company).all()q2 = session.query(Stakeholder).all()
The latest documentation:
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')