SQLAlchemy one-to-many relationship for single table inheritance - declarative

Question

Basically, I have this model, where I compared the BaseNode class and two subclasses in one table. The fact is that I need one of the subclasses to have a one-to-many relationship with the other subclass. Thus, in sorting, this is a relationship with another row of another class (subclass), but in the same table. Do you think I can write it using declarative syntax ?.

Note. Due to other relationships in my model, if possible, I really need to stick with unidirectional inheritance.

class BaseNode(DBBase): __tablename__ = 'base_node' id = Column(Integer, primary_key=True) discriminator = Column('type', String(50)) __mapper_args__ = {'polymorphic_on': discriminator} class NodeTypeA(BaseNode): __mapper_args__ = {'polymorphic_identity': 'NodeTypeA'} typeB_children = relationship('NodeTypeB', backref='parent_node') class NodeTypeB(BaseNode): __mapper_args__ = {'polymorphic_identity': 'NodeTypeB'} parent_id = Column(Integer, ForeignKey('base_node.id')) 

Using this code, you will throw:

sqlalchemy.exc.ArgumentError: NodeTypeA.typeB_children and back-reference NodeTypeB.parent_node have the same direction, did you mean to set remote_side to many-to-one?

Any ideas or suggestions?

Answer 1

I struggled with this myself before. I was able to get this relationship with a relational relationship:

 class Employee(Base): __tablename__ = 'employee' id = Column(Integer, primary_key=True) name = Column(String(64), nullable=False) Employee.manager_id = Column(Integer, ForeignKey(Employee.id)) Employee.manager = relationship(Employee, backref='subordinates', remote_side=Employee.id) 

Please note that manager and manager_id are " manager_id monkeys" because you cannot do self-promotion in class definitions.

So, in your example, I would assume the following:

 class NodeTypeA(BaseNode): __mapper_args__ = {'polymorphic_identity': 'NodeTypeA'} typeB_children = relationship('NodeTypeB', backref='parent_node', remote_side='NodeTypeB.parent_id') 

EDIT . Essentially, your mistake tells you that the relationship and its feedback are the same. Thus, no matter what rules SA applies to determine what relationships are at the table level, they do not expect the information you provide.

I found out that just saying mycolumn=relationship(OtherTable) in your declarative class will cause mycolumn be a list, suggesting that SA can detect explicit relationships. Therefore, if you really want the object to have a reference to the parent and not its children, you can define parent=relationship(OtherTable, backref='children', remote_side=OtherTable.id) in the child table. This determines both directions of the parent-child relationship.