I’ve found examples for a self-referencing many-to-many relationship with an association table. How can I achieve the same using an association object?
The code below is based on: How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?
from sqlalchemy import Table, Column, Integer, ForeignKey from db.common import Base from sqlalchemy.orm import relationship M2M = Table('m2m', Base.metadata, Column('entity_parent_id', Integer, ForeignKey('entity.id'), primary_key=True), Column('entity_child_id', Integer, ForeignKey('entity.id'), primary_key=True), ) class Entity(Base): __tablename__ = 'entity' id = Column(Integer, primary_key=True) entity_childs = relationship("Entity", secondary=M2M, primaryjoin="Enity.id==m2m.c.entity_parent_id", secondaryjoin="Enity.id==m2m.c.entity_child_id", ) entity_parents = relationship("Entity", secondary=M2M, primaryjoin="Enity.id==m2m.c.entity_child_id", secondaryjoin="Enity.id==m2m.c.entity_parent_id", )
Advertisement
Answer
The following approach uses an association object instead of an association table to get a self-referencing many-to-many relationship:
from sqlalchemy import Column, Integer, ForeignKey, create_engine, String from sqlalchemy.orm import relationship, sessionmaker from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class EntityAssociation(Base): __tablename__ = 'entity_association' entity_parent_id = Column(Integer, ForeignKey('entity.id'), primary_key=True) entity_child_id = Column(Integer, ForeignKey('entity.id'), primary_key=True) class Entity(Base): __tablename__ = 'entity' id = Column(Integer, primary_key=True) name = Column(String) entity_childs = relationship('Entity', secondary='entity_association', primaryjoin=id==EntityAssociation.entity_parent_id, secondaryjoin=id==EntityAssociation.entity_child_id, backref='childs') entity_parents = relationship('Entity', secondary='entity_association', primaryjoin=id==EntityAssociation.entity_child_id, secondaryjoin=id==EntityAssociation.entity_parent_id, backref='parents') def __repr__(self): return f'<Entity(name={self.name})>' if __name__ == '__main__': engine = create_engine('sqlite://') Base.metadata.create_all(engine) Session = sessionmaker(engine) db = Session() parent1 = Entity(name='parent1') parent2 = Entity(name='parent2') child1 = Entity(name='child1') child2 = Entity(name='child2') parent1.entity_childs = [child1, child2] parent2.entity_childs = [child2] db.add(parent1) db.add(parent2) db.add(child1) db.add(child2) db.commit() entities = db.query(Entity).all() for entity in entities: print(entity) print(' Parent: ', entity.entity_parents) print(' Childs: ', entity.entity_childs) print()
This will have the following result:
<Entity(name=parent1)> Parent: [] Childs: [<Entity(name=child1)>, <Entity(name=child2)>] <Entity(name=child1)> Parent: [<Entity(name=parent1)>] Childs: [] <Entity(name=child2)> Parent: [<Entity(name=parent1)>, <Entity(name=parent2)>] Childs: [] <Entity(name=parent2)> Parent: [] Childs: [<Entity(name=child2)>]