Mysql query with subquery in Nodejs

Question

I have the following structure in the following tables.

partner (idParceiro, name, email, phone) example partnerTag (idPartner, tag)

What I need to do, a select in the partner table and do a for in the result and query the partner table by idParty and bring the results to fill in a Json that stays that way.

[{
  nome: 'nome teste',
  email: 'email teste',
  telefone: 1199999999,
  tags: ['tag1', 'tag2', 'tag3']
}, {
  nome: 'nome teste',
  email: 'email teste',
  telefone: 1199999999,
  tags: ['tag1', 'tag2', 'tag3']
}]

The problem is that when I consult the first table and do a go in the result to get the ID and go in the other table it gets lost by being async.

How can I resolve this in NODE.JS because I have several queries that depend on another to generate a Json.

Follow my code below.

router.route('/parceiro').get(function(req, res) {
  parceiro.consultar(req, function(err, rows){
    if(err) return res.status(400).json(err);

    var p  = [];
    _.each(rows, function(one) {
      var pa = {};
      pa.parceiroId = one.parceiroId;
      pa.clienteId = one.clienteId;
      pa.nome = one.nome;
      pa.imagemDestaque = one.imagemDestaque;
      pa.imagemLogo = one.imagemLogo;
      pa.desconto = one.desconto;
      pa.titulo = one.titulo;
      pa.descricao = one.descricao;
      pa.urlSite = one.urlSite;
      pa.validadeDe = one.validadeDe;
      pa.validadeAte = one.validadeAte;
      pa.isCupom = one.isCupom;
      pa.urlOferta = one.urlOferta;
      pa.cupomDesconto = one.cupomDesconto;
      pa.ativo = one.ativo;
      pa.dataCadastro = one.dataCadastro;
      pa.tags = [];
      parceiro.tag(req, function(err, r){
        _.each(r, function(two) {
          pa.tags.push(two.tag);
        });
      });
      pa.categorias = [];
      pa.regioes = [];
      p.push(pa);
    });
    return res.status(200).json(p);
  });
});

Answer 1

Another approach, even better than doing this in Node.js is to make this data merge in Mysql.

So you can concatenate the tags with the GROUP_CONCAT and receive this in the columns of row that Mysql returns, without having to make several queries to fetch "pieces" of information.

Forehead like this:

SELECT p.id, p.nome, GROUP_CONCAT(t.tag) AS tags FROM parceiro AS p
INNER JOIN parceiroTag AS t ON p.id = t.parceiroid
GROUP BY id

Example here (link).

Answer 2

You need to string that logic together just to call res.status().json() when that object has all the properties you need. And as everything is asynchronous the thing complicates a little.

A good library to work these threads, is called async. But doing "by hand" I think you need something like this:

function waitForIt(obj, tables, done) {
    var callbacks = 0;
    return function(err) {
        if (err) return console.log(err);
        callbacks++;
        if (callbacks == tables.length) done(err, obj);
    }
}

function join(table, obj, cb) {
    parceiro[table](req, function(err, r) {
        obj[table] = r.map(function(row) {
            return row.tag;
        });
        cb(err);
    });
}

router.route('/parceiro').get(function(req, res) {
    parceiro.consultar(req, function(err, rows) {
        if (err) return res.status(400).json(err);
        rows.forEach(function(pa) {
            var cb = waitForIt(pa, ['tag', 'regi'], function(err, obj) {
                if (err) return res.status(400).json(err);
                return res.status(200).json(obj);
            });
            tables.forEach(function(table) {
                join(table, pa, cb);
            });
        });
    });
});

The idea is to call all the tables you need, add to the object what the table brings and then, only when all the callbacks of the Subqueries have been called; only then call the last callback I named done.

Notes:

• #1 I’ve made the code now, I haven’t tested it, but it’s the logic you need.
• #2 still think you could do this only with Mysql :)
• #3 use if (err) return res.status(400).json(err); will expose internal application errors to the user and may cause gaps in BD security.

Answer 3

I was able to do it this way.

exports.consultar  = function (req, callback) {
  req.getConnection(function(err,connection){
    connection.query('SELECT * FROM parceiro', function(err, result){
      done = _.after(result.length, function () {
        callback(err, result)
      });
      result.forEach(function(pa){
        connection.query('SELECT * FROM parceiroTag where parceiroId = ' + pa.parceiroId, function(err, result){
          pa.tags = [];
          for (var i = 0; i < result.length; i++) {
            pa.tags.push(result[i].tag);
          }
          done();
        });
      });
    });
  });
};

Thanks for all your help.