Format object with object array in properties - variable scope problem

Question

Good morning,

I have a problem returning from a webservice, I do a query to get Categories, and then another to get the items from that category. The goal is to assemble a return in the pattern:

{
 "categoryId" : 1,             <--- resultado da query 1
 "category" : "Alimentos",     <--- resultado da query 1
 "items" : [{
             "eventItemListId": 34,
             "itemId": 31,
             "item": "blabla",
             "amountNeeded": 4,
             "unityId": 1,
             "unity": "Un",
             "addedBy": "1"
            },
            (...)              <--- Este array de objetos vem da query 2
  ]
}

My webservice is as follows:

getEventItemListByCategory: (eventId) => {
  return new Promise((resolve, reject) => {
    const { connection, errorHandler } = deps
    const sqlData = [eventId]
    connection.query(process.env.QUERY_CATEGORY_ALL, sqlData, (error, results) => {
      if (error) {
        errorHandler(error, 'Falha ao obter lista de categorias', reject)
        return false
      }
      var ret = []
      for (let i = 0; i < results.length; i++) {
          let row = results[i]
        console.log(row.id + ' | ' + row.description)
        const sqlData = [eventId, row.id]
        connection.query(process.env.QUERY_ITEM_EVENT_ITEMS_BY_CATEGORY, sqlData, (error, results) => {
          if (error) {
            errorHandler(error, 'Falha ao obter lista de itens evento', reject)
            return false
          }
          ret.push({
            categoryId: row.id,
            category: row.description,
            items: JSON.stringify(results)
          })
        })
        console.log(JSON.stringify(ret))
      }
      resolve({ eventItems: JSON.stringify(ret) })
    })
  })
}

On the line where I log to the console, the result is (apparently ok):

[{"categoryId":1,"category":"Alimentos","items":"[  {"eventItemListId":34,"itemId":31,"item":"blabla","categoryId":1,"category":"Alimentos","amountNeeded":4,"unityId":1,"unity":"Un","addedBy":"1"},{"eventItemListId":36,"itemId":38,"item":"lalala","categoryId":1,"category":"Alimentos","amountNeeded":4,"unityId":1,"unity":"Un","addedBy":"1"},{"eventItemListId":5,"itemId":7,"item":"Linguiça","categoryId":1,"category":"Alimentos","amountNeeded":5,"unityId":4,"unity":"Kg","addedBy":"1"},{"eventItemListId":6,"itemId":10,"item":"Pão de alho","categoryId":1,"category":"Alimentos","amountNeeded":2,"unityId":2,"unity":"Pc","addedBy":"3"},{"eventItemListId":4,"itemId":5,"item":"Picanha","categoryId":1,"category":"Alimentos","amountNeeded":3,"unityId":4,"unity":"Kg","addedBy":"2"},{"eventItemListId":3,"itemId":3,"item":"Refrigerante","categoryId":1,"category":"Alimentos","amountNeeded":12,"unityId":5,"unity":"L","addedBy":"1"},{"eventItemListId":35,"itemId":32,"item":"Teste inclusão","categoryId":1,"category":"Alimentos","amountNeeded":10,"unityId":1,"unity":"Un","addedBy":"1"},{"eventItemListId":1,"itemId":1,"item":"Vodka","categoryId":1,"category":"Alimentos","amountNeeded":6,"unityId":3,"unity":"Gf","addedBy":"1"},{"eventItemListId":2,"itemId":2,"item":"Whisky","categoryId":1,"category":"Alimentos","amountNeeded":6,"unityId":3,"unity":"Gf","addedBy":"2"}]"},
{"categoryId":2,"category":"Infraestrutura","items":"[
  {"eventItemListId":7,"itemId":12,"item":"Caixa de som","categoryId":2,"category":"Infraestrutura","amountNeeded":2,"unityId":1,"unity":"Un","addedBy":"3"},{"eventItemListId":10,"itemId":20,"item":"Carvao","categoryId":2,"category":"Infraestrutura","amountNeeded":2,"unityId":2,"unity":"Pc","addedBy":"2"},{"eventItemListId":11,"itemId":21,"item":"Gelo","categoryId":2,"category":"Infraestrutura","amountNeeded":8,"unityId":2,"unity":"Pc","addedBy":"2"}]"},
{"categoryId":3,"category":"Outros","items":"[
{"eventItemListId":8,"itemId":18,"item":"Baralho","categoryId":3,"category":"Outros","amountNeeded":2,"unityId":1,"unity":"Un","addedBy":"3"},{"eventItemListId":9,"itemId":19,"item":"Narguile","categoryId":3,"category":"Outros","amountNeeded":1,"unityId":1,"unity":"Un","addedBy":"3"}]"}]

But the webservice, when tested in Postman does not return value. By doing a test moving the 'console.log' out of the scope it is, standing just before the 'resolve', it actually logs empty arrays.

I have already taken the test to change the location of the variable declaration in all blocks, but I cannot assemble the return as desired until the final return scope. Can anyone explain to me exactly where the mistake is?

Thank you.

Answer 1

You are solving the problem before you finish the querys within the for. Try to check if it is the last iteration before returning the reply to the client. The code would be next to the below:

for (let i = 0; i < results.length; i++) {
    let row = results[i]
    console.log(row.id + ' | ' + row.description)
    const sqlData = [eventId, row.id]
    connection.query(process.env.QUERY_ITEM_EVENT_ITEMS_BY_CATEGORY, sqlData, (error, results) => {
        if (error) {
            errorHandler(error, 'Falha ao obter lista de itens evento', reject)
            return false
        }
        ret.push({
            categoryId: row.id,
            category: row.description,
            items: JSON.stringify(results)
        })

        // Validar indice antes de resolve a promise
        if (i === results.length - 1) {
            console.log(JSON.stringify(ret))
            resolve({ eventItems: JSON.stringify(ret) })
        }
    })
}

The command connection.query responds asynchronously, hence you must make sure that they are finished before returning the array to the client. Therefore, the ideal would be to create a list of Promise for each query execution and resolve the main Promise at the end of all the Promises using Promise.all