說明：
工作中遇到一種表結構，每條記錄中的一個字段，又是該表内其他記錄的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.