SQLAlchemy 多对多关系的附加字段中间表

Question

我有三个表：用户表（User）、社区表（Community）和社区成员表（community_members），这个社区成员表是用来表示用户和社区之间的多对多关系。

我使用Flask-SQLAlchemy来创建这些表：

community_members = db.Table('community_members',
                db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                db.Column('community_id', db.Integer, db.ForeignKey('community.id')),
                )


class Community(db.Model):
    __tablename__ = 'community'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    members = db.relationship(User, secondary=community_members,
                            backref=db.backref('community_members', lazy='dynamic'))

现在我想在社区成员表中添加一个额外的字段，像这样：

community_members = db.Table('community_members',
                db.Column('id', db.Integer, primary_key=True),
                db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                db.Column('community_id', db.Integer, db.ForeignKey('community.id')),
                db.Column('time_create', db.DateTime, nullable=False, default=func.now()),
                )

然后在Python的命令行中，我可以这样做：

创建一个社区：

> c = Community()
> c.name = 'c1'
> db.session.add(c)
> db.session.commit()

给社区添加成员：

> u1 = User.query.get(1)
> u2 = User.query.get(2)
> c.members.append(u1)
> c.members.append(u2)
> db.session.commit()

> c.members
[<User 1>, <User 2>]

好的，这样是可以的。

但是我现在该如何获取社区成员表中的创建时间（time_create）呢？

Answer 1

如果你只需要通过已知的用户ID（比如说用户ID是2）来查询 community_members 和 community 这两个表，在SQLAlchemy中，你可以这样做：

session.query(community_members.c.time_create, Community.name).filter(community_members.c.user_id==2)

这样就能得到你想要的结果。

Answer 2

你需要从使用简单的多对多关系，转变为使用一个叫做“关联对象”的东西。简单来说，就是把关联表变成一个有明确类映射的对象。接下来，你会定义与 User 和 Community 之间的一对多关系：

class Membership(db.Model):
    __tablename__ = 'community_members'

    id = db.Column('id', db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    community_id = db.Column(db.Integer, db.ForeignKey('community.id'))
    time_create = db.Column(db.DateTime, nullable=False, default=func.now())

    community = db.relationship(Community, backref="memberships")
    user = db.relationship(User, backref="memberships")
    

class Community(db.Model):
    __tablename__ = 'community'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, unique=True)

不过，你可能只偶尔关心创建时间；你想要恢复之前的关系！其实，你不想重复设置 relationship，因为这样的话，sqlalchemy 会认为你想要建立两个不同的关联，这样就会产生混淆！你可以通过添加一个 关联代理 来解决这个问题。

from sqlalchemy.ext.associationproxy import association_proxy

Community.members = association_proxy("memberships", "user")
User.communities = association_proxy("memberships", "community")