具有三个相互关联的表的多对多(SqlAlchemy) [英] Many-to-Many with three tables relating with each other (SqlAlchemy)

问题描述

我有三个表User，Device和Role.我已经像这样创建了一个多对多的b/w用户和设备关系；

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)

这很好运作.我可以将设备 d1 分配给用户 u1 > d1.users.append(u1)，并将用户分配给设备> u1.devices.append(d1)和 db.session.commit().

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

我想要的是扩展表 user_devices ，并在表中再添加一列作为 role_id ，这将成为Role表的ForeignKey.因此，此表 user_devices 将清楚地描述特定 Device 上特定 User 的 Role .在表 user_devices 中添加一列 role_id 后，我将 Role 表描述为；

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")

以这种方式，如何为设备 d1 上的用户 u1 分配角色 r1 ?这是我尝试过的:

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)

这将在表 user_devices

有没有办法像这样将两个属性添加到表中?

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

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

以便仅在commit()之后创建一行?

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

推荐答案

3个实体的关联不再是简单的多对多关系.您需要的是关联对象模式.为了使关联的处理更加容易，将其映射为模型类而不是简单的 Table :

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")

要将用户与设备和角色相关联，请创建一个新的 UserDevice 对象:

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

请注意，ORM关系不再是 Device 等的简单集合，而是 UserDevice 对象.这是一件好事:例如，当您遍历 user.user_devices 时，您不仅可以获得有关设备的信息，还可以了解用户在该设备上所扮演的角色.如果确实希望在不需要角色信息的情况下也提供更简单的集合，则可以使用

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.

这篇关于具有三个相互关联的表的多对多(SqlAlchemy)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！