node.js，express - 以同步方式在循环内一个接一个地执行mysql查询

In my node.js, express app, I am making an ajax call with the superagent middleware. The call fetches database data in a complex array using the node-mysql middleware through quite a few database queries.

在我的node.js，表达应用程序，我正在与superagent中间件进行ajax调用。该调用使用node-mysql中间件通过相当多的数据库查询来获取复杂数组中的数据库数据。

Before pasting the code, I am trying to explain in words what I am trying to do although the code would suffice to say what it wants to do with the addition that all the asynchronous things inside the first callback should be done in the synchronous way.

在粘贴代码之前，我试图用文字解释我想要做什么，尽管代码足以说明它想要做什么，并且第一次回调中的所有异步内容应该以同步方式完成。

Explanation:

说明：

Inside the callback of the first query , a for loop is executed to run the second query multiple times and after each loop, the next loop is to be called only after the callback of the second query is complete. Things are same for the next code lines as well.

在第一个查询的回调中，执行for循环以多次运行第二个查询，并且在每个循环之后，仅在第二个查询的回调完成后才调用下一个循环。下一个代码行的情况也是一样的。

Code:

码：

You can however skip the innards( marked in comments) of the for loops to make things brief and easy if you want.

但是，您可以跳过for循环的内部（在注释中标记），以便在需要时简化和简单。

conn.query("SELECT * FROM `super_cats`",function(error, results, fields) {   

        if(error){console.log("erro while fetching products for homepage "+ error);}

        for(var i in results) { // FIRST FOR LOOP INSIDE THE FIRST QUERY CALLBACK

            /*Innards of for loop starts*/    
            var elem = new Object();
            var supcat_id=results[i].id;
            elem.super_id =supcat_id;
            elem.cats=new Array();
            var cat= '';
            /*Innards of for loop ends*/ 

            conn.query("SELECT * FROM `categories` WHERE `supcat_id`="+supcat_id,function(error_cats, results_cats, fields_cats) {   

                if (error_cats) {console.log("erro while fetching cats for menu " + error_cats);}

                for(var j in results_cats) {

                    /*Innards of for loop starts*/    
                    cat= new Object();
                    var cat_id=results_cats[j].id;
                    cat.cat_id=cat_id;
                    cat.cat_name=results_cats[j].cat_name;
                    cat.subcats=new Array();    
                    /*Innards of for loop starts*/    

                    conn.query("SELECT * FROM `subcategories` WHERE `category`="+cat_id,function(error_subcats, results_subcats, fields_subcats) { 
                        if (error_subcats) {console.log("erro while fetching subcats for menu " + error_subcats);}   
                        for(var k in results_subcats ){

                            /*Innards of for loop starts*/    
                            var subcat=new Object();
                            var subcat_id=results_subcats[k].id;
                            subcat.subcat_id=subcat_id;
                            subcat.subcat_name=results_subcats[k].subcategory;
                            cat.subcats.push(subcat);
                            elem.cats.push(cat);    
                            /*Innards of for loop starts*/

                        }// end of for loop for  results_subcats   

                    });

                }// end of for loop for result_cats
            });   

            super_cats.push(elem);    
         }// end of for supercat results   

    res.send(super_cats)    
});

I tried with the async middleware but in vain as I just could not figure out which function to use in this case .

我尝试使用异步中间件，但徒劳无功，因为我无法弄清楚在这种情况下使用哪个函数。

To be brief, requirements are :

简而言之，要求是：

1) All the asynchronous things inside the first callback should be done in the synchronous way.

1）第一个回调中的所有异步事物都应该以同步方式完成。

