How to get inserted ids from mutlitple values in a transaction?

[carlosfer02]

I am tring to insert multiple values inside query statement of a transaction. I need to get the inserted ids for the next query chain, how can i retrieve them?

let results = await mysql.transaction()
            .query('INSERT INTO boxes (branch_id, number) VALUES (1,1), (1,2), (1,3)')
            .query(async (r) => {
                //Here i need to get the boxes ids...
            })
            .rollback(e => {})
            .commit();

[code-xhyun]

Is this what you want? @carlosfer02

const results  =  await  mysql . transaction( ) 
  . query ( 'INSERT INTO test (x) VALUES (?)' ,  [ 1 ] ) 
  . query ( ( r )  =>  [ 'UPDATE table SET x = 1 WHERE id =?' ,  r . insertId ] ) 
  . commit ( ) 

[pergunt]

@devShHong

Same problem. I am inserting a bunch of items. New ids of these items I need for another table. But I am getting only one object inserted instead of an array of objects.

[code-xhyun]

@pergunt sry dude, It's been a long time since I've used that library, so i can't say proper information.

[votemike]

Here we go....

import mysql from 'serverless-mysql';
import {raw} from "mysql";

const db = mysql({....});

const inserts1 = [['Foo'], ['Bar']];
const inserts2 = [[42, raw(`(SELECT id FROM names WHERE name=${db.escape('Foo')})`)],[69, raw(`(SELECT id FROM names WHERE name=${db.escape('Bar')})`)]];

try {
    const results = await db.transaction()
      .query('TRUNCATE prices')
      .query('ALTER TABLE prices DROP FOREIGN KEY FK_PricesNames')
      .query('TRUNCATE names')
      .query('INSERT INTO name (name) VALUES ?', [inserts1])
      .query('INSERT INTO prices (price, `name-id`) VALUES ?', [inserts2])
      .query('ALTER TABLE prices ADD CONSTRAINT `FK_PricesNames` FOREIGN KEY (`name-id`) REFERENCES `names` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION')
      .commit();
    await db.end();
    return results;
  } catch (error) {
    return {error};
  }