Skip to content

Many-to-Many Relationships

A many-to-many relationship means:

many A relate to many B

Example:

a PostPost can have many TagTags
a TagTag can apply to many PostPosts

Association table

post_tags = db.Table(
    "post_tags",
    db.Column("post_id", db.Integer, db.ForeignKey("posts.id"), primary_key=True),
    db.Column("tag_id", db.Integer, db.ForeignKey("tags.id"), primary_key=True),
)

post_tags = db.Table(
    "post_tags",
    db.Column("post_id", db.Integer, db.ForeignKey("posts.id"), primary_key=True),
    db.Column("tag_id", db.Integer, db.ForeignKey("tags.id"), primary_key=True),
)

Models

class Post(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
 
    tags = db.relationship("Tag", secondary=post_tags, back_populates="posts")
 
 
class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
 
    posts = db.relationship("Post", secondary=post_tags, back_populates="tags")

class Post(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
 
    tags = db.relationship("Tag", secondary=post_tags, back_populates="posts")
 
 
class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
 
    posts = db.relationship("Post", secondary=post_tags, back_populates="tags")

Using it

post = Post(title="Flask Tips")
tag = Tag(name="flask")
post.tags.append(tag)
 
db.session.add(post)
db.session.commit()

post = Post(title="Flask Tips")
tag = Tag(name="flask")
post.tags.append(tag)
 
db.session.add(post)
db.session.commit()

When you need an association model

If the relationship needs extra fields (e.g., created_atcreated_at, rolerole), use an association model instead of db.Tabledb.Table.

If this helped you, consider buying me a coffee ☕

Buy me a coffee