Many-to-Many with three tables relating with each other (SqlAlchemy)

Question

I've three tables User, Device and Role. I have created a many-to-many relation b/w User and Device like this;

#Many-to-Many relation between User and Devices
userDevices = db.Table("user_devices",
                       db.Column("id", db.Integer, primary_key=True),
                       db.Column("user_id", db.Integer, db.ForeignKey("user.id")),
                       db.Column("device_id", db.Integer, db.ForeignKey("device.id"))))

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(60), index=True, unique=True)
    devices = db.relationship("Device", secondary=userDevices, backref=db.backref('users'), lazy="dynamic")

class Device(db.Model):
    __tablename__ = 'device'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), unique=True)

This works quiet well. I can assign a device d1 to user u1 > d1.users.append(u1), and user to device > u1.devices.append(d1) and db.session.commit().

What I want more is to extend the table user_devices with one more column as role_id which will be ForeignKey for Role table. So that this table user_devices will clearly describe a Role for specific User on specific Device. after adding a column role_id in table user_devices I described Role table as;

class Role(db.Model):
    __tablename__ = 'role'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), unique=True)
    device = db.relationship("Device", secondary=userDevices, backref=db.backref('roles'), lazy="dynamic")

In this way, how can I assign a Role r1 to User u1 on Device d1 ? here is what I tried:

# First get the device, user and role 
deviceRow = db.session.query(Device).filter(Device.name=="d1").first()
userRow = db.session.query(User).filter(User.username=="u1").first()
roleRow = db.session.query(Role).filter(Role.name == "r1").first()
# Then add the user on that device
deviceRow.users.append(userRow)
deviceRow.roles.append(roleRow)

This creates two rows in the table user_devices

Is there any way that we could add two attributes into the table like this ?;

deviceRow.users.append(userRow).roles.append(roleRow)

so that it creates only one row after commit() ?

Answer 1

An association of 3 entities is no more a simple many to many relationship. What you need is the association object pattern. In order to make handling the association a bit easier map it as a model class instead of a simple Table:

class UserDevice(db.Model):
    __tablename__ = "user_devices"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)
    device_id = db.Column(db.Integer, db.ForeignKey("device.id"), nullable=False)
    role_id = db.Column(db.Integer, db.ForeignKey("role.id"), nullable=False)

    __table_args__ = (db.UniqueConstraint(user_id, device_id, role_id),)

    user = db.relationship("User", back_populates="user_devices")
    device = db.relationship("Device")
    role = db.relationship("Role", back_populates="user_devices")

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(60), index=True, unique=True)
    user_devices = db.relationship("UserDevice", back_populates="user")

class Role(db.Model):
    __tablename__ = "role"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), unique=True)
    user_devices = db.relationship("UserDevice", back_populates="role")

To associate a user with a device and a role create a new UserDevice object:

device = db.session.query(Device).filter(Device.name == "d1").first()
user = db.session.query(User).filter(User.username == "u1").first()
role = db.session.query(Role).filter(Role.name == "r1").first()
assoc = UserDevice(user=user, device=device, role=role)
db.session.add(assoc)
db.session.commit()

Note that the ORM relationships are no longer simple collections of Device etc., but UserDevice objects. This is a good thing: when you iterate over user.user_devices for example, you get information on both the device and the role the user has on it. If you do wish to provide the simpler collections as well for situations where you for example don't need the role information, you can use an associationproxy.

Answer 2

There is a way to have 3-way many-to-many that is not a composition of two many-to-many relationships. You need an association object because the syntax for using just a Table doesn't allow 3-way many-to-many (because secondary explicitly refers to a 2-way many-to-many).

Here is a minimum example of how to do that in general:

from sqlalchemy import ForeignKey, Column, Integer, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

# Helper classes to simplify the other classes:
#  1. Adds ch column
#  2. Defines how to print it
class Ch:
  ch = Column(String, nullable=False)
  def __str__(self):
    return self.ch

#  3. Automatically determines table name (for foreign key)
class AutoNamed:
  @declared_attr
  def __tablename__(cls):
    return cls.__name__

class ABC(AutoNamed, Base):
  a_id = Column(Integer, ForeignKey('A.a_id'), primary_key=True)
  b_id = Column(Integer, ForeignKey('B.b_id'), primary_key=True)
  c_id = Column(Integer, ForeignKey('C.c_id'), primary_key=True)
  a = relationship('A', back_populates='abcs')
  b = relationship('B', back_populates='abcs')
  c = relationship('C', back_populates='abcs')
  def __repr__(self):
    return f'{self.a} {self.b} {self.c}'

class A(Ch, AutoNamed, Base):
  a_id = Column(Integer, primary_key=True)
  abcs = relationship('ABC', back_populates='a')

class B(Ch, AutoNamed, Base):
  b_id = Column(Integer, primary_key=True)
  abcs = relationship('ABC', back_populates='b')

class C(Ch, AutoNamed, Base):
  c_id = Column(Integer, primary_key=True)
  abcs = relationship('ABC', back_populates='c')

Ok, now a little explanation:

• ABC is an association table that needs a single instance of each of the tables in the 3-way many-to-many.
• Each of A, B, C will have references to all ABC objects that involve them added automatically when you instantiate an ABC instance.
• There is a gotchya: when you use relationship.secondary, the property on the object is a list of the other type (in their case, parent.children is a list of Children objects). However, in the docs for "association objects", when translating this to Association objects, although they still name the property on the parent object children, it is actually a list of Association objects. Here, I make this explicit by calling the property abcs.

You can instantiate these like normal:

anA = A(ch='x')
anB = B(ch='y')
anC = C(ch='z')
anABC = ABC(a=anA, b=anB, c=anC)

sess.add(anABC)

As a sanity check, here's the SQL that gets generated from this for SQLite. Exactly what we expect.

CREATE TABLE "A" (
  ch VARCHAR NOT NULL, 
  a_id INTEGER NOT NULL, 
  PRIMARY KEY (a_id)
);

CREATE TABLE "B" (
  ch VARCHAR NOT NULL, 
  b_id INTEGER NOT NULL, 
  PRIMARY KEY (b_id)
);

CREATE TABLE "C" (
  ch VARCHAR NOT NULL, 
  c_id INTEGER NOT NULL, 
  PRIMARY KEY (c_id)
);

CREATE TABLE "ABC" (
  a_id INTEGER NOT NULL, 
  b_id INTEGER NOT NULL, 
  c_id INTEGER NOT NULL, 
  PRIMARY KEY (a_id, b_id, c_id), 
  FOREIGN KEY(a_id) REFERENCES "A" (a_id), 
  FOREIGN KEY(b_id) REFERENCES "B" (b_id), 
  FOREIGN KEY(c_id) REFERENCES "C" (c_id)
);