How to add items into two tables in the same express function (mysql)

Question

I'm creating a cart for a virtual store, and when pressing "submit" button I need to insert the order into the "order" table and the items into the "orderitems" table.

I've been using express to insert things in only one table each function, but now I need to put both in the same... I'm trying to do it with promisses.

This is my html:

<table class="table">
            <thead>
                <tr scope="row">
                    <th scope="col">Product name</th>
                    <th scope="col">Price</th>
                    <th scope="col">Quantity</th>
                </tr>
            </thead>
            <tbody>
                <tr v-for="product in cart" v-bind:key="product.id" scope="row">
                    <td scope="col">{{product.name}}</td>
                    <td scope="col">{{product.price | currency}}</td>
                    <td scope="col"><input type="text" class="input-group-text" v-model="product.quantity"></td>
                </tr>
                <tr>
                    <td colspan="3">Frete: {{fare | currency}}</td>
                </tr>
                <tr>
                    <td colspan="3" class="highlighted">Total: {{total | currency}}</td>
                </tr>
            </tbody>
        </table>
        <button class="btn btn-info" @click="registerTransaction()">Submit cart</button>

this is the function I call:

registerTransaction(){
            const date = new Date()
            const dateTime = {
                day: date.getUTCDate(),
                month: date.getUTCMonth(),
                year: date.getUTCFullYear(),
                hour: date.getUTCHours() - 3,
                minute: date.getUTCMinutes(),
                sec: date.getUTCSeconds()
            }
            const transaction = {
                date: new Date(`${dateTime.year}-${dateTime.month}-${dateTime.day}T${dateTime.hour}:${dateTime.minute}:${dateTime.sec}Z`)
            }
            transaction.userid = 1
            transaction.fare = parseFloat(this.fare)
            transaction.total = parseFloat(this.total)
            transaction.cart = this.cart


            axios.post(`${baseApiUrl}/checkout`, transaction)
                .then(() => this.$toasted.global.defaultSuccess())
                .catch(showError)
        }

and this is my backend for axios:

const save = (req, res) =>{
        const transaction = {...req.body}

        try{
            existsOrError(transaction.date, "Error: transaction date is invalid")
            existsOrError(transaction.userid, "Error in the user authentication")
            //existsOrError(transaction.fare, "Error in calculating the fare")
            existsOrError(transaction.total, "Error in the total value")
        }
        catch(msg){
            return res.status(400).send(msg)
        }

        app.db('orders')
            .insert(transaction)
            .then(console.log('feito!'))
            .then(saveProducts(transaction))
            .catch(err => res.status(500).send(err))



    }

    const saveProducts = (transaction) => {
        const orderId = app.db(`orders`).select(`idOrder`) .where({idClient: transaction.userid}).last()
        console.log('Done')
        for(let i = 0; i < transaction.cart.length; i++){
            quantity = app.db('products').select('quantity').where({id: transaction.cart[i].id}).first()
            quantity -= transaction.cart[i].quantity
            app.db('products').update(quantity).where({id: transaction.cart[i].id})
            const orderedProduct = {idOrder: parseInt(orderId), idProduct: parseInt(transaction.cart[i].id), quantity: parseInt(transaction.cart[i].quantity)}
            app.db('orderedproducts').insert(orderedProduct).then(console.log('produto inserido')).catch(err => res.status(500).send(err))
        }
        return
    }

I expected it to save first my order, call the saveProducts function to save the products in it, and return, but it's returning a error as if it's not allowed to do...

Answer 1

I went through the documentation and found the folllowing, which basically means that query methods has a promise interface meaning we can attach a then and catch method. It also means that we can use async/await on it.

knex.select('*')
  .from('users')
  .where({name: 'Tim'})
  .then(function(rows) {
    return knex.insert({user_id: rows[0].id, name: 'Test'}, 'id').into('accounts');
  })
  .then(function(id) {
    console.log('Inserted Account ' + id);
  })
  .catch(function(error) { console.error(error); });

For your code I have added async/await, if you like you can use then method.

const save = (req, res) => {
  const transaction = { ...req.body };

  try {
    existsOrError(transaction.date, "Error: transaction date is invalid");
    existsOrError(transaction.userid, "Error in the user authentication");
    //existsOrError(transaction.fare, "Error in calculating the fare")
    existsOrError(transaction.total, "Error in the total value");
  } catch (msg) {
    return res.status(400).send(msg);
  }

  try {
    let orderId = await app
      .db("orders")
      .insert(transaction);
    console.log('order inserted :' + orderId);

    await saveProducts(transaction);
    console.log("saved all the products");
  } catch(err) {
    console.log(err);
  }
};

const saveProducts = async transaction => {
  const orderId = await app
    .db(`orders`)
    .select(`idOrder`)
    .where({ idClient: transaction.userid })
    .last();
  console.log("orderId : " + orderId);
  let quantity;
  for (let i = 0; i < transaction.cart.length; i++) {
    quantity = await app
      .db("products")
      .select("quantity")
      .where({ id: transaction.cart[i].id })
      .first();
    quantity -= transaction.cart[i].quantity;
    await app
      .db("products")
      .update(quantity)
      .where({ id: transaction.cart[i].id });
    const orderedProduct = {
      idOrder: parseInt(orderId),
      idProduct: parseInt(transaction.cart[i].id),
      quantity: parseInt(transaction.cart[i].quantity)
    };
    let insertedId = await app
      .db("orderedproducts")
      .insert(orderedProduct)
      .catch(err => res.status(500).send(err));
    console.log("inserted ordered product");
  }
  return;
};

NOTE: I have not executed the code so I can't guarantee that it will work the way you want it to be, but I am sure this will give you an idea how to move forward.