带有附加字段的 SQLAlchemy ManyToMany 辅助表 [英] SQLAlchemy ManyToMany secondary table with additional fields

问题描述

我有 3 个表:用户、社区、社区成员(用于用户和社区的关系 many2many).

I have 3 tables: User, Community, community_members (for relationship many2many of users and community).

我使用 Flask-SQLAlchemy 创建这个表:

I create this tables using 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 添加其他字段，如下所示:

Now I want add additional field to community_members like this:

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 shell 中我可以做到这一点:

And now in python shell I can do this:

创建社区:

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

好的，这有效.

但是现在我如何获得 community_members 表的 time_create ?

But how now I can get time_create of community_members table?

推荐答案

您将不得不从使用简单的多对多关系切换到使用关联对象"，这基本上只是获取关联表和给它一个适当的类映射.然后，您将定义与 User 和 Community 的一对多关系:

You will have to switch from using a plain, many-to-many relationship to using an "Association Object", which is basically just taking the association table and giving it a proper class mapping. You'll then define one-to-many relationships to User and 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 会认为你以某种方式想要两个关联；这一定意味着不同的东西！您可以通过添加关联代理来完成此操作.

But you may only occasionally be interested in the create time; you want the old relationship back! well, you don't want to set up the relationship twice; because sqlalchemy will think that you somehow want two associations; which must mean something different! You can do this by adding in an association proxy.

from sqlalchemy.ext.associationproxy import association_proxy

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

这篇关于带有附加字段的 SQLAlchemy ManyToMany 辅助表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！