Basically i got a createorder that needs to add the order into the database , and all the products in the order into an Orderline extra table.
Been trying different ways to get the second query to work but the only thing that gets inserted into the db is the data from the first query.
the second query also needs to use the id from the inserted order of the first query. since the database uses autoincrement for the id of the order.
CreateOrder: (req) => {
return new Promise((resolve, reject) => {
const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';
db.query(sqlInsert, [
req.body.Created,
req.body.firstName,
req.body.lastName,
req.body.street,
req.body.number,
req.body.postalCode,
req.body.city,
req.body.telephone,
req.body.email,
req.body.totalPrice],
(err, result, fields) => {
if (err) {
reject(err);
}
else {
resolve(result);
}
});
db.query(sqlInsert2,
[
result.insertId,
req.body.producten.id,
req.body.producten.cartQuantity,
req.body.producten.price], (err, result, fields) => {
if (err) {
reject(err);
} else {
resolve(result);
}
});
});
},
}
You'll need two promises: one for each query. And the second one should be created only when the first has resolved.
A nice way to do this, is to promisify db.query into a new function, and only use that function from then on. So define this first:
const promiseQuery = (sql, params) => new Promise((resolve, reject) =>
db.query(sql, params, (err, result, fields) => err ? reject(err) : resolve(result))
);
Secondly, you can make your CreateOrder an async function so that you can use await:
async CreateOrder: (req) => {
const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';
let result = await promiseQuery(sqlInsert, [
req.body.Created,
req.body.firstName,
req.body.lastName,
req.body.street,
req.body.number,
req.body.postalCode,
req.body.city,
req.body.telephone,
req.body.email,
req.body.totalPrice
]);
await promiseQuery(sqlInsert2, [
result.insertId,
req.body.producten.id,
req.body.producten.cartQuantity,
req.body.producten.price
]);
}
Try this:
CreateOrder: (req) => {
const { Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice } = req.body
let results = {}
return new Promise((resolve, reject) => {
const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';
db.query(sqlInsert, [Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice],
(err, result, fields) => {
console.log(result)
results.insertId = result.id
});
db.query(sqlInsert2, [ results.insertId, req.body.producten.id, req.body.producten.cartQuantity, req.body.producten.price], (err, result, fields) => {
if (err) {
reject(err);
} else {
resolve(result);
}
});
});
},
}
I'm guessing in your initial code you're resolving the promise in the first db.query which results in ending the promise.
the above code is correct same flow works here
async function CreateOrder(req) {
const sqlInsert = 'INSERT INTO legs (leg) VALUES (?);';
const sqlInsert2 = 'INSERT INTO destinations (destination,area) VALUES (?,?);';
let result = await promiseQuery(sqlInsert, [
"lw"
]);
let res= await promiseQuery(sqlInsert2, [
"test",
"test location"
]);
console.log(res)
}
const promiseQuery = (sql, params) => new Promise((resolve, reject) =>
db.query(sql, params, (err, result, fields) => err ? reject(err) : resolve(result))
);
async function test(){
await CreateOrder()
}
try checking if any of the key is undefined or there is a mismatch in the field type and the value you are passing
if you req.body.producten is an array you have to do the query for each iteration
async CreateOrder: (req) => {
const sqlInsert = 'INSERT INTO web3.orders (Created, firstName, lastName, street, number, postalCode, city, telephone, email, totalPrice) VALUES (?,?,?,?,?,?,?,?,?,?);';
const sqlInsert2 = 'INSERT INTO web3.orderline (orderId,productId,qty,price) VALUES (?,?,?,?);';
let result = await promiseQuery(sqlInsert, [
req.body.Created,
req.body.firstName,
req.body.lastName,
req.body.street,
req.body.number,
req.body.postalCode,
req.body.city,
req.body.telephone,
req.body.email,
req.body.totalPrice
]);
for (let ele of req.body.producten){
await promiseQuery(sqlInsert2, [
result.insertId,
ele.id,
ele.cartQuantity,
ele.price
]);
}
}