Generally many-to-many relationship will required 3 table to manage both side of the relationship that map to list the entities on the other side. In this tutorial, we’ll look into self referential in many-to-many relationship.
Many To Many – Self Referential Relationship
Typical example, wordpress category and parent category structure. Example, category Java(id=1), subcategory SpringFramework(id=2, parent_id=1)
Association
Association table is to link both relational table with the attribute secondary argument to the relationship attribute. This table doesn’t need to be object model, and it should be manage by sqlalchemy.
category_tree = db.Table(
'category_tree',
db.Column('parent_id', db.Integer, db.ForeignKey('category.id')),
db.Column('children_id', db.Integer, db.ForeignKey('category.id'))
)
Now, we need higher level view of the relationship that we can use in sqlalchemy without directly working with the foreign key. For the relationship, we define a collection named as catogories. The first attribute of relationship is object name, secondary refer to the association table.
class Category(db.Model):
__tablename__ = 'category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
categories = db.relationship(
'Category', secondary=category_tree,
)
def __repr__(self):
return '<Category> {}'.format(self.id)
PrimaryJoin and SecondaryJoin
Sqlalchemy not able to distinguish the association due to 2 foreign key exist, it need a way to differentiate which belong to parent and children. In this case, we’ll use primaryjoin and secondaryjoin. Below is based on my personal understanding to define the usage of primaryjoin and secondaryjoin.
How it works.
From the point of view for the category, if we make the assumption of category to it’s children. The collection is children and the primaryjoin will be the first column in the association table, secondaryjoin will become second column.
class Category(db.Model):
__tablename__ = 'category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
children = db.relationship(
'Category',
secondary=category_tree,
primaryjoin=(category_tree.c.parent_id == id),
secondaryjoin=(category_tree.c.children_id == id),
backref=db.backref('parents', lazy='dynamic'),
lazy='dynamic'
)
def __repr__(self):
return '<Category> {}'.format(self.id)
If the point of view to this table is category to it’s parent. The collection is parent. The collection is parent and the primaryjoin is map to second column in the association table, secondaryjoin will become first column. Let’s test it out to the first scenario.
class Category(db.Model):
__tablename__ = 'category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
parents = db.relationship(
'Category',
secondary=category_tree,
primaryjoin=(category_tree.c.parent_id == id),
secondaryjoin=(category_tree.c.children_id == id),
backref=db.backref('children_categories', lazy='dynamic'),
lazy='dynamic'
)
def __repr__(self):
return '<Category> {}'.format(self.id)
Out Goal
Programming
+-- Java
+ SpringFramework
+ Hibernate
+-- Python
+ Flask
+ Django
Test Add
programming = Category(name="Programming") java = Category(name="Java") spring = Category(name="Spring") hibernate = Category(name="Hibernate") python = Category(name="Python") flask = Category(name="Flask") django = Category(name="Django") db.session.add(programming) db.session.add(java) db.session.add(spring) db.session.add(hibernate) db.session.add(python) db.session.add(flask) db.session.add(django) db.session.commit() +------------------------------- + id | name +------------------------------- + 1 | Programming +------------------------------- + 2 | Java +------------------------------- + 3 | Spring +------------------------------- + 4 | Hibernate +------------------------------- + 5 | Python +------------------------------- + 6 | Flask +------------------------------- + 7 | Django +-------------------------------
Testing Relationship
programming.children_categories.append(java) programming.children_categories.append(python) db.session.commit() programming.children_categories.all() [<Category> 2, <Category> 5] +------------------------------- + id | parent_id | children_id +------------------------------- + 1 | 1 | 2 +------------------------------- + 2 | 1 | 5 +------------------------------- java.children_categories.append(spring) java.children_categories.append(hibernate) python.children_categories.append(flask) python.children_categories.append(django) db.session.commit() java.children_categories.all() python.children_categories.all()
Testing Relationship Scenario 2
class Category(db.Model):
__tablename__ = 'category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
parents = db.relationship(
'Category',
secondary=category_tree,
primaryjoin=("Category.id==category_tree.c.children_id"),
secondaryjoin=("Category.id==category_tree.c.parent_id"),
backref=db.backref('children_categories', lazy='dynamic'),
lazy='dynamic'
)
def __repr__(self):
return '<Category> {}'.format(self.id)
Now I reverse the sequence of the primaryjoin and secondaryjoin and the relationship.
java.parents.append(programming) python.parents.append(programming) db.session.commit() programming.children_categories.all() [<Category> 2, <Category> 5] python.parents.all() [<Category> 1] java.parents.all() [<Category> 1] +------------------------------- + id | parent_id | children_id +------------------------------- + 1 | 1 | 2 +------------------------------- + 2 | 1 | 5 +-------------------------------
Fans and Idols
relation = db.Table(
'relation',
db.Column('myidol_id', db.Integer, db.ForeignKey('me.id')),
db.Column('myfans_id', db.Integer, db.ForeignKey('me.id'))
)
class Me(db.Model):
__tablename__ = 'me'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
myfans = db.relationship(
"Fan", secondary=relation,
primaryjoin=('Me.id==relation.c.myfans_id'),
secondaryjoin=('Me.id==relation.c.myidol_id'),
backref=db.backref('myidols', lazy='dynamic'),
lazy='dynamic')
def __repr__(self):
return '<Parent> {}'.format(self.id)
Test Add
me = Me(name="mingch") yong = Me(name="Yong") john = Me(name="John") jack = Me(name="Jack") rex = Me(name="Rex") db.session.add(me) db.session.add(yong) db.session.add(john) db.session.add(jack) db.session.add(rex) db.session.commit() yong.myfans.append(me) yong.myfans.append(jack) yong.myfans.append(rex) jack.myfans.append(yong) jack.myfans.append(john) me.myfans.append(yong) me.myfans.append(rex) db.session.commit() +------------------------------- + id | name +------------------------------- + 1 | mingch +------------------------------- + 2 | Yong +------------------------------- + 3 | John +------------------------------- + 4 | Jack +------------------------------- + 5 | Rex +-------------------------------
Yong Fans
list(map(lambda x: (x.name), yong.myfans.all())) ['mingch', 'Jack', 'Rex']
Jack Fans
list(map(lambda x: (x.name), jack.myfans.all())) ['Yong', 'John']
Rex Fans
list(map(lambda x: (x.name), rex.myfans.all())) []
Rex has zero fans, how about who are his idol?
list(map(lambda x: (x.name), rex.myidols.all())) ['mingch', 'Yong']