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

Question

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

Answer 1

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