CODEWITHSUNDEEP
mysqlsqldatabasesequelize.js
lellefood
lellefood

Reputation: 2096

Sequelize same N:M table for different associations

In a Node + MySQL project I got 4 Tables:

Now I got the association of the N:M like this:

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

I need to use the same ProjectMembers table as N:M for InvitedUsers and Projects

Is this possible? I suppose that it's not since the foreign key constrains on the N:M table cannot choose on which table (Users or InvitedUsers) has to be applied

I've tried adding (omitting the options):

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

And no error is displayed during the associations definition but when i try to add an entry in the ProjectMembers table coping a InvitedUsers's id as FK, I got a foreign key constrain error.

So my question is if I can use a N:M table for different N:M relations.

Upvotes: 4

Views: 664

Answers (3)

Kshateesh
Kshateesh

Reputation: 609

Did you try creating different alias using as option in belongsToMany So, your associations might look like this - 

//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' });

Upvotes: 0

AbhinavD
AbhinavD

Reputation: 7282

yes, this can be done as long as you specify which foreignKey the association should use for joining the tables. You can specify two different keys, one for user and one for the InvitedUser and Sequelize will pick the right key. 

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

This is how the schema of project_members would look like

Schema for n:m relation

Below is the full working code

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

      })
    })
  })

Upvotes: 1

Rick James
Rick James

Reputation: 142218

Speaking from MySQL's perspective...

A many:many database table is essentially 2 columns (project_id, person_id) with two indexes ((project_id, person_id) and (person_id, project_id)). If is reasonable to add a 3rd column to qualify the type of relationship between the ("member" versus "invited user").

More discussion of the table: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Upvotes: 4

Related Questions