在节点上执行异步多个MySQL查询

Question

我正在与mysql一起使用节点，这是我的问题。

我正在尝试在数据库上添加新照片，并将其返回为数组

这是我的功能：

function addNewPhotos(_id, files) {
var deferred = Q.defer();
var new_photos = []
_.each(files, function (one) {
    var data = [
        one.path,
        _id,
        0
    ]
    var sql = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)';
    db.connection.query(sql, data, function (err, result) {
        if (err)
            deferred.reject(err.name + ': ' + err.message);
        var sql = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?';
        if (result){
            db.connection.query(sql, [result.insertId], function(err, photo) {
                if (err) deferred.reject(err.name + ': ' + err.message);
                if (photo) {
                    new_photos.push(photo[0]);
                }
            });
        }
    })
})
deferred.resolve(Array.prototype.slice.call(new_photos));
return deferred.promise}

插入效果很好，但我无法检索结果将它们发送回客户端。（我的数组是空的）

谢谢。

Answer 1

始终在最低级别上进行尊重，在这种情况下， db.connection.query()。

if(!db.connection.queryAsync) {
    db.connection.queryAsync = function(sql, data) {
        return Q.Promise(function(resolve, reject) { // or possibly Q.promise (with lower case p), depending on version
            db.connection.query(sql, data, function(err, result) {
                if(err) {
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    };
}

现在，更高级别的代码变得非常简单：

function addNewPhotos(_id, files) {
    var sql_1 = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)',
        sql_2 = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?';
    return Q.all(files.map(function(one) {
        return db.connection.queryAsync(sql_1, [one.path, _id, 0]).then(function(result) {
            return db.connection.queryAsync(sql_2, [result.insertId]);
        });
    }));
};

为了防止单个故障扫描整个过程，您可能会选择捕获单个错误并注入某种默认值;

function addNewPhotos(_id, files) {
    var sql_1 = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)',
        sql_2 = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?',
        defaultPhoto = /* whatever you want as a default string/object in case of error */;
    return Q.all(files.map(function(one) {
        return db.connection.queryAsync(sql_1, [one.path, _id, 0]).then(function(result) {
            return db.connection.queryAsync(sql_2, [result.insertId]);
        }).catch(function() {
            return defaultPhoto;
        });
    }));
};

Answer 2

当所有操作完成后，在异步循环函数中进行返回

function addNewPhotos(_id, files) {
var deferred = Q.defer();
var new_photos = [];
var todo = files.length;
var done = 0;
_.each(files, function (one) {
    var data = [
        one.path,
        _id,
        0
    ]
    var sql = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)';
   db.connection.query(sql, data, function (err, result) {
       if (err)
            deferred.reject(err.name + ': ' + err.message);
       var sql = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?';
        if (result){
            db.connection.query(sql, [result.insertId], function(err, photo) {
                if (err) deferred.reject(err.name + ': ' + err.message);
                if (photo) {
                    new_photos.push(photo[0]);
                }
                if(++done >= todo){
                   deferred.resolve(Array.prototype.slice.call(new_photos));
                   return deferred.promise
                }
            });
        }
        else
        {
           if(++done >= todo){
              deferred.resolve(Array.prototype.slice.call(new_photos));
              return deferred.promise;
           }
        }
    })
  })
}