I'm trying to use a Joined Table Inheritance pattern to map an inheritance hirarchy where some of the subclasses have relationships to each other. Below is a toy example of the kind of thing I'm trying to achieve based on the example in the documentation (using sqla 1.4):
from sqlalchemy import Column, Integer, Identity, String, ForeignKey, ARRAY, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employees'
id = Column(Integer, Identity(0), primary_key=True)
employee_type = Column(String)
__mapper_args__ = {
"polymorphic_identity": "component",
"polymorphic_on": employee_type,
}
class Manager(Employee):
__tablename__ = 'managers'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
direct_reports = relationship("Engineer", back_populates="manager")
__mapper_args__ = {
"polymorphic_identity": "manager",
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
manager_id = Column(Integer, ForeignKey('managers.id'))
manager = relationship("Manager", back_populates="direct_reports", foreign_keys=[manager_id])
__mapper_args__ = {
"polymorphic_identity": "engineer",
}
This simple example fails during import with sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Manager.direct_reports - 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. which makes sense I guess.
The following fails with sqlalchemy.exc.ArgumentError: Engineer.manager and back-reference Manager.direct_reports are both of the same direction symbol('MANYTOONE'). Did you mean to set remote_side on the many-to-one side ? when creating an instance of Manager:
class Manager(Employee):
__tablename__ = 'managers'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
direct_reports = relationship("Engineer", back_populates="manager")
__mapper_args__ = {
"polymorphic_identity": "manager",
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
manager = relationship("Manager", back_populates="direct_reports", foreign_keys=[id])
__mapper_args__ = {
"polymorphic_identity": "engineer",
}
Setting remote_side=[id] doesn't help.
What does work is:
class Manager(Employee):
__tablename__ = 'managers'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
__mapper_args__ = {
"polymorphic_identity": "manager",
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
manager = relationship("Manager", backref=backref("direct_reports"))
__mapper_args__ = {
"polymorphic_identity": "engineer",
}
however, the documentation describes backref as legacy and encourages avoiding it's use. So the question is, is there a way to create this design without using backref? Is this just a bad design?