Node.js多个Sequelize原始sql查询子查询 [英] Node.js multiple Sequelize raw sql query sub queries

问题描述

标题听起来很复杂。我有一个用户表，每个用户可以有多个兴趣。这些兴趣通过查找表链接到用户。在PHP中，我查询了users表，然后为每个人做了查询以查找兴趣。我如何在Node.js / Sequelize中执行此操作？我怎样才能设定某种承诺呢？例如：

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:

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;

}）;

推荐答案

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

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.

如果您想真正返回用户及其兴趣，我会建议这样的事情：

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

在上面的代码中 _ 指的是实用程序库。在函数调用users.length次之后执行回调函数。回调是一个传递给你的代码的函数，应该处理返回结果，例如在网络服务器的上下文中将用户返回给客户端。

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.

另一条评论 - 如果您只是在进行原始SQL查询，Sequelize可能不是您的最佳选择。您没有直接使用SQL驱动程序的任何原因？如果你想使用sequelize，你应该利用它的功能。尝试使用加入/急切加载

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

更新使用承诺的示例

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

这篇关于Node.js多个Sequelize原始sql查询子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！