How to save both element and the associated elements in join table with express.js and react.js-CodePudding

I made a similar post to this asking how to delete the elements, but now I run into a similar, yet separate problem with creation.

I have a project where I have two main tables: Contacts and Workers

I also have a join table called WorkerContacts

In my project, I give the user the option of creating contacts, something that would also require creating elements of the join table. Multiple workers could be associated with a single contact and, as such, I create at least 1 workerContact for every contact made. My original concern was that with my current setup (seen below), if I run into an error where I successfully create a contact, but then fail to create the associated join tables (resulting from an error), that would throw off everything. I received help for that problem, but now my issue comes from the fact that to create a new element of my join table, I need to first get the id of the new customer.

Here's my current situation:

First is my controller function for creation:

export const create = (req, res) => {
    const customer = new Customer(
        null, req.body.customer.company, req.body.customer.contact_name, req.body.customer.email, req.body.customer.number, req.body.customer.title, req.body.customer.old_address, req.body.customer.new_address, req.body.customer.category,  
        req.body.customer.broker_name, req.body.customer.broker_company, req.body.customer.broker_number, req.body.customer.broker_email, req.body.customer.architect_name, req.body.customer.architect_company, req.body.customer.architect_number, req.body.customer.architect_email,  
        req.body.customer.consultant_name, req.body.customer.consultant_company, req.body.customer.consultant_number, req.body.customer.consultant_email, ""
        )
    Customer.companyValidator(customer.company)
    .then(([found_customer_element]) => {
        if (found_customer_element.length !== 0){
            res.status(406).json({message: "Company already has an associated customer"});
        }
        
        else{
            (customer.customerValidator() && req.body.workers.length !== 0) ? 
            customer.save(req.body.workers)
            .then((result) => {
                Customer.findByID(result[0].insertId)
                .then(([new_customer]) =>{
                    res.json({customer: new_customer, workers: req.body.workers})
                })
                .catch(err => res.status(500).json({message: "We had some trouble saving on our end. Please try to reload page and try again"}))
                
            })
            .catch(err => res.status(500).json({message: "We had some trouble saving on our end. Please try to reload page and try again"}))
            :
            res.status(406).json({message: "Must have company name, customer name, workers, and category filled"});
        }
    })
    .catch(err => res.status(500).json({message: "Something went wrong on our end, please try to reload page and try again"}) )

Ignoring all of the validator functions I have, because you have to actually create the element for the first time, we don't have a defined value for id.

async save(worker_list){
        // The purpose of this function is to save a new element to the database.
        try{
            await db.execute(`INSERT INTO customers (company, contact_name, contact_email, contact_phone_number, contact_title, old_address, new_address, category, broker_name, broker_company,
                broker_number, broker_email, architect_name, architect_company, architect_number, architect_email, consultant_name, consultant_company, consultant_number, consultant_email, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, 
                [this.company, this.contact_name, this.contact_email, this.contact_phone_number, this.contact_title, this.old_address, this.new_address, this.category, this.broker_name, this.broker_company, this.broker_number, this.broker_email,
                this.architect_name, this.architect_company, this.architect_number, this.architect_email, this.consultant_name, this.consultant_company, this.consultant_number, this.consultant_email, this.notes]);
            for (const worker of worker_list){
                console.log(this.id)
                await db.execute(`INSERT INTO workercustomers (customer_id, worker_id) VALUES(?, ?)`, [this.id, worker.value])
            }
            await db.execute("COMMIT");
        }
        catch (err){
            await db.execute("ROLLBACK")
        }
    }

Here is the function where I insert into the database. The problem is that since I don't have a this.id value, I can't initialize both in one go, then rollback if anything happens. How would I make it so that I can fix my problem?

I'm using a MySQL database if that helps.

Hopefully this is enough information, but if you require more, I can definitely provide you with it.

CodePudding user response：

The INSERT query should return an object containing an insertId property:

async save(worker_list) {
  // The purpose of this function is to save a new element to the database.
  try {
    await db.execute('START TRANSACTION');
    const newCustomer = await db.execute(
      `INSERT INTO customers (company, contact_name, contact_email, contact_phone_number, contact_title, old_address, new_address, category, broker_name, broker_company,
            broker_number, broker_email, architect_name, architect_company, architect_number, architect_email, consultant_name, consultant_company, consultant_number, consultant_email, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        this.company,
        this.contact_name,
        this.contact_email,
        this.contact_phone_number,
        this.contact_title,
        this.old_address,
        this.new_address,
        this.category,
        this.broker_name,
        this.broker_company,
        this.broker_number,
        this.broker_email,
        this.architect_name,
        this.architect_company,
        this.architect_number,
        this.architect_email,
        this.consultant_name,
        this.consultant_company,
        this.consultant_number,
        this.consultant_email,
        this.notes,
      ]
    );
    if (!newCustomer) {
        // Failed to insert element 
        await db.execute('ROLLBACK');
        return;
    }
    for (const worker of worker_list) {
      await db.execute(
        `INSERT INTO workercustomers (customer_id, worker_id) VALUES(?, ?)`,
        [newCustomer.insertId, worker.value]
      );
    }
    await db.execute('COMMIT');
  } catch (err) {
    await db.execute('ROLLBACK');
  }
}

Also, make sure that you execute START TRANSACTION, otherwise your operations will not be atomic.