How to access a variable from a knex query inside of a second knex query?

26
January 12, 2019, at 5:40 PM

I have the following route where I want to get the sum associated to an id I get from the first query

app.post('/VerEmpresas', function (req, res) {
  var r, dot, ide;
  knex.from('empresas').select("id", "rut", "empresa", "razon_social", "email")
    .then((rows) => {
      for (row of rows) {
        ide = row['id'];
        knex.from('sucursales').sum("dotacion as SUM").where('id_empresas', '=', ide)
          .then((q0) => {
            dot = q0[0].SUM;
            console.log(dot);
          })
        console.log(dot);
        // do something here with dot
      }
      res.send();
    })
})

The first console.log show the correct value, but the second console.log shows undefined. How do I get to use the variable outside of the then()?

Answer 1

When you are using then, you are dealing with asynchronous code, which means the path the program follows isn't linear. You can read more about how to return values from asynchronous calls here.

In your case, the second console.log is run before dot has been defined. That might sound strange, but the database call simply isn't finished by the time the second console.log has run. In order to use the dot value after you define it, you have to chain another then to the same chain. Something like this:

app.post('/VerEmpresas', function (req, res) {
  var r, dot, ide;
  knex.from('empresas').select("id", "rut", "empresa", "razon_social", "email")
    .then((rows) => {
      for (row of rows) {
        ide = row['id'];
        knex.from('sucursales').sum("dotacion as SUM").where('id_empresas', '=', ide)
          .then((q0) => {
            dot = q0[0].SUM;
            console.log(dot);
          })
          .then(() => {
            console.log(dot);
            // do something here with dot
          })
      }
      res.send();
    })
})

The second then is run after the first. Usually, you pass results between thens with return and parameters but in your case, you have declared dot with var at the top of the function which makes it available that way.

There are two more issues with your code, but it falls outside the scope of this question:

  1. You have async calls inside a loop. This can cause issues as the loop can start all the async calls immediately. Read more about that here. I would advice you to switch to async/await, which is another way to orchestrate asynchronous calls in order to make it look like synchronous code.

  2. You are mixing SQL and ordinary JavaScript. This might be what you want in this case, but generally writing the entire query in SQL is the way to go. Especially the for loop looks weird. Looping over the entire result of the first query is basically the same as a select.

READ ALSO
How to break out values in a row to be in its own field based on a date value

How to break out values in a row to be in its own field based on a date value

In the Table Setup below you will see that the Amount data is broken down in various rows in the tableI am creating a view where i want to break this out where each Amount value is in its own field based on the CreatedDt field

31
SQL-queries in server.js or in react-component file?

SQL-queries in server.js or in react-component file?

I'm totally new at using Expressjs and React

52
Laravel app using Jaws Maria DB on Heroku getting MYSQL errors

Laravel app using Jaws Maria DB on Heroku getting MYSQL errors

I have a Laravel app on Heroku using Maria DB as a Add onMy App is up and running

31
How can I modify the jetpack code so that when I publish a post and jetpack publish it on twitter does not show the url of the new post?

How can I modify the jetpack code so that when I publish a post and jetpack publish it on twitter does not show the url of the new post?

Hi ppl i need help with this because it shows the url in twitter and it comes from and ugly subdomain so i dont want to show the url of the new post just send a tweet with text

46