代码之家 › 专栏 › 技术社区 › lellefood

为不同的关联继承相同的n:m表

sequelize.js database sql mysql

lellefood · 技术社区 · 6 年前

在node+mysql项目中,我得到了4个表:

Users 描述注册用户,
InvitedUsers 原始用户,包含一些非注册用户的信息,
Projects 描述一个项目,
ProjectMembers N:M协会 项目 和 用户

现在我得到了n:m的关联,就像这样:

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

我需要用同样的 项目成员 表为n:m 邀请用户 和 项目

这可能吗? 我想这并不是因为n:m表上的外键约束不能选择哪个表( 用户 或 邀请用户 )必须申请

我试过添加(省略选项):

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

并且在关联定义期间不会显示错误,但是当我尝试在 项目成员 桌面A 邀请用户 的ID为FK,我遇到外键约束错误。

所以我的问题是,如果我可以使用N:M表来处理不同的N:M关系。

3 回复 | 直到 6 年前

AbhinavD 6 年前

是的,只要你指定哪个,就可以做到这一点。 foreignKey 关联应用于联接表。您可以指定两个不同的密钥,一个用于 user 还有一个给 InvitedUser 续集将选择正确的关键。

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

      })
    })
  })

Rick James diyism 6 年前

从mysql的角度来说…

许多:许多 数据库 表本质上是两列( project_id , person_id )有两个索引( (project_id, person_id) 和 (person_id, project_id) )如果有理由添加第三列来限定(“成员”与“受邀用户”之间的关系类型)。

Kshateesh 6 年前

您是否尝试使用 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' });