Question:
I have the following structure in the following tables.
partner (idPartner, name, email, phone) example partnerTag (idPartner, tag)
What do I need to do, a select on the partner table and do a for on the result and consult the partnerTag table by idParceiro and bring the results to fill a Json that looks like this.
[{ 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 for the result to get the ID and go to the other table it gets lost because it is async.
How can I solve this in NODE.JS because I have several queries that depend on each other 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); }); });
Answer:
Another approach, even better than doing this in Node.js is to do this data join in MySQL.
That way you can concatenate the tags with GROUP_CONCAT and get it in the columns of the row that MySQL returns, without having to do several queries to get "chunks" of information.
Test it 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