[SOLVED] How to implement many to many relationship using flask sqlalchemy?

How to implement many to many relationship using flask sqlalchemy?

I am new to flask-sqlalchemy especially when it comes to relationship models. What are the concepts of many-to-many relationship models? My example use case is a single user can have multiple groups and groups can have multiple users. Is the correct relationship "Many-to-Many"? If not, what type of relationship makes sense in this use case? I've already searched around but I don't fully understand the concept and the implementation. Thanks in advance :-)

Solution

Yes, that is considered a many-to-many relationship in your scenario. For example, consider a database that has users and groups. We can say that a user has many groups, and a group has many users. It's like an overlapped one-to-many relationship from both sides. This is actually non-trivial to represent in a relational database, and it cannot be done by simply adding foreign keys to the existing tables. To accommodate this, you need an additional table that acts as an intermediary/auxiliary where the primary keys of both tables are stored.

Here is an implementation using Flask-SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////tmp/test.db"
db = SQLAlchemy(app)

# act as auxiliary table for user and group table
user_groups = db.Table(
    "user_groups",
    db.Column("user_id", db.Integer, db.ForeignKey("user.id")),
    db.Column("group_id", db.Integer, db.ForeignKey("group.id")),
)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    groups = db.relationship("Group", secondary=user_groups, back_populates="users")


class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), nullable=False)
    users = db.relationship("User", secondary=user_groups, back_populates="groups")

For more reference: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html How to build many-to-many relations using SQLAlchemy: a good example