promise-mysql return promise

Question

I'm about to blow a fuse with this problem: I treat to translate an PHP application to Angular 6, however I'd develop this problem in JavaSript ES6, for simplification before the final and definitive code. The situation is as follows:

My users' table is:

    +-------------------------+-----------------------+------+-----+---------+----------------+
    | Field                   | Type                  | Null | Key | Default | Extra          |
    +-------------------------+-----------------------+------+-----+---------+----------------+
    | id                      | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | ip_address              | varchar(45)           | NO   |     | NULL    |                |
    | username                | varchar(100)          | NO   |     | NULL    |                |
    | password                | varchar(80)           | NO   |     | NULL    |                |
    | salt                    | varchar(40)           | YES  |     | NULL    |                |
    | email                   | varchar(254)          | NO   | UNI | NULL    |                |
    | activation_code         | varchar(40)           | YES  |     | NULL    |                |
    | forgotten_password_code | varchar(40)           | YES  |     | NULL    |                |
    | forgotten_password_time | int(11) unsigned      | YES  |     | NULL    |                |
    | remember_code           | varchar(40)           | YES  |     | NULL    |                |
    | created_on              | int(11) unsigned      | NO   |     | NULL    |                |
    | last_login              | int(11) unsigned      | YES  |     | NULL    |                |
    | active                  | tinyint(1) unsigned   | YES  |     | NULL    |                |
    | first_name              | varchar(50)           | YES  |     | NULL    |                |
    | last_name               | varchar(50)           | YES  |     | NULL    |                |
    | company                 | varchar(100)          | YES  |     | NULL    |                |
    | phone                   | varchar(20)           | YES  |     | NULL    |                |
    +-------------------------+-----------------------+------+-----+---------+----------------+

My groups table is:

    +-------------+-----------------------+------+-----+---------+----------------+
    | Field       | Type                  | Null | Key | Default | Extra          |
    +-------------+-----------------------+------+-----+---------+----------------+
    | id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | name        | varchar(20)           | NO   |     | NULL    |                |
    | description | varchar(100)          | NO   |     | NULL    |                |
    +-------------+-----------------------+------+-----+---------+----------------+

I have besides a relation like:

    +----------+-----------------------+------+-----+---------+----------------+
    | Field    | Type                  | Null | Key | Default | Extra          |
    +----------+-----------------------+------+-----+---------+----------------+
    | id       | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | user_id  | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    | group_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    +----------+-----------------------+------+-----+---------+----------------+

Well, in this situation I want get all groups where a due user (in this example user 2) belongs.

My JavaScript code is:

var mysql = require('promise-mysql');

var connection;
var arr = [];

mysql.createConnection({
    host: 'host',
    user: 'user',
    password: 'password',
    database: 'database'
}).then(
    function (conn) {
        connection = conn;
        return conn.query('select * from users_groups where user_id=2');
    }
).then(
    function(value) {
        console.log('Initial value : ' + JSON.stringify(value) );


        for (let user_group of value) {
            console.log('Each value : ' + JSON.stringify(user_group.group_id));
            arr.push( connection.query(`select * from groups where id = ${user_group.group_id}`));
        }     
        return arr;
    }, function (reason) {
        console.log('Rechazo: ' + reason);    
    }
).then (
    function (echo) {
        console.log('Echo: ' + JSON.stringify(echo));
        connection.end();
    }
);

And I did get:

Initial value : [{"id":10,"user_id":2,"group_id":1},{"id":11,"user_id":2,"group_id":2}]
Each value : 1
Each value : 2
Echo: [{"isFulfilled":false,"isRejected":false},{"isFulfilled":false,"isRejected":false}]

Can anyone help me please? Thanks in advance!

Answer 1

In order for promise chaining to work properly, you have to return promises in your then calls for any asynchronous actions. In your 2nd then call, you're pushing a series of asynchronous db calls into an array. Since you return an array instead of a promise, your next then call does not wait for all of your queries to finish. Pass this array to Promise.all and return that instead and you'll achieve your desired results.

var mysql = require('promise-mysql');

var connection;
var arr = [];

mysql.createConnection({
    host: 'host',
    user: 'user',
    password: 'password',
    database: 'database'
}).then(
    function (conn) {
        connection = conn;
        return conn.query('select * from users_groups where user_id=2');
    }
).then(
    function(value) {
        console.log('Initial value : ' + JSON.stringify(value) );


        for (let user_group of value) {
            console.log('Each value : ' + JSON.stringify(user_group.group_id));
            arr.push( connection.query(`select * from groups where id = ${user_group.group_id}`));
        }     
        return Promise.all(arr);
    }, function (reason) {
        console.log('Rechazo: ' + reason);    
    }
).then (
    function (echo) {
        console.log('Echo: ' + JSON.stringify(echo));
        connection.end();
    }
);