英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论