sequelize destroy record with join

Question

In my model there is a Users table, and a UserPhones table. User.id is a foreign key in UserPhones.

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
      userid       : {
        type        : DataTypes.UUID,
        primaryKey  : true,
      },
      username : DataTypes.STRING,
    },
    {
      classMethods: {
        associate: (models) => {       
          models.User.hasOne(models.UserPhone, {foreignKey: 'userId'});
        }
      }
    });

  return User;
};


module.exports = (sequelize, DataTypes) => {
  const UserPhone = sequelize.define('UserPhone', {
      id              : {
        type        : DataTypes.UUID,
        primaryKey  : true,
      },
      userId   : {
        type      : DataTypes.UUID,
        references: {
          model     : require('.').User,
          key       : 'userid',
          deferrable: sequelize.Deferrable.INITIALLY_IMMEDIATE
        }
      },
      phoneNumber     : {
        type: DataTypes.STRING
      }
    },
    {
      classMethods: {
        associate: (models) => {
          models.UserPhone.belongsTo(models.User, {foreignKey: 'userId'});
        }
      }
    }
  );
  return UserPhone;
};

Destroying a phoneNumber is easy:

UserPhone.destroy({where: {phoneNumber: '123456789'}};

I would like to delete all users that have a specific phone number with sequelize. Even better, delete all users that have one of an array of phone numbers.

Answer 1

I do not think it is possible to do a DELETE and a JOIN query at the same time.

Therefore,

I would like to delete all users that have a specific phone number with sequelize.

UserPhone.findAll({attributes: ['userId'], where: {phoneNumber: '1234'}}
.then(function (userIds) {
    if (userIds.length === 0) 
       return Promise.resolve(true) //nothing to delete
    return User.destroy({where: {id: {$in: userIds}}});
})

Delete all users that have one of an array of phone numbers.

var array = ['123', '456'];
UserPhone.findAll({attributes: ['userId'], where: {phone: { $in: array }}}
.then(function (userIds) {
    if (userIds.length === 0) 
       return Promise.resolve(true) //nothing to delete
    return User.destroy({where: {id: {$in: userIds}}});
})

Answer 2

Sequelize does not currently support an include option on the destroy method: https://sequelize.org/master/class/lib/model.js~Model.html#static-method-destroy

If you want to avoid multiple sql statements, you will have to write a raw query: https://sequelize.org/master/manual/raw-queries.html

Eg. for a SQL Server solution, the following would work:

await sequelize.query(`
    DELETE User
    FROM User
    INNER JOIN UserPhone ON UserPhone.userId = User.userId
    WHERE UserPhone.phoneNumber = :phoneNumber
`, { replacements: { phoneNumber: '1234'} })