說明：
工作中遇到一種表結構，每筆資料中的一個欄位，又是該表內其他紀錄的key。搜了一下，發現已經有人寫文章介紹這種關聯關係，原文連結：Flask - SQLAlchemy Self Referential Relationship - CodeOmitted
翻譯如下(主要由谷歌translate翻譯，本人水平有限，敬請指正)：
標題：Flask - SQLAlchemy中指向自身的關聯關係
通常，多對多關係需要3張表來管理，以便完成從一邊到另一邊的對映。 在本教學中，我們將研究多對多關係中的自引用。
多對多 -- 指向自身的關聯關係
一個典型的範例，wordpress中的類別和父類別結構。例如，類別Java(id=1)和子類別別SpringFramework(id=2, parent_id=1)。
關聯(Association)
關聯表(association table)，透過主表中relationship的secondary引數，將關聯表和主表關聯起來【譯註：參見如下category_tree和Category的定義，可以看到在Category的relationship定義中，secondary引數指向了category_tree】。 關聯表不需要是物件模型，它應該由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'))
)
定義了category_tree之後，我們還需要定義上一層的relationship，以便sqlalchemy可以替我們處理外來鍵等細節【譯註：此處應該是指下述class Category中的categories=db.relationship】。 對於這種關係，我們定義了一個名為catogories的集合。 關係的第一個屬性是物件名，secondary指向關聯表。
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 ' {}'.format(self.id)
PrimaryJoin和SecondaryJoin
category_tree的parent_id和children_id指向的是同一個欄位，Sqlalchemy無法區分哪個category.id是parent_id，哪個category.id是children_id。因此，需要一種方法來加以區分。在這個場景中，我們使用primaryjoin和secondaryjoin。以下是基於我個人的理解，定義的primaryjoin和secondaryjoin的用法。
怎麼區別的
從category的角度來看，如果我們將category視為為它的子類別。 集合是children，primaryjoin將是關聯表中的第一列，secondaryjoin將成為第二列【譯註：見下述children=db.relationship的引數】。
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 ' {}'.format(self.id)
如果將category看成是其自身的parent。 該集合是parent，primaryjoin對映到關聯表中的第二列，secondaryjoin將成為第一列。 讓我們用第一個場景測試一下【譯註：應該是指將catetory看成是其自身的children的場景】。
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 ' {}'.format(self.id)
測試目標
Programming
+-- Java
+ SpringFramework
+ Hibernate
+-- Python
+ Flask
+ Django
測試新增資料
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
+-------------------------------
測試新增關係
programming.children_categories.append(java)
programming.children_categories.append(python)
db.session.commit()
programming.children_categories.all()
[ 2, 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()
譯註：原文還有第二種場景的測試(即將category視作其自身的parent)以及愛豆和粉絲的關係，略。
補充：How do 'primaryjoin' and 'secondaryjoin' work for many-to-many relationship in SQLAlchemy??stackoverflow.com
這個問題裡面有對primaryjoin和secondaryjoin的解釋：
大概是說primaryjoin是從左表到關聯表的關聯。secondaryjoin是從右表到關聯表的關聯。以下是原文：In a many to many relationship, the primaryjoin expression describes the join between the left table and the junction table, and the secondaryjoin describes the join between the junction table and the right table. In other words, the primaryjoin expression is saying, "find all rows in the followers table where follower_id is X", the secondaryjoin expression is saying "find all rows in the followers table where followed_id is X", and get those two together to find all users that follow user X, and all users that are followed by user X.
It depends on the direction you're querying from. When you ask for user.followers, it will find them by using the primaryjoin to query the followers table for all rows where followed_id == user.id, and retrieve the user other with other.id == follower_id. When you ask for user.followed, it uses the secondaryjoin to query the followers table for all rows where follower_id == user.id, and retrieve the user other with other.id == followed_id.
Because you're adding it to self.followed collection, telling SQLAlchemy that's someone self is following. If you were adding it to the self.followers collection, you'd be doing the inverse, telling SQLAlchemy that 'user' is a follower of self.