Node.js 多个 Sequelize 原始 sql 查询子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18640627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Node.js multiple Sequelize raw sql query sub queries
提问by Mark Robson
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:
标题听起来很复杂。我有一个用户表，每个用户可以有多种兴趣。这些兴趣通过查找表链接到用户。在 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;
});
});
回答by Jan Aagaard Meier
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 的异步特性。你代码中的 return 语句会在第一次调用 sequelize.query 之后直接执行，也就是在查询返回之前。这意味着用户将是未定义的。
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.
在上面的代码_中指的是一个实用程序库。在函数被调用 users.length 次后执行回调函数。回调函数是传递给您的一段代码的函数，它应该处理返回结果，例如在 Web 服务器的上下文中将用户返回给客户端。
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/预先加载一次性加载用户和兴趣
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;
}
});
});
});
