CODEWITHSUNDEEP
javascriptnode.jsasynchronousasync-awaitpromise
Juliette
Juliette

Reputation: 4429

promise logic with sql in nodejs when making calls to database

I have the code below that is reliant on two api calls to two different tables. It first gets data from database, gets an id from there, makes another call to another table, gets data from there and appends it to each value from the first call. The code works fine in theory but I just need to be able to resolve the final product by creating a chain of events. The final resolve should wait for the entire code to finish executing and then return the value of first_call.

Here is my code. Let me know how I can modify it.

Updated code

function getUsers() {
    return new Promise((resolve, reject) => {
      var sql = `SELECT p.customer, p.fname, p.lname, p.email, p.user, c.name AS organizationName, c.email AS organizationEmail c.did AS phoneNumber FROM people AS p LEFT JOIN customers AS c ON c.id = p.customer`;
      console.log(sql)
      con.query(sql, function (err, result) {
        if (err) throw err; 
        resolve(result); 
      });
    });
  }

Old code

function getUsers() {
    return new Promise((resolve, reject) => {
      var first_call = []
      var sql = `SELECT customer, fname, lname, email, user FROM people`;
      con.query(sql, function (err, result) {
        if (err) throw err; 
        first_call = result
      });

      for (let i = 0; i < first_call.length; i++) {
        var sql2 = `SELECT name, email, did FROM customers WHERE id = ${first_call[i].customer}`;
        con.query(sql2, function (err, result2) {
          first_call[i].organizationName = result2[0].name;
          first_call[i].organizationEmail = result2[0].email;
          first_call[i].phoneNumber = result2[0].did;
        });
      }

      resolve(first_call); 
    });
  }

Upvotes: 0

Views: 457

Answers (1)

Barmar
Barmar

Reputation: 780673

The simplest solution would be to combine the two queries with a JOIN:

SELECT p.customer, p.fname, p.lname, p.email, p.user, c.name AS organizationName, c.email AS organizationEmail, c.did AS phoneNumber
FROM people AS p
LEFT JOIN customers AS c ON c.id = p.customer

But if you really want two queries, here's how to rewrite your promise code using async and await.

You should also use a parameter in the SQL rather than substituting a variable.

async function getUsers() {
  var first_call = []
  var sql = `SELECT customer, fname, lname, email, user FROM people`;
  first_call = await con.query(sql)
  var sql2 = `SELECT name, email, did FROM customers WHERE id = ?`;
  for (let i = 0; i < first_call.length; i++) {
    let result2 = await con.query(sql2, [first_call[i].customer]);
    first_call[i].organizationName = result2[0].name;
    first_call[i].organizationEmail = result2[0].email;
    first_call[i].phoneNumber = result2[0].did;
  }

  return first_call;
}

Upvotes: 1

Related Questions