var bookshelf = require('bookshelf')(knex); const ArticuloImagen = bookshelf.Model.extend({ tableName: 'ARTICULOS_IMAGEN' }); const Articulo = bookshelf.Model.extend({ tableName: 'ARTICULOS', imagenes: function() { return this.hasMany(ArticuloImagen, 'id_articulo', 'id'); } }); router.get('/articulos', (req , res) => { let cantidadesVendidas = knex.schema .raw(`select stock.SEC, stock.ART, SUM(stock.can) as cantidadVendida from AMOVSTOC stock join ARTICULOS art on art.CodSec = stock.SEC and art.CodArt = stock.ART and art.categoria_selfservice > 0 and art.PreVen > 0 where stock.FEC >= dateadd(month, -2, getdate()) and stock.CYV = 'V' and TCO = 'FT' group by stock.sec, stock.art`) var articulos = Articulo .where((query) => { query.where('NHA', 0); query.where('PreVen', '>', 0); query.where('categoria_selfservice', '>', 0); }) .fetchAll({withRelated: ['imagenes']}) Promise.all([articulos, cantidadesVendidas]) .then(values => { let articulos = values[0]; let cantidadesVendidas = values[1]; var promisesCodBar = []; articulos.forEach(articulo => { let cantidadVendida = cantidadesVendidas.filter(art => { return articulo.get('CodArt') == art.ART && articulo.get('CodSec') == art.SEC; }); articulo.set('cantidadVendida', cantidadVendida[0] ? cantidadVendida[0].cantidadVendida : 0); promisesCodBar.push( knex('CODBAR').where({ CodSec: articulo.get('CodSec'), CodArt: articulo.get('CodArt') }) ); }); Promise.all(promisesCodBar).then((codigoBarra) => { codigoBarra.forEach(codigo => { codigo = codigo[0]; let articulo = articulos.filter(art => { return art.get('CodArt') == codigo.CodArt; }); articulo[0].set('codigoBarra', codigo.CodBar); }); res.status(200).send(articulos); }); }); }); router.get('/articulos/:id', (req, res) => { Articulo .where({id: req.params.id}) .fetch({withRelated: 'imagenes'}) .then(articulo => res.status(200).send(articulo)) .catch(err => { res.status(500).send(err)}) }); module.exports = router;