I'm trying to establish a many-to-many relationship with only one table and an association table in between. The problem I'm facing is that appending to the relationship in one object doesn't back populate in the corresponding object unless I make the relationship directed. What I want in this case is to make the relationship undirected though.
undirected if PERSON A is friends with PERSON B, PERSON B is friends with PERSON A as well
directed if PERSON A follows PERSON B, PERSON B has a follower in PERSON A
from sqlalchemy import Table, ForeignKey, Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
engine = create_engine('postgresql+psycopg2://deniz@localhost:54321/sqla', echo=False)
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
friends_association = Table(
'friends_association', Base.metadata,
Column('person1_id', ForeignKey('person.id'), primary_key=True),
Column('person2_id', ForeignKey('person.id'), primary_key=True)
)
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String)
friends = relationship('Person', back_populates='friends', secondary=friends_association,
primaryjoin=friends_association.c.person1_id == id,
secondaryjoin=friends_association.c.person2_id == id
)
def __repr__(self) -> str:
return f"<Person {self.name}>"
Base.metadata.create_all()
ben = Person(name='Ben')
deniz = Person(name='Deniz')
paul = Person(name='Paul')
deniz.friends.extend([ben, paul])
session = Session()
session.add(deniz)
session.commit()
persons = session.query(Person)
for person in persons:
print((f"Person {person.name} has friends {person.friends}"))
OUTPUT:
Person Deniz is friends with [<Person Ben>, <Person Paul>]
Person Ben is friends with []
Person Paul is friends with []
if I change the undirected 'friends'-relationship attribute of the Person class to a directed follower-following-relationship like this, the data is back populated correctly.
class Person(Base):
...
followers = relationship('Person', backref='following', secondary=friends_association,
primaryjoin=friends_association.c.person1_id == id,
secondaryjoin=friends_association.c.person2_id == id
)
persons = session.query(Person)
for person in persons:
print((f"Person {person.name} has followers {person.followers} and is following {person.following}"))
OUTPUT:
Person Deniz has followers [<Person Ben>, <Person Paul>] and is following []
Person Ben has followers [] and is following [<Person Deniz>]
Person Paul has followers [] and is following [<Person Deniz>]