Asynchrony in a database query

I don't believe you are using the async.map function correctly. Unfortunately you are using it so incorrectly that I cannot determine what it is you are actually trying to do. But here is an explanation of how you are misusing, vs the proper use of the async.map function.

Example from the async documentation:

async.map(['file1','file2','file3'], fs.stat, function(err, results){
    // results is now an array of stats for each file
});

Let's Simplify this a bit.

async.map(someArray, someFunction, someCallBack);

I believe your problem lies in the someFunction argument. Here is your version of this:

function(item, nextParent) {
    var sql = 'SELECT `mail_id`, `subject`, `preview`, `owner_user_id`, `viewer_user_id`, `viewer_is_new`, `time_stamp` FROM `phpfox_mail` WHERE (`viewer_user_id` = ' + item.viewer_user_id + ' OR `owner_user_id` = ' + item.viewer_user_id + ') AND (`viewer_user_id` = ' + item.owner_user_id + ' OR `owner_user_id` = ' + item.owner_user_id + ') ORDER BY `mail_id` DESC LIMIT 1';
    var dialogs = [];
    connection.query(sql, function(err, rows) {
        // ???
    });
}

The function for this argument, if we consult the fs.Stat example, should take two arguments, this first is an item from the array we sent into async.map. This part I believe you have right. HOwever, the second argument is a callback, with two arguments. An error, and a set of results. This is the part I think you are messing up. You should call this callback. Internally async.map provides the callback argument, this callback argument is how async.map collects results into the array. Without the call to this, async.map cannot collect the results.

I believe what you want is this:

function(item, someCallBackArgument) {
    var sql = 'SELECT `mail_id`, `subject`, `preview`, `owner_user_id`, `viewer_user_id`, `viewer_is_new`, `time_stamp` FROM `phpfox_mail` WHERE (`viewer_user_id` = ' + item.viewer_user_id + ' OR `owner_user_id` = ' + item.viewer_user_id + ') AND (`viewer_user_id` = ' + item.owner_user_id + ' OR `owner_user_id` = ' + item.owner_user_id + ') ORDER BY `mail_id` DESC LIMIT 1';
    var dialogs = [];
    connection.query(sql, function(err, rows) {
        someCallBackArgument(err, rows);
    });
}

I'm assuming this is a question about how to use async.map: I've rewritten your code so it uses callbacks properly: You will need to adjust the output but this should return you an array with all the data you require.

connection.query(sql, function(err, rows) {
      if (err) throw err;

      async.map(rows, function(item, callback) {
          var sql = 'SELECT `mail_id`, `subject`, `preview`, `owner_user_id`, `viewer_user_id`, `viewer_is_new`, `time_stamp` FROM `phpfox_mail` WHERE (`viewer_user_id` = ' + item.viewer_user_id + ' OR `owner_user_id` = ' + item.viewer_user_id + ') AND (`viewer_user_id` = ' + item.owner_user_id + ' OR `owner_user_id` = ' + item.owner_user_id + ') ORDER BY `mail_id` DESC LIMIT 1';
          var dialogs = [];
          connection.query(sql, function(err, rows) {
            if(err) return callback(err);
            callback(null, { item: item, rows: rows} );
          });

      }, function(err, item) {
          // Here I have to get the generated array with all the necessary dialogue.
          console.log(JSON.stringify(item));

          IM.socket.emit('logger', {text: 'dataIsABuilding', key: 'success'});
          IM.socket.emit('dialogsBuilding', item);
      });
    });

Yes! :)

IM = {
    iUserId: false,
    socket: false,
    checkDialog: function(socket) {
        this.socket = socket;
        var sql = 'SELECT DISTINCT(`owner_user_id`), `viewer_user_id` FROM `phpfox_mail` WHERE `owner_user_id` = ' + this.iUserId + ' GROUP BY 1,2 UNION SELECT DISTINCT (`viewer_user_id`), `owner_user_id` FROM `phpfox_mail` WHERE `viewer_user_id` = ' + this.iUserId + ' GROUP BY 1,2 ORDER BY 1 ';
        connection.query(sql, function(err, rows) {
          if (err) throw err;

          async.map(rows, function(item, nextParent) {
              var sql = 'SELECT `mail_id`, `subject`, `preview`, `owner_user_id`, `viewer_user_id`, `viewer_is_new`, `time_stamp` FROM `phpfox_mail` WHERE (`viewer_user_id` = ' + item.viewer_user_id + ' OR `owner_user_id` = ' + item.viewer_user_id + ') AND (`viewer_user_id` = ' + item.owner_user_id + ' OR `owner_user_id` = ' + item.owner_user_id + ') ORDER BY `mail_id` DESC LIMIT 1;';
              var dialogs = [];
            connection.query(sql, function(err, rows) {
              if(err) return nextParent(err);
              nextParent(null, rows[0]);
            });

          }, function(err, item) {             
             async.map(item, function(dialog, next) {
                 connection.query('SELECT `user_name`, `full_name`, `user_profile_image` FROM `phpfox_user` WHERE `user_id` = ' + dialog.owner_user_id, function(err, user) {
                     next(err, {owner: user[0], dialog: dialog});
                 });
             }, function(err, rows) {                 
                 async.map(rows, function(dialog, next) {
                     connection.query('SELECT `user_name`, `full_name`, `user_profile_image` FROM `phpfox_user` WHERE `user_id` = ' + dialog['dialog']['viewer_user_id'], function(err, user) {
                         next(err, {viewer: user[0], dialog: dialog['dialog'], owner: dialog['owner']});
                     });
                 }, function(err, dialogs) {
                     console.log(dialogs);
                     IM.socket.emit('logger', {text: 'dataIsABuilding', key: 'success'});
                     IM.socket.emit('dialogsBuilding', dialogs);
                 });                 
             });
          });
        });
    }

And it worked! :)

But it seems to me, this code - it sucks!