5

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;
});
Mouneer
  • 12,827
  • 2
  • 35
  • 45
Mark Robson
  • 1,298
  • 3
  • 15
  • 40

1 Answers1

16

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

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.

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

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;
      }
    });
  });
});
Jan Aagaard Meier
  • 28,078
  • 8
  • 95
  • 66
  • Just remember that you have to include [underscore](https://npmjs.org/package/underscore) module – josemando Nov 27 '13 at 19:03
  • 6
    _ is already used in sequelize, and exposed via `Sequelize.Utils._` – Jan Aagaard Meier Nov 28 '13 at 08:28
  • 2
    Hi Jan I was blocked on the same issue for many hours. I tried using Promise.all without any luck. I'm new to node.js and all the async stuff so likely I've been confused but I was wondering if you had a working example using Promise.all ? thanks In any case your answer works and helped me a lot. – Etienne Sep 08 '15 at 04:30
  • 5
    @Etienne I've added an example using promises – Jan Aagaard Meier Sep 08 '15 at 08:12
  • @Jan. " Any reason why you are not using the SQL driver directly?" Can you please explain SQL driver. I am connecting postres and express using sequelize and only executing raw queries / raw multiple queries –  Jul 25 '20 at 11:37