CODEWITHSUNDEEP
postgresqlsequelize.js
user081608
user081608

Reputation: 1113

Sequelize bulk update with inner join

I am currently working with Sequelize and can not figure out how to update bulk when im associating two tables together. I have the follow:

Tables:

members
   user_id
   channel_id
   all

activities
   user_id
   channel_id

I am trying to update members.all when the user_ids match, members.channel_id is 2 and activities.channel_id is not 2.

Here is working Postgresql:

UPDATE members AS m 
   SET    "all" = true 
   FROM   activities AS a 
   WHERE  m.user_id = a.user_id 
      AND m.channel_id = 2 
      AND a.current_channel != 2;

Is this possible to do is sequelize? How do include a.current_channel != 2 into my current update?

Member.update(
        { all: true },
        { where: { channel_id: channelId } },
)

When I try to add an include it does not work.

Upvotes: 3

Views: 5687

Answers (2)

Sanket Berde
Sanket Berde

Reputation: 6895

Generally, i use this hack

models.findAll({
 where: {
 // which models to update
 }
}).then(targets => {
 models.target.update({
 // your updates
},{
 where : {
  target_primary_key: targets.map(t => t.primary_key)
 }
})

})

Upvotes: 0

oniramarf
oniramarf

Reputation: 903

I think you can't do something like that using Sequelize update method. I would use the include option in a findAll method, but as far as I can see on the documentation, there is no include option for the update method.

You could use a raw query to use directly the query.

sequelize.query("UPDATE members AS m SET "all" = true FROM activities AS a WHERE m.user_id = a.user_id AND m.channel_id = 2 AND a.current_channel != 2").spread((results, metadata) => {
  // Results will be an empty array and metadata will contain the number of affected rows.
});

Upvotes: 3

Related Questions