How do I use WHERE using Knexjs as a conditional?

Question

I am doing a put route to update, and using knexJS to update the data in the database, and in my code the solution I found was this below:

dbKnex("game").where({ id: id }).select("*").then(data => {
        if(data != "") {
                var { nome, preco } = req.body;

                dbKnex("game").where({ id: id }).update({ nome: nome, preco: preco}).then(data => {
                res.sendStatus(200);
            }).catch(err => {
                console.log(err);
            }) 
        } else {
            res.sendStatus(404);
        }
    })

In this first search of the database it returns the data that was found in a select, which are then used in a check if they exist in the database (if(data != "")), but I want another solution that doesn’t need that select, only one condition on the WHERE part.

Answer 1

You could do it in a simpler way by passing only the conditional and the parameters to be updated. The data returns array with number of records that have been changed. Can be [1], [2], [3], ..., but I believe that id is unique, then it would return only the record that has been updated. If the data[0] for undefined, means that the id was not found in the database, so no record was changed.

The code could be written this way:

// não precisa do "select"
dbKnex('game')
  .where({ id: id })
  .update({ nome: nome, preco: preco })
  .then((data) => {
    if (!data[0]) { 
      // se o "data[0]" for undefined, significa que não alterou nenhum
      // registro pois não achou nenhum que segue a condicao.
      res.sendStatus(404);
    } else {
      res.sendStatus(200);
    }
  })
  .catch((err) => {
    console.log(err);
  });

This code waives the need to use the select, because we are already applying the logic in case no record has been found in the bank at if (!data[0]) and also avoids having to make two queries in the bank, one to find and the other to update.

---

• detail: if(data != "") this comparison using only != is dangerous. Avoid using it.
• the data would be an array with changed number of records. So, for your case, we expect an array of type [1] for the modified record, or [] if no record has been changed.