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 次后执行回调函数.回调是一个传递给你的代码的函数，它应该处理返回结果，例如在 web 服务器的上下文中将用户返回到客户端.

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，你应该利用它的特性.尝试使用 JOINs/急切加载

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屋！