Nested queries with pg-promises

Question

I need to make a query with pg-promise using the result to make 3 others queries but i am getting this error when executing:

Unhandled rejection TypeError: Method 'batch' requires an array of values. at batch (C:\apps\pfc\node_modules\spex\lib\ext\batch.js:61:26) at C:\apps\pfc\node_modules\spex\lib\ext\batch.js:149:26 at Task.batch (C:\apps\pfc\node_modules\pg-promise\lib\task.js:120:39)..............

This is my code:

db.task(t => {
    return t.one('select gid, idgrupo from orden where gid = $1', req.params.ordenid, resultado => {
        return t.batch([
            t.one('select o.gid as num, v.matricula, v.denom, o.pkini, o.pkfin, o.fechaini, o.f_estimada, o.fechafin, o.idestado, o.descr, o.instr, g.id as idgrupo, g.nombre as grupo, g.actividad, e.descr as estado from orden as o inner join estado as e on o.idestado = e.gid inner join grupo as g on o.idgrupo = g.id inner join via as v on o.idctra = v.gid and o.gid = $1', resultado.gid),
            t.any('select * from operacion order by gid where idgrupo = $1', resultado.idgrupo),
            t.any('select m.gid, m.fechamed as fecha, m.cantidad, m.costemed as coste, o.codigo, o.descr from medicion m, operacion o where m.idorden = $1 and m.idope = o.gid order by fecha asc', resultado.gid)
        ])
            .then(data => {
                res.render('mediciones/nuevaMed', {
                        title: 'Crear / Modificar parte de trabajo',
                        orden: data[0],
                        operaciones: data[1],
                        medicion: [],
                        mediciones: data[2],
                        errors: []
                });
            }).catch(function(error) {next(error);});
    }).then(t.batch);
 });

Answer 1

I'm the author of pg-promise.

---

There are several problems with your code, as explained below...

• Value-transformation callback that you use with method one is meant for transforming the returned value. And even though technically you can return a promise, which you do, this creates an awkward code, from the promises point of view. I would advice not to do it that way.

• It is not a good solution putting non-database code inside database tasks, which creates mixed-purpose code that's way more difficult to maintain, and is also considered an anti-pattern.

• And the very bug you are getting in the end is because you are doing .then on the result of the batch, which in your code will be undefined, passing it into another batch, which obviously doesn't like it and throws that very error. The thing is, you do not need it there at all. You must have copied if from the code where it was needed, and put it where it is not needed :)

Having said all that, here's what your code should look like:

db.task(t => {
    return t.one('select gid, idgrupo from orden where gid = $1', req.params.ordenid)
        .then(resultado => {
            return t.batch([
                t.one('select o.gid as num, v.matricula, v.denom, o.pkini, o.pkfin, o.fechaini, o.f_estimada, o.fechafin, o.idestado, o.descr, o.instr, g.id as idgrupo, g.nombre as grupo, g.actividad, e.descr as estado from orden as o inner join estado as e on o.idestado = e.gid inner join grupo as g on o.idgrupo = g.id inner join via as v on o.idctra = v.gid and o.gid = $1', resultado.gid),
                t.any('select * from operacion order by gid where idgrupo = $1', resultado.idgrupo),
                t.any('select m.gid, m.fechamed as fecha, m.cantidad, m.costemed as coste, o.codigo, o.descr from medicion m, operacion o where m.idorden = $1 and m.idope = o.gid order by fecha asc', resultado.gid)
            ]);
        });
})
    .then([orden, operaciones, mediciones] => {
        res.render('mediciones/nuevaMed', {
            title: 'Crear / Modificar parte de trabajo',
            orden,
            operaciones,
            medicion: [],
            mediciones,
            errors: []
        });
    })
    .catch(next);

And it gets even simpler when using ES7 syntax:

db.task(async t => {
    const resultado = await t.one('select gid, idgrupo from orden where gid = $1', req.params.ordenid);
    const orden = await t.one('select o.gid as num, v.matricula, v.denom, o.pkini, o.pkfin, o.fechaini, o.f_estimada, o.fechafin, o.idestado, o.descr, o.instr, g.id as idgrupo, g.nombre as grupo, g.actividad, e.descr as estado from orden as o inner join estado as e on o.idestado = e.gid inner join grupo as g on o.idgrupo = g.id inner join via as v on o.idctra = v.gid and o.gid = $1', resultado.gid);
    const operaciones = await t.any('select * from operacion order by gid where idgrupo = $1', resultado.idgrupo);
    const mediciones = await t.any('select m.gid, m.fechamed as fecha, m.cantidad, m.costemed as coste, o.codigo, o.descr from medicion m, operacion o where m.idorden = $1 and m.idope = o.gid order by fecha asc', resultado.gid);
    return {orden, operaciones, mediciones};
})
    .then({orden, operaciones, mediciones} => {
        res.render('mediciones/nuevaMed', {
            title: 'Crear / Modificar parte de trabajo',
            orden,
            operaciones,
            medicion: [],
            mediciones,
            errors: []
        });
    })
    .catch(next);