SQLAlchemy ManyToMany secondary table with additional fields
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)
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_proxyCommunity.members = association_proxy("memberships", "user")User.communities = association_proxy("memberships", "community")
If you only need query community_members
and community
table by a known user_id(such as user_id=2), In SQLAlchemy, you can perform:
session.query(community_members.c.time_create, Community.name).filter(community_members.c.user_id==2)
to get the result.