How to create many-to-one relations between subclasses in the same Joined Table inheritance hirarchy

19 Views Asked by At

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?

0

There are 0 best solutions below