Subquery within a javascript and mysql callback

Question

Subquery within a javascript and mysql callback

Navigation

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

0

Good to all I try to get the details of a foreign table after making a query, I do a for cycle to go through all the results of the main query and make a sub query with the data of the foreign id, this from a server in express using mysql as sgdb.

  var sql = 'SELECT * FROM producto ORDER BY idproducto DESC LIMIT '+per_page+' OFFSET '+offset+'';
  db.query(sql).then((results) =>{
    productos = [];
    if(results.length > 0){
      for (var i = 0; i < results.length; i++) {
        var item = results[i];
        sql = 'SELECT * FROM categoria WHERE idcategoria = ?';
        db.query(sql, item.categoria).then((result) => {
          if(result.length > 0){
            item.categoria = result;
          }else{
            item.categoria = null;
          }
          productos.push(item);
        });
      }
      console.log(productos);
      res.status(200).send({
        productos: productos
      });
    }else{
      res.status(404).send({
        message: 'Sin registros'
      });
    }
  });

According to the logs of the console, the data is ordered correctly, but when the subquery is finished and the data object is placed in another array to be sent by the server, they do not reach the array and the browser is empty.

Any ideas or comments? I have very little to be with javascript technologies and I just found the connection client with mysql. Thanks for your time

mysql javascript nodejs

asked by Rafa 09.04.2018 в 22:36

source

2 answers

How to make a NumberPicker on Android that has 3 elements to select? Obtain Excepcion when a mail does not reach its recipient by a badly written recipient or that does not exist

score 0 · Answer 1

your code to make it better use the promises here an example.

function consultaCategoria(id_categoria){
  return new Promise(function (resolve , reject) {
    db.query(sql, item.categoria).then((result) => {
            if(result.length > 0){
              item.categoria = result;
            }else{
              item.categoria = null;
            }
            resolve(item);
          });
})
function consultarProducto(){
  return new Promise(function (resolve , reject) {
    db.query(sql).then((results) =>{
      resolve(results)

    })
  })
}

consultarProducto().then((results) =>{
   for (var i = 0; i < results.length; i++){
     consultaCategoria(result.results[i]).then((item) =>{
       productos.push(item)
   }
   return productos;
}).then((productos) =>{
  res.status(200).send({
        productos: productos
      });
})

score 0 · Answer 2

If it is a good contribution, but related to this topic I would like to store this relational field. My article interface is like this ...

The problem arises from the POST method when I already want to Save ... I get this error

And this is my server-side POS code

postNewArticle: function (req, res, next) { req.assert ('name', 'Mandatory name to enter !!'). notEmpty (); req.assert ('code', 'Code Required to enter !!'). notEmpty (); req.assert ('category', 'Mandatory category of entering !!'). notEmpty (); req.assert ('stock', 'Stock Required to enter !!'). notEmpty (); req.assert ('image', 'Mandatory image of entering !!'). notEmpty ();

var errors = req.validationErrors ();   if (! errors) {                                                       // sanitize ('') = > disinfects the specified request locations,                                                       // Since it's not empty, String where is ...      v_name = req.sanitize ('name') .escape (). trim (); // escape () Serves so that the Column 'name' can not be null
     v_codigo = req.sanitize ('code') .escape (). trim (); // trim () Used to make property of a field define 'trim' of undefined        v_category = req.sanitize ('category'). escape (). trim (); // escape () Serves so that the Column 'name' can not be null
     v_stock = req.sanitize ('stock') .escape (). trim (); // trim () Allows property of a field to be defined 'trim' of undefined      v_image = req.sanitize ('image'). escape (). trim (); // escape () Serves so that the Column 'name' can not be null

var art = {
  nombre: v_nombre,
  codigo: v_codigo,
  categoria: v_categoria,
  stock: v_stock,
  imagen: v_imagen,
  descripcion:v_descripcion

   }
var categoria= 0
var config = require('../database/config');
var cnn = mysql.createConnection(config);


cnn.connect();
var queryAsync = Promise.promisify(cnn.query.bind(cnn));

   queryAsync('INSERT INTO Articulo SET ?', art)
   .then(function(results){
    categoria=results[0]
    //console.log(categoria)
   //cnn.query('INSERT INTO Articulo SET ?', art , function(err, rows, fields){

     if(err) throw err;
  req.flash('msg_info', 'Artículo Registrado exitosamente!!!');
  cnn.end();
  res.render('almacen/articulo/create', {
  categoria:categoria
  });
})


 //})

} else { var errorDetails="

Sorry, there are errors !!!

";

for(i in errors){

  error= errors[i];
  errorDetalles+= '<li>'+error.msg+'</li>'; 
  }
  errorDetalles+= '</ul>';

req.flash('msg_error', errorDetalles);

 res.render('almacen/articulo/create', {

 });

}

},

And this is my code on the Client's side ....

New Article

      <% if(messages.msg_error) { %>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
            <strong><%- messages.msg_error%></strong> <br></br>

            <button type="button" class="close" data-dismiss="alert" aria-label="Close">
            <span aria-hidden="true">&times;</span>
            </button>
            </div>
              <%}%>
     </div>       
  </div>

First name Category "> Code Stock Description Image save Cancel