使用Sequelize更新Postgres表中的一行。

huangapple go评论58阅读模式
英文:

Updating a row in a Postgres table using Sequelize

问题

我正在尝试使用以下Sequelize查询来更新给定表中的一行:

const handleEditProfile = async (req, res) => {
  let medicalconditions = req.body.medicalconditions;
  let allergies = req.body.allergies;
  let bloodtype = req.body.bloodtype;
  let weight = req.body.weight;
  let height = req.body.height;
  let userId = req.body.userid;

  Profile.bulkCreate(
    [{userId: userId, medicalconditions: medicalconditions, allergies: allergies, bloodtype: bloodtype, weight: weight, height: height}],
    {updateOnDuplicate: ['medicalconditions', 'allergies', 'bloodtype', 'weight', 'height']}
  )
  .then(data => {
    res.send(data);
  })
  .catch(err => {
    res.status(400).send(err);
  })
};

在Postgres DB中,我得到的是:

 id |      medicalconditions      |     allergies      | bloodtype | weight  |   height   |         createdAt          |         updatedAt          | userId 
----+-----------------------------+--------------------+-----------+---------+------------+----------------------------+----------------------------+--------
 74 | type 2 diabetes             |   penicillin       |     AB    | 114 lbs |            | 2023-02-13 14:56:21.789-06 | 2023-02-13 14:56:21.789-06 |     40
 90 | hyperthyroidism             | none               | AB        | 120 lbs | 5'4"       | 2023-02-17 18:08:44.503-06 | 2023-02-17 18:08:44.503-06 |     40

如您所见,我通过userId查找了"行"。我想要更新"updateOnDuplicate"中提到的列,其中userId = 40。然而,它并没有更新id = 74和userId = 40的行,而是创建了一个新的行,id = 90和userId = 40。如何在不提及主键id的情况下修复这个问题?

const User = sequelize.define('user', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  uid: {
    type: DataTypes.TEXT,
    allowNull: false,
    unique: true
  },
  firstName: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  lastName: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  DOB: {
    type: DataTypes.TEXT,
    allowNull: false
  }
});

const Profile = sequelize.define('profile', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  medicalconditions: {
    type: DataTypes.TEXT
  },
  allergies: {
    type: DataTypes.TEXT
  },
  bloodtype: {
    type: DataTypes.TEXT
  },
  weight: {
    type: DataTypes.TEXT
  },
  height: {
    type: DataTypes.TEXT
  }
});

User.hasOne(Profile);
Profile.belongsTo(User);
英文:

I am trying to update a row in a given table using the following Sequelize query:

const handleEditProfile = async (req, res) => {
  let medicalconditions = req.body.medicalconditions;
  let allergies = req.body.allergies;
  let bloodtype = req.body.bloodtype;
  let weight = req.body.weight;
  let height = req.body.height;
  let userId = req.body.userid;

  Profile.bulkCreate(
    [{userId: userId, medicalconditions: medicalconditions, allergies: allergies, bloodtype: bloodtype, weight: weight, height: height}],
    {updateOnDuplicate: ['medicalconditions', 'allergies', 'bloodtype', 'weight', 'height']}
  )
  .then(data => {
    res.send(data);
  })
  .catch(err => {
    res.status(400).send(err);
  })
};

This is what I get in the Postgres DB:

 id |      medicalconditions      |     allergies      | bloodtype | weight  |   height   |         createdAt          |         updatedAt          | userId 
----+-----------------------------+--------------------+-----------+---------+------------+----------------------------+----------------------------+--------
 74 | type 2 diabetes             |   penicillin       |     AB    | 114 lbs |            | 2023-02-13 14:56:21.789-06 | 2023-02-13 14:56:21.789-06 |     40
 90 | hyperthyroidism             | none               | AB        | 120 lbs | 5'4"       | 2023-02-17 18:08:44.503-06 | 2023-02-17 18:08:44.503-06 |     40

As you can see, I looked up the "row" by the userId. I want to update the columns mentioned in "updateOnDuplicate" where userId = 40. However, instead of updating the row with the id = 74 and userId = 40, it creates a new row with the id = 90 and userId = 40. How can I fix this without mentioning the primary id?

const User = sequelize.define('user', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  uid: {
    type: DataTypes.TEXT,
    allowNull: false,
    unique: true
  },
  firstName: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  lastName: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  DOB: {
    type: DataTypes.TEXT,
    allowNull: false
  }
});

const Profile = sequelize.define('profile', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  medicalconditions: {
    type: DataTypes.TEXT
  },
  allergies: {
    type: DataTypes.TEXT
  },
  bloodtype: {
    type: DataTypes.TEXT
  },
  weight: {
    type: DataTypes.TEXT
  },
  height: {
    type: DataTypes.TEXT
  }
});

User.hasOne(Profile);
Profile.belongsTo(User);

答案1

得分: 1

如果您希望Profile记录不仅通过主键(PK)而且通过userId也是唯一的,您需要在Profile表上创建一个userId的唯一索引/约束。这样,如果在要插入的对象中不指定主键,PostgreSQL将查看字段值,以确定是否已经存在具有这些值的记录。另一个好处是,通过在userid上创建唯一索引,即使使用单独的create调用,也无法创建具有相同userId的两条记录。

英文:

If you want Profile records to be unique not only by PK but by userId as well you need an unique index/constraint in Profile table on userId. That way if you don't indicate PK in objects that about to be inserted PostgreSQL will look at fields values that correspond to any unique constraint to decide whether a record with such values already exists or doesn't.
Another benefit of having the unique index on userid that you can't create two records with the same userId even if you use an individual create calls.

huangapple
  • 本文由 发表于 2023年2月18日 13:04:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/75491314.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定