get the last insert id in node.js and pass it to a second query

Question

I have in my sql database 2 tables, a table called club and a table called players, they are connected by one to many relationships, the query in node.js works fine but i can not get the last insert of table club , i need to it for insert in the foreign key in the table players

here what i have tried in node.js:



       module.exports={
 create:(data,callback)=>{
  var myArray = new Array();
 /* for(let item of data.players) {
    console.log(item.firstname);
}*/

data.players.forEach((player) => {
  console.log(player.id);
  console.log(player);
  var playerModel ={
    id : player.id,
    firstname : player.firstname,
    lastname : player.lastname,
    position : player.position,
    price : player.price,
    appearences : player.appearences,
    goals : player.goals,
    assists : player.assists,
    cleansheets : player.cleansheets,
    redcards : player.redcards,
    yellowcards : player.yellowcards,
    image : player.image,
    clubid : player.clubid,
  };
  console.log("model"+playerModel.position);
  myArray.push(playerModel);
});
var id;
 
 pool.query(
      'insert into club(userid,name,price) values(?,?,?)',
   [
        data.userid,
        data.name,
        data.price
      ],
    
      (error,result) => {
        if(error){
          callback(error);
         
        }
     /*   id = result.insertId;
        console.error(result);
        console.log(result+"  result");*/
        
        console.log(result.insertId);
        return callback(null,result.insertId);
      },

      
    );



    for(var item of myArray){
    pool.query(
      
      'insert into players(id,firstname,lastname,position,price,appearences,goals,assists,cleansheets,redcards,yellowcards,image,clubid) values (?,?,?,?,?,?,?,?,?,?,?,?,?)',
       [ 
       item.id,
       item.firstname,
       item.lastname,
       item.position,
       item.price,
       item.appearences,
       item.goals,
       item.assists,
       item.cleansheets,
       item.redcards,
       item.yellowcards,
       item.image,
       (
        'select top 1 id from club order by id desc'
        )
       ],
      
 
     (error,results,fields)=>{
       if(error){
         callback(error);
       }
       return callback(null,results);
     },
    );
    }
  },
     

no idea about how to do this

Answer 1

If I understand this correctly, a subquery should work here.

-- first insert the club from paraterized query
insert into club (clubid, name, price) 
values (? , ? , ?);


-- then use a subquery to find the last inserted club id
insert into
   players (id, firstname, lastname, position, price, appearences, goals, assists, cleansheets, redcards, yellowcards, image, clubid) 
values
   (
       ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 
      (
         select clubid 
         from club 
         order by clubid desc
         limit 1
      )
   )
;

Apart from that, an insert statement doesn't give you any data back. If you are looking to get the ID from the first call in NodeJS, you need to run a batch statement. 1 insert and 1 select, in the same batch of statements that is sent to the SQL server. See more for multi statement config here. node-mysql multiple statements in one query

const pool = mysql.createConnection({multipleStatements: true});

pool.query(`
  insert into club(userid,name,price) values(?,?,?);
  select clubid from club order by clubid desc limit 1;
  `
  , params
  , function(err, results) {
  if (err) throw err;
    // now the id will be the second item of the batch statement result
    const myId = results[1]
});
)

Based on both things, you could combine them, actually.

pool.query(`
  insert into club(userid,name,price) values(?,?,?);
  insert into players (id, firstname, lastname, position, price, appearences, goals, assists, cleansheets, redcards, yellowcards, image, clubid) 
  values
   (
       ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 
      (
         select clubid 
         from club 
         order by clubid desc
         limit 1
      )
   );
  `
  , params
)

You could also create yourself a stored procedure for this purpose.

Answer 2

Create a multi-query batch, like

INSERT INTO table1 (column1, column2, column3) VALUES (?, ?, ?);
INSERT INTO table2 (column1, reference_column, column3) VALUES (?, LAST_INSERT_ID(), ?);

Execute using a method which:

1. supports multi-query batch execution
2. executes the batch as a transaction

The parameters are provided to this method as one solid data array (for shown code it will contain 5 values, first 3 will be inserted into 1st query and all another toi 2nd one). id value assigned by 1st query will be automatically retrieved by the function and inserted into 2nd query.

---

I don't know what is the method with described properties in Node.JS. But it must exist..

Answer 3

An alternate way to figure this out was placing the initial query holding the first INSERT statement into a variable:

const first_insert = db.query('INSERT')...

Then returning the variable at the end of the first promise, and then finding the property values of the variable (since it returns a javascript object afterall):

.then((first_insert) => {
   console.log(Object.entries(first_insert)); //returns array
   console.log(first_insert[0]); 
/*returns contents of first index of previously returned array, you'll see an object with a insertId property, thats what you need*/
   console.log(first_insert[0].insertId) 
/*this is the value you need to pass to each following statement*/
/*store that value in a variable that you declarre before any of the db methods, I named mine pk*/
   pk = first_insert[0].insertId
/*Now use that variable for the foreign key and to correspond with the placeholder in whatever queries you use*/
}

Answer 4

How to use this in NodeJS, oracle ? My Error is Error: ORA-00933: SQL command not properly ended.

-- first insert the club from paraterized query

INSERT INTO club (clubid, name, price) 
VALUES (? , ? , ?);

-- then use a subquery to find the last inserted club id

INSERT INTO
   players (id, firstname, lastname, position, price, appearences, goals, assists, cleansheets, redcards, yellowcards, image, clubid) 
VALUES
   (
       ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 
      (
         SELECT clubid 
         FROM club 
         ORDER BY clubid desc
         LIMIT 1
      )
   )
;