Alex Portnoy Alex Portnoy - 4 months ago 120
SQL Question

Sequelize delete multiple associations

Hi I have the following tables

models.User= sequelize.define("User", {
name: {
type: DataTypes.STRING, allowNull: false, unique: true
}
});
models.Group= sequelize.define("Group", {
name: {
type: DataTypes.STRING, allowNull: false, unique: true
}
});


db.User.belongsToMany(db.Group, {
"through": "UsersGroup"
});

db.Group.belongsToMany(db.User, {
"through": "UsersGroup"
});


Is there a way to delete at once many user from a group, given the group name , and the names (users) to delete?

like:

var groupName = 'Work';

var users = ['Alice','Bob']


So I need to delete the association between Work group and {Alice and Bob}

Answer

Using "through": "UsersGroup" will automatically create a new Model UsersGroup however it won't associate it with User and Group directly. So what i'd do is to create a new sequelize model UsersGroup with whatever attributes and keys and define the association to User and Group. Then instead of passing "UsersGroup" string to belongsToMany you use the model directly as db.UsersGroup.

models.UsersGroup = sequelize.define("UsersGroup", {});

db.UsersGroup.belongsTo(db.User);
db.UsersGroup.belongsTo(db.Group);
db.UsersGroup.findAll({attributes:['id'], include:[
  {model: db.User, where: {name: {$in:['Alice','Bob']}}},
  {mode: db.Group, where: {name: 'Work'}}
]}).then(function(toBeDeleted){
  return db.UsersGroups.destroy({where:{id:{$in:toBeDeleted.map(function(d){ return d.id})}}})
}).then(function(){
  ....
}).catch(function(dbErr){throw err;})

You also might want to check if toBeDeleted.length > 0 as i remember sequelize was acting funny when an empty array is passed.

Also in case you don't have id as primary key in usersgroup table you can easily modify the code above to use combination of user_id and group_id - Just modify the where statements.

The other approach could be selecting Users and Groups separately and use resulting sets in UsersGroups.destroy(). But i prefer to have an association even from a through model.

If you were to delete associations just based on group name OR user name you'd be able to do that using removeAssociation or removeAssociations have a looj here

Comments