为不同的关联设置相同的n:m表

t9eec4r0 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(538)

在node+mysql项目中，我得到了4个表： Users 描述注册用户， InvitedUsers 原始用户，包含一些非注册用户的信息， Projects 描述一个项目， ProjectMembers n:m联想 Projects 以及 Users 现在我得到了n:m的联想式：

Users.belongsToMany(Projects, { through: ProjectMembers });
Projects.belongsToMany(Users, { through: ProjectMembers });

我需要用同样的 ProjectMembers 表中n:m表示 InvitedUsers 以及 Projects 这可能吗？我想这不是因为n:m表上的外键约束不能选择哪个表( Users 或者 InvitedUsers )必须应用
我尝试添加（省略选项）：

InvitedUsers.belongsToMany(Projects, { through: ProjectMembers });
Projects.belongsToMany(InvitedUsers, { through: ProjectMembers });

在关联定义期间没有显示错误，但是当我尝试在 ProjectMembers 台面a InvitedUsers 的id为fk，出现外键约束错误。
所以我的问题是，我是否可以使用一个n:m表来处理不同的n:m关系。

sql mysql Database sequelize.js

来源：https://stackoverflow.com/questions/50764005/sequelize-same-nm-table-for-different-associations

3条答案

按热度按时间

fumotvh31#

您是否尝试使用创建不同的别名 as 中的选项 belongsToMany 所以，你的联想可能是这样的-

//User-projects via ProjectMembers
Users.belongsToMany(Projects, { through: ProjectMembers, as: 'ProjectMembers' });
Projects.belongsToMany(Users, { through: ProjectMembers, as: 'ProjectMembers' });

//InvitedUsers-Projects via ProjectMembers
InvitedUsers.belongsToMany(Projects, { through: ProjectMembers, as: 'ProjectInvitedMembers' });
Projects.belongsToMany(InvitedUsers, { through: ProjectMembers, as: 'ProjectInvitedMembers' });

赞(0）回复(0）举报 2021-06-24

m528fe3b2#

是的，只要您指定 foreignKey 关联应用于联接表。可以指定两个不同的键，一个用于 user 一个是给 InvitedUser sequelize会选择正确的键。

User.belongsToMany(Project, { through: ProjectMembers, foreignKey: 'user_id' });
Project.belongsToMany(User, { through: ProjectMembers, foreignKey: 'project_id' });
Invited.belongsToMany(Project, { through: ProjectMembers, foreignKey: 'invited_id' });
Project.belongsToMany(Invited, { through: ProjectMembers, foreignKey: 'project_id' });

这就是 project_members 看起来像

下面是完整的工作代码

const User = sequelize.define('user', {
  username: Sequelize.STRING,
});

const Invited = sequelize.define('invited', {
  username: Sequelize.STRING,
});

const Project = sequelize.define('project', {
  name: Sequelize.STRING,
});

const ProjectMembers = sequelize.define('project_members', {
  id: {
    allowNull: false,
    autoIncrement: true,
    primaryKey: true,
    type: Sequelize.INTEGER,
  },
  user_id: {
    type: Sequelize.INTEGER,
  },
  project_id: Sequelize.INTEGER,
  invited_id: Sequelize.INTEGER,
});

User.belongsToMany(Project, { through: ProjectMembers, foreignKey: 'user_id' });
Project.belongsToMany(User, { through: ProjectMembers, foreignKey: 'project_id' });
Invited.belongsToMany(Project, { through: ProjectMembers, foreignKey: 'invited_id' });
Project.belongsToMany(Invited, { through: ProjectMembers, foreignKey: 'project_id' });

sequelize.sync({ force: true })
  .then(() => {
    User.create({
      username: 'UserOne',
      projects: {
        projectName: 'projectOne'
      }
    }, { include: [Project] }).then((result) => {
      Invited.create({
        username: 'InvitedOne',
        projects: {
          projectName: 'projectTwo'
        }
      }, { include: [Project] }).then((result2) => {
        console.log(result2);

      })
    })
  })

赞(0）回复(0）举报 2021-06-24

gdrx4gfi3#

从mysql的Angular 来说。。。
一many:many database 表本质上是两列( project_id , person_id )有两个索引( (project_id, person_id) 以及 (person_id, project_id) ). 如果合理的话，可以添加第3列来限定“成员”与“受邀用户”之间的关系类型。
更多关于table的讨论：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

赞(0）回复(0）举报 2021-06-24

我来回答

为不同的关联设置相同的n:m表

3条答案

相关问题

热门标签

最新问答