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