0

So most questions about LAST_INSERT_ID() with multiple INSERT records involve getting multiple IDs back after the insert, my question is backwards from that.

I have table foo, with a many-to-many relationship with bar, and a junction table, foo__bar. I want to INSERT a single foo record, but also an unknown quantity of bar associations, so I need the LAST_INSERT_ID() after the first INSERT statement, but multiple times:

const db = require('mysql');
...
//connection and pooling set up

let sql = `INSERT INTO foo VALUES(null, ?, ?);
           INSERT INTO foo__bar((LAST_INSERT_ID(), ?), (LAST_INSERT_ID(), ?)//etc
          `;
let barIds = [1,4];

let params = [name, description, barIds[0], barIds[1]];

let result = await db.query(sql, params);

This works fine if the bar table is already populated and the bar ids are valid, and I only ever do two associations per INSERT: 

--table foo__bar

+------+------+
|foo_id|bar_id|
|------|------|
|     1|     1|
|------|------|
|     1|     4|
+------+------+

But what if I have multiple values/associations? Using the 'mysql' npm package, normally I could just do this and an array will be converted into ((val1, val2),(val1, val2),(val1,val1)) etc. However, for my junction table, I need val1 to be the same every time, specifically LAST_INSERT_ID(). Is this possible without having to make two async calls to the database?

Thanks

hamncheez
  • 709
  • 12
  • 22
  • So someone elsewhere mentioned the only way to do this is with two separate queries, using transactions to make sure there aren't any widow/orphans. Anyone have insights? – hamncheez Aug 01 '19 at 02:17
  • 1
    Yes, my understanding is also that you would need a transaction to do this. – Tim Biegeleisen Aug 01 '19 at 04:34

0 Answers0