2) the response should be sent to the ajax call only after all the calculations are done and not before that (as it would probably happen if things were asynchronous as they are in the existing code, wouldn't it ?)

2）只有在完成所有计算之后才应该将响应发送到ajax调用，而不是在之前（如果事情是异步的，因为它们在现有代码中可能会发生，不是吗？）

1 个解决方案

#1

It may be just semantics, but it's important to understand that you cannot run this in a synchronous way. You have to run it asynchronously, and manage the order of the processing to get the desired effect. I find it useful to think about these kinds of problems more in terms of how I want to transform the data (à la functional programming) rather than the imperative code I would write in a more synchronous environment.

它可能只是语义，但重要的是要理解你不能以同步的方式运行它。您必须异步运行它，并管理处理的顺序以获得所需的效果。我发现在我想要转换数据（例如函数式编程）方面而不是在更加同步的环境中编写的命令式代码时，更多地考虑这些问题是有用的。

From what I can tell by the code, you want to end up with a data structure in super_cats that looks something like this:

从代码中我可以看出，你想在super_cats中得到一个看起来像这样的数据结构：

[
  {
    super_id: 1,
    cats: [
      {
        cat_id: 2,
        cat_name: "Category",
        subcats: [
          {
            subcat_id: 3,
            subcat_name: "Subcategory"
          },
          ...
        ]
      },
      ...
    ]
  },
  ...
]

Let's start by extracting this into a single function call with a single callback.

让我们首先将它解压缩为一个带有单个回调的函数调用。

function getCategoryTree(callback) {
}

Now, then, let's take it from the top. You want to run a single asynchronous function (an SQL query), and you want to produce an array with one entry per result. That sounds like a map operation to me. However, since we want one of the values (cats) to be determined asynchronously, we need to use an asynchronous map, which the async library provides.

现在，让我们从顶部开始吧。您希望运行单个异步函数（SQL查询），并且希望生成一个数组，每个结果只有一个条目。这听起来像是对我的地图操作。但是，由于我们希望异步确定其中一个值（猫），我们需要使用异步库提供的异步映射。

Let's just fill in the async.map signature for now; we want to map over our results (this is the functional equivalent of our for loop), and for each one we want to turn the result into something—the asynchronous function that does the something is called the iterator. Finally, once we have all our transformed array elements, we want to call the callback given to our function.

我们现在就填写async.map签名;我们想要映射我们的结果（这是我们的for循环的功能等价物），并且对于每一个我们想要将结果转化为某种东西 - 执行某些东西的异步函数称为迭代器。最后，一旦我们拥有了所有变换后的数组元素，我们就想调用赋予函数的回调。

function getCategoryTree(callback) {
  conn.query("SELECT * FROM `super_cats`", function(error, results, fields) {
    async.map(results, iterator, callback);
  });
}

Let's create a new function for getting the top-level category information, and use its name in place of our iterator placeholder.

让我们创建一个新函数来获取*类别信息，并使用它的名称代替迭代器占位符。

function getCategoryTree(callback) {
  conn.query("SELECT * FROM `super_cats`", function(error, results, fields) {
    async.map(results, getSuperCategory, callback);
  });
}

function getSuperCategory(resultRow, callback) {
}

Now we need to decide what we want to give back for each resultRow. Based on our diagram above, we want an object with super_id equal to the row's ID, and cats equal to all the categories in the top-level category. However, since cats is also determined asynchronously, we need to run the next query and transform those results before we can move on.

现在我们需要决定我们想要为每个resultRow回馈什么。根据上面的图表，我们希望一个对象的super_id等于行的ID，而cat则等于*类别中的所有类别。但是，由于猫也是异步确定的，我们需要运行下一个查询并在继续之前转换这些结果。

Similar to last time, we want each item in our cats array to be an object with some information from the query's result, but we also want a subcats array, which is again determined asynchronously, so we'll use async.map again. This time, however, we'll use an anonymous function for the callback, since we want to do something with the results before we give them to the higher-level callback.

与上次类似，我们希望我们的cats数组中的每个项都是一个对象，其中包含查询结果中的一些信息，但是我们还需要一个子类数组，这个数组也是异步确定的，所以我们将再次使用async.map。但是，这一次，我们将使用匿名函数进行回调，因为我们希望在将结果提供给更高级别的回调之前对结果执行某些操作。

function getSuperCategory(resultItem, callback) {
  var supcat_id = resultItem.id;

  conn.query("SELECT * FROM `categories` WHERE supcat_id` = " + supcat_id, function(error, results, fields) {
    async.map(results, getCategory, function(err, categories) {
      callback(err, { super_id: supcat_id, cats: categories });
    });
  });
}

As you can see, once this async.map is done, it means we have all the categories under this this super-category; thus, we can call our callback with the object we want to be in the array.

正如您所看到的，一旦完成async.map，就意味着我们拥有此超类别下的所有类别;因此，我们可以使用我们想要在数组中的对象来调用我们的回调。

Now that that's done, we just need to implement getCategory. It will look very similar to getSuperCategory, because we want to do basically the same thing—for each result, return an object that has some data from the query, but also an asynchronous component.

既然已经完成了，我们只需要实现getCategory。它看起来与getSuperCategory非常相似，因为我们希望基本上做同样的事情 - 对于每个结果，返回一个包含查询中某些数据的对象，但也是一个异步组件。

function getCategory(resultItem, callback) {
  var cat_id = resultItem.id;
  var cat_name = resultItem.cat_name;

  conn.query("SELECT * FROM `subcategories` WHERE `category` = " + cat_id, function(error, results, fields) {
    async.map(results, getSubCategory, function(err, subcategories) {
      callback(err, { cat_id: cat_id, cat_name: cat_name, subcats: subcategories });
    });
  });
}

Now, we just need to implement getSubCategory.

现在，我们只需要实现getSubCategory。

function getSubCategory(resultItem, callback) {
  callback(null, {
    subcat_id: resultItem.id,
    subcat_name: resultItem.subcategory
  });
}

Oops! The data we need from getSubCategory doesn't have an asynchronous component! It turns out we didn't need that last async.map at all; we could have used a regular array map; let's change getCategory and getSubCategory to work that way.

哎呀！我们从getSubCategory需要的数据没有异步组件！事实证明我们根本不需要最后的async.map;我们可以使用常规阵列图;让我们改变getCategory和getSubCategory以这种方式工作。

function getCategory(resultItem, callback) {
  var cat_id = resultItem.id;
  var cat_name = resultItem.cat_name;

  conn.query("SELECT * FROM `subcategories` WHERE `category` = " + cat_id, function(error, results, fields) {
    var subcategories = results.map(getSubCategory);
    callback(error, { cat_id: cat_id, cat_name: cat_name, subcats: subcategories });
  });
}

function getSubCategory(resultItem) {
  return {
    subcat_id: resultItem.id,
    subcat_name: resultItem.subcategory
  };
}

It's worth noting that our original method worked fine; if there's a chance getSubCategory ever has an async component, you could just leave it as it was.

值得注意的是，我们的原始方法运行良好;如果有可能getSubCategory有一个异步组件，你可以保持原样。

And that's it! Here's the code that I wrote as I was writing this answer; note that I had to fake out the SQL a bit, but I think the idea is there:

就是这样！这是我写这个答案时写的代码;请注意，我不得不假装SQL，但我认为这个想法是存在的：

var async = require("async");

// fake out sql queries
queryNum = 0;
var conn = {
  query: function(query, callback) {
    queryNum++;
    var results = [1, 2, 3, 4, 5].map(function(elem) {
      return {
        id: queryNum + "-" + elem,
        cat_name: "catname-" + queryNum + "-" + elem,
        subcategory: "subcategory-" + queryNum + "-" + elem
      };
    });
    callback(null, results, null);
  }
};

function getCategoryTree(callback) {
  conn.query("SELECT * FROM `super_cats`", function(error, results, fields) {
    async.map(results, getSuperCategory, callback);
  });
}

function getSuperCategory(resultItem, callback) {
  var supcat_id = resultItem.id;

  conn.query("SELECT * FROM `categories` WHERE supcat_id` = " + supcat_id, function(error, results, fields) {
    async.map(results, getCategory, function(err, categories) {
      callback(err, { super_id: supcat_id, cats: categories });
    });
  });
}

function getCategory(resultItem, callback) {
  var cat_id = resultItem.id;
  var cat_name = resultItem.cat_name;

  conn.query("SELECT * FROM `subcategories` WHERE `category` = " + cat_id, function(error, results, fields) {
    var subcategories = results.map(getSubCategory);
    callback(error, { cat_id: cat_id, cat_name: cat_name, subcats: subcategories });
  });
}

function getSubCategory(resultItem) {
  return {
    subcat_id: resultItem.id,
    subcat_name: resultItem.subcategory
  };
}

getCategoryTree(function(err, result) {
  console.log(JSON.stringify(result, null, "  "));
});

---

There are some inefficiencies here, but for simplicity's sake I've glossed over them. For example, rather than running the second sub-query over and over, you could query at once for all the category IDs, then query all the categories at once, etc. Then, once you have all the data, you could loop over each array synchronously to pull out the pieces you need.

这里有一些效率低下的问题，但为了简单起见，我已经掩饰了它们。例如，不是一遍又一遍地运行第二个子查询，而是可以立即查询所有类别ID，然后一次查询所有类别，等等。然后，一旦获得所有数据，就可以遍历每个同步阵列以拉出你需要的部分。

In addition, there are better ways to store tree structures in relational databases; in particular, take a look at Modified Preorder Tree Traversal.

此外，还有更好的方法可以在关系数据库中存储树结构;特别是，看一下Modified Preorder Tree Traversal。