时间戳

首页 > 解决方案 > 调用数据库时在nodejs中使用sql的promise逻辑

javascript - 调用数据库时在nodejs中使用sql的promise逻辑

问题描述

我有下面的代码，它依赖于对两个不同表的两个 api 调用。它首先从数据库获取数据，从那里获取 id，再次调用另一个表，从那里获取数据并将其附加到第一次调用的每个值。该代码在理论上可以正常工作，但我只需要能够通过创建一系列事件来解决最终产品。最终解析应该等待整个代码执行完毕，然后返回 first_call 的值。

这是我的代码。让我知道如何修改它。

更新代码

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); 
      });
    });
  }

旧代码

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); 
    });
  }

标签： javascriptnode.jsasynchronousasync-awaitpromise

解决方案

最简单的解决方案是将两个查询与 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

但是如果你真的想要两个查询，这里是如何使用async和重写你的承诺代码await。

您还应该在 SQL 中使用参数而不是替换变量。

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;
}

推荐阅读