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.

Maybe setting cascade can help https://github.com/sequelize/sequelize/issues/8444 — sujeet, Jul 16 '20 at 18:44

score 9 · Answer 1 · answered Dec 01 '16 at 13:26

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

PostgreSQL supports it with `DELETE` + `USING` extension: https://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join SQLite doesn't support it apparently without subqueries: https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite — Ciro Santilli OurBigBook.com, Apr 24 '23 at 22:31

Pelle Jacobs · Answer 2 · 2022-05-05T08:45:10.593

1

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

edited May 05 '22 at 08:45

answered Jul 16 '20 at 18:17

Pelle Jacobs

2,379
1
21
25

1

using Join in a RAW Query gives error : "Incorrect syntax near the keyword 'INNER". I used INNER JOIN As well as Join keyword. [sequelize version 6] – Daniyal Saleem Dec 20 '21 at 15:45
2

You can't use joins with Postgres. You should use the `USING` clause. You can do `DELETE User USING UserPhone WHERE UserPhone.userId = User.userId AND UserPhone.phoneNumber = 1234`. – TPoschel Apr 28 '22 at 18:24
Thanks for raising this. The snippet provided was specifically for SQL Server, I've clarified this in the answer. – Pelle Jacobs May 05 '22 at 08:46

score 0 · Answer 3 · answered Nov 20 '21 at 19:23

0

The best way to do it, as far as I know (just spent time looking) destroy wont remove the underlying relationships

const id = parentRecord.id;

return models.sequelize.transaction(function (t) {
  return models.Parent.destroy({ where: { id }}, {transaction: t})
  .then(async function(deleteCount) {
   return await models.Child.destroy({where: { parent.id: id }}, {transaction: t});
   })
  })
  .catch((e) => {
       console.log("Error", e);
       return Promise.reject(e);
   });

answered Nov 20 '21 at 19:23

Nick Licata

147
11

In most cases, you don't have the ID of the parentRecord in the forehand. Since Sequelize works asynchronously, getting the parent ID, then delete the parent entry and delete the child entry (parent ID as foreign key) becomes a real mess :( – Alex F. Jul 06 '22 at 20:34

sequelize destroy record with join

3 Answers3