Query mysql with subquery in NodeJs

Question

Query mysql with subquery in NodeJs

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I have the following structure in the following tables.

partner (idPartner, name, email, phone) example Tag partner (old id, tag)

What I need to do, a select in the partner table and make a is in the result and query the tableTag partner by the idParceiro and bring the results to fill 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 query the first table and make a for in the result to get the ID and go in the other table it is lost because it is async.

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

Below is my code.

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

mysql javascript array node.js

asked by anonymous 14.04.2016 / 22:59

3 answers

How to transform rows into columns, when the number of rows is variable in R Pickadate Angular - Translation

score 1 · Answer 1

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

So you can concatenate the tags with GROUP_CONCAT and get this in the columns of row that MySQL returns, without having to make multiple queries to fetch "bits" of the information.

Test 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).

score 0 · Answer 2

You need to chain this logic to just call res.status().json() when this object has all the properties you need. And since everything is asynchronous the thing complicates a little.

A good library to work with these threads, is called async . But by "handy" 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, join the object to the table and then, only when all the callbacks of the subqueries have been called; only then call the last callback that I gave the name of done .

Notes:

# 1 I made the code now, did not test, but that's the logic you need.
# 2 I still think I could do this only with MySQL:)
# 3 Using if (err) return res.status(400).json(err); will expose internal application errors to the user and may cause BD security flaws.

score 0 · Answer 3

I was able to do it as follows.

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 everyone's help.