Query parameterized with node.js and knex.js

Question

Query parameterized with node.js and knex.js

Navigation

#1 by (2 votes)

0

In the code below I make a query and fetch the contracts that are completed or not, according to the parameter concluido (0 or 1).

How do I make this query bring all contracts when the value of the concluido parameter is not passed by the request?

exports.read = function(req, res) {
    var concluido = parseInt(req.query.concluido);

    knex.select()
    .from('contrato as c')
    .innerJoin('empresa as e', 'e.idempresa', 'c.idempresa')
    .where('c.concluido', concluido)
    .then(function (result) {
        return res.status(200).json(result);
    })
    .catch(function (err) {
        return res.status(400).json(err);
    });
}

Currently I have two knex functions that are called according to the conditions, but as I have several parameters I will have to create several functions, I believe it is not the correct way.

Note: this may be an example in SQL itself.

sql node.js express

asked by anonymous 07.12.2016 / 18:10

1 answer

Datapicker with @ Html.EditorFor How to make the answer enter immediately after the question?

score 2 · Accepted Answer

One option is to split the string with a conditional:

let select = knex.select()
    .from('contrato as c')
    .innerJoin('empresa as e', 'e.idempresa', 'c.idempresa');

if (req.query.concluido) {
    select = select.where('c.concluido', concluido);
}

select
  .then(function (result) {
      return res.status(200).json(result);
  })
  .catch(function (err) {
      return res.status(400).json(err);
  });

So just put WHERE if one is done, and another option is to use a trick in SQL:

let select = knex.select()
    .from('contrato as c')
    .innerJoin('empresa as e', 'e.idempresa', 'c.idempresa');
    .where('c.concluido', concluido)
    .orWhere(concluido, '');

select
  .then(function (result) {
      return res.status(200).json(result);
  })
  .catch(function (err) {
      return res.status(400).json(err);
  });

.orWhere there will act when the completed one comes empty.