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

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

• javascript
• node.js
• express

• 1 Send 2 queries as one batch query, and use LAST_INSERT_ID() MySQL function in 2nd query. Execute the batch as single transaction. – Akina Commented Apr 28, 2021 at 9:09
• 1 understood nothing literally , how to do this in pratics? – Fares Ben Slama Commented Apr 28, 2021 at 9:11
• actually this is a node.js problem , you can see that i retrieved the last inserted id in a value id = results.insertId, but outside the first query it will be undefined , How to place that variable id without getting undefined in the second query??? i mean to pass this value to the second insert – Fares Ben Slama Commented Apr 30, 2021 at 10:38

4 Answers 4

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 bine 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.

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..

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*/
}

How to use this in NodeJS, oracle ? My Error is Error: ORA-00933: SQL mand 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
      )
   )
;