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() ?
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
.