SQLAlchemy ManyToMany带有附加字段的辅助表 [英] SQLAlchemy ManyToMany secondary table with additional fields
问题描述
我有3个表:User,Community,community_members(用于许多用户和社区的关系).
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'))
现在,我想像这样向社区成员添加其他字段:
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屋!