CODEWITHSUNDEEP
node.jssequelize.js
Yaron
Yaron

Reputation: 1965

Updating with calculated values in Sequelize

I want to run an update query on my model using the previous value in one of the fields.

This updates the model (row id=4) the 'seq' field to 5.

 Model.update({
        seq: 5
    },{
        where:{
            'id':4,

        }
    });

Now how do I update the model to the previous value stored in the 'seq' field + 5 ?

 Model.update({
            seq: 'seq' + 5
        },{
            where:{
                'id':4,

            }
        });

Upvotes: 15

Views: 28903

Answers (4)

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 382532

Here are some syntaxes that may be of interest, and slightly safer than using literal.

Given a table:

const Inverses = sequelize.define('Inverses',
  {
    myValue: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
    inverse: {
      type: DataTypes.INTEGER,
    },
    name: {
      type: DataTypes.STRING,
    },
  },
  { timestamps: false }
);
await Inverses.sync({ force: true })
async function reset() {
  await sequelize.truncate({ cascade: true })
  await Inverses.create({ myValue: 2, inverse: -2, name: 'two' });
  await Inverses.create({ myValue: 3, inverse: -3, name: 'three' });
  await Inverses.create({ myValue: 5, inverse: -5, name: 'five' });
}

Update to fixed myValue.

await Inverses.update(
  { inverse: 0, },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse: -2, name: 'two'   },
{ myValue: 3, inverse:  0, name: 'three' },
{ myValue: 5, inverse:  0, name: 'five'  },

Instead update to match another column:

await Inverses.update(
  { inverse: sequelize.col('myValue'), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse: -2, name: 'two'   },
{ myValue: 3, inverse:  3, name: 'three' },
{ myValue: 5, inverse:  5, name: 'five'  },

Update to match another column with modification:

await Inverses.update(
  { inverse: sequelize.fn('1 + ', sequelize.col('myValue')), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse: -2, name: 'two'   },
{ myValue: 3, inverse:  4, name: 'three' },
{ myValue: 5, inverse:  6, name: 'five'  },

A string function test.

await Inverses.update(
  { name: sequelize.fn('upper', sequelize.col('name')), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse: -2, name: 'two'   },
{ myValue: 3, inverse: -3, name: 'THREE' },
{ myValue: 5, inverse: -5, name: 'FIVE'  },

Operator without parenthesis, e.g. col1 + col2. Possible with the "where" hack mentioned at: Sequelize sum between two columns in model but that is so ugly I wonder if I should just use literal instead.

await Inverses.update(
  { inverse: sequelize.where(sequelize.col('myValue'), '*', sequelize.col('inverse')), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse:  -2, name: 'two'   },
{ myValue: 3, inverse:  -9, name: 'three' },
{ myValue: 5, inverse: -25, name: 'five'  },

Equivalent literal version. We have to manually quote for PostgreSQL because of the upper case V.

await Inverses.update(
  { inverse: sequelize.literal('"myValue" * "inverse"'), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse:  -2, name: 'two'   },
{ myValue: 3, inverse:  -9, name: 'three' },
{ myValue: 5, inverse: -25, name: 'five'  },

With a literal multiplier instead.

await Inverses.update(
  { inverse: sequelize.where(sequelize.col('myValue'), '*', -2), },
  { where: { myValue: { [Op.gt]: 2 } } },
);

Outcome:

{ myValue: 2, inverse:  -2, name: 'two'   },
{ myValue: 3, inverse:  -6, name: 'three' },
{ myValue: 5, inverse: -10, name: 'five'  },

Tested on package.json:

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.14.0",
    "sql-formatter": "4.0.2",
    "sqlite3": "5.0.2"
  }
}

on Ubuntu 22.04, PostgreSQL 14.5.

Runnable GitHub upstream with assertions.

Upvotes: 1

Łukasz Szewczak
Łukasz Szewczak

Reputation: 1881

You can use

Model.update(
  { seq: sequelize.literal('seq + 5') },
  { where: { id: model_id } }
);

Or you can use the increment method

Model.increment('seq', { by: 5, where: { id: 'model_id' }});

Upvotes: 43

ndotie
ndotie

Reputation: 2150

say you want to increment column aa by value 5 where gender is female

Users.increment('aa', { by: 5, where: { gender: 'female' } });

Upvotes: 7

Sunny Sultan
Sunny Sultan

Reputation: 1180

You can do this by using increment

 Model.increment(
        { seq: +5 },
        { where: { id: 4 } }
      );

and output:

UPDATE `Model` SET `seq`=`seq`+ 5 WHERE `id` = 4

Upvotes: 16

Related Questions