说明：
工作中遇到一种表结构，每条记录中的一个字段，又是该表内其他记录的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.