pythonsqlalchemymany-to-manyself-referencing-table

Self-referencing many-to-many relationship with an association object in SQLAlchemy


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",
                                     )


Solution

  • 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)>]