Flask – SQLAlchemy Self Referential Relationship

Flask – SQLAlchemy Self Referential Relationship

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

 

 

Write a Reply or Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.