节点。js多序列的原始sql查询子查询。

The title sounds complicated. I have a users table, and each user can have multiple interests. These interests are linked to the user via a lookup table. In PHP I queried the users table, then for each one did a query to find interests. How can I do this in Node.js/Sequelize? How can I set up some sort of promises too? For example:

这个标题听起来复杂。我有一个users表，每个用户可以有多个兴趣。这些兴趣通过查找表链接到用户。在PHP中，我查询了users表，然后对每个用户进行查询以查找兴趣。我怎么能在Node.js/Sequelize中做这个?我怎样才能建立一些承诺呢?例如:

sequelize.query("SELECT * FROM users").success(function(users) {
    for (var u in users) {
       sequelize.query("SELECT interests.id, interests.title FROM interests, user_interests WHERE interests.id = user_interests.interest_id AND user_interests.user_id = " + users[u].id).success(function(interests) {
       if (interests.length > 0) {
         users[u].interests = interests;
       }
    });
 }
return users;

});

1 个解决方案

#1

From the return statement in the bottom of your code, it seems you have not totally grasped the asynchronous nature of node.js. The return statement in your code will be executed directly after the first call to sequelize.query, that is, before the query returns. This means that users will be undefined.

从代码底部的return语句中，似乎您还没有完全掌握node.js的异步特性。代码中的返回语句将在第一次调用sequelize之后直接执行。查询，即在查询返回之前。这意味着用户将没有定义。

If you wanted to actually "return" the users and their interest, I would suggest something like this:

如果你想要“回报”用户和他们的兴趣，我建议你这样做:

sequelize.query("SELECT * FROM users").success(function(users) {
    done = _.after(users.length, function () {
        callback(users)
    })

    for (var u in users) {
        sequelize.query("SELECT interests.id, interests.title FROM interests, user_interests WHERE interests.id = user_interests.interest_id AND user_interests.user_id = " + users[u].id).success(function(interests) {
            if (interests.length > 0) {
             users[u].interests = interests;
            }
            done();
        });
    }
});

In the code above _ refers to a utility lib. that executes the callback function after the function has been called users.length times. Callback is a function that is passed to your piece of code, and should process the return result, for example returning the users to the client in the context of a webserver.

在上面的代码中，_引用一个实用程序lib，它在函数被调用后执行回调函数。长时间。回调是传递给您的代码段的函数，并且应该处理返回结果，例如在webserver的上下文中将用户返回给客户端。

Another comment - if you are only doing raw SQL queries, Sequelize might not be the best choice for you. Any reason why you are not using the SQL driver directly? If you want to use sequelize, you should take advantage of its features. Try to the define a model for users and interests, set up an association and load up users and interests in one go using JOINs / eager loading

另一个注释——如果您只执行原始的SQL查询，那么Sequelize可能不是您的最佳选择。您为什么不直接使用SQL驱动程序?如果您想使用sequelize，您应该利用它的特性。尝试为用户和兴趣定义一个模型，建立一个关联并将用户和兴趣加载到一起使用join / eager加载。

update: An example using promises

更新:使用承诺的例子。

sequelize.query("SELECT * FROM users").then(function(users) {
  return sequelize.Promise.map(users, function (u) {
    return sequelize.query("SELECT interests.id, interests.title FROM interests, user_interests WHERE interests.id = user_interests.interest_id AND user_interests.user_id = " + users[u].id).then(function(interests) {
      if (interests.length > 0) {
        user.interests = interests;
      }
    });
  });
});

#1