How to implement many to many relationship using flask sqlalchemy?

Question

Hi I am new to flask sqlalchemy especially when it comes to relationship model, can someone help me what is the concept of many to many relationship model. My example use case is user can have multiple groups and groups can have multiple user. Am I correct the correct relationship with that will be "Many-to-Many" relationship model? Or other models based on my example use case? I already searched many about these but I don't fully understand the concept and the implementation. Thanks in advance :-)

score 3 · Accepted Answer · edited Oct 25 '21 at 12:27

Yes, that is considered many-to-many relationship in your scenario. For example, consider a database that has user and group. We can say that a user has many groups, and a group has many users. It's like overlapped one-to-many relationships from both side. 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 require an additional table that act as intermediary/auxiliary where primary keys of both tables is 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

Hi Kelvin, thanks for the help. And another question, I am correct that this is bidirectional relationship? Based in the documentation. But I don't fully understand the meaning of bidirectional relationship. Can you explain what it is? Thanks in advance. — Paul Exconde, Mar 31 '20 at 13:03
Yes your case is bidirectional relationship. Bidirectional it means, when you're accessing User Model you also have access to Group Model through db,relationship and on the other side when you access Group Model you have access to User Model. Both of this case is useful let say you want to query list of groups that user have or you want to query list of groups and then how many user inside each group — Kelvin, Apr 02 '20 at 01:49

How to implement many to many relationship using flask sqlalchemy?

1 Answers1