NodeJS My SQL query with Chain promise

Question

I have 3 function and i want to call this function to step by step, for example when i will call firs function and get result, i have to call second function and pass parameter returned from first call. after i will finish second call i have to call third function and pass parameter returned from second function.

#1:

getCategory = function (branch_id) {
    var deferred = q.defer();
    var categoryData;
    var query = 'SELECT id,name,price,currency FROM category  where branch_id=?';
    pool.getConnection(function (err, connection) {
        connection.query(query, [branch_id], function (error, row, fields) {
            if (error) {
                deferred.reject(error);
            } else {
                connection.release();
                deferred.resolve(row);
            }
        });
    });
    return deferred.promise; }

#2:

getRoom = function (branch_id, categoryID) {
    var deferred = q.defer();
    var roomData;
    var roomSql = 'SELECT id,room_no,name,price,currency FROM room  where branch_id=? and category_id=?';
    pool.getConnection(function (err, connection) {
        connection.query(roomSql, [branch_id, categoryID], function (error, row, fields) {
            if (err) {
                deferred.reject(err);
            } else {
                connection.release();
                deferred.resolve(row);
            }
        });
    });
    return deferred.promise;
}

#3:

getReservationL = function (room_id, start_date, end_date) {
    var deferred = q.defer();
    var reservationData;
    var reservationSql = 'SELECT d.id,d.create_date,d.update_date,d.room_id,d.status_id,d.start_date,d.end_date, ' +
        ' s.name as status_name,a.id as reservation_id,a.person_no as person_no, p.first_name,p.last_name,p.email ' +
        ' FROM reservation_detail d ' +
        ' inner join reservation_status s on d.status_id=s.id ' +
        ' inner join reservation a on d.reservation_id=a.id ' +
        ' inner join person p on a.person_no=p.personal_no ' +
        ' where d.room_id=? and d.start_date >? and d.start_date<?';
    pool.getConnection(function (err, connection) {
        connection.query(reservationSql, [room_id, start_date, end_date], function (error, row, fields) {
            if (err) {
                deferred.reject(err);
            } else {
                connection.release();
                deferred.resolve(row);
            }
        });
    });
    return deferred.promise;
}

I need something like this:

data = getCategory()
for(i=0;i<data.length;i++){
   data[i].room = getRoom(data[i].id);
   for(j=0;j<data[i].room[j].length;j++){
      data[i].room[j].reservation = getReservationL(data[i].room[j].id);
   }
}

How to implement this pseudo code in NodeJS using promise or callback. I would prefer to use promise.

UPDATE#1 I have data like this after second iteration

[
  {
    "id": 64,
    "name": "VIP",
    "price": 116.5,
    "currency": "USD",
    "room": [
      {
        "id": 44,
        "room_no": "101",
        "name": "A",
        "price": 100,
        "currency": "EUR"
      },
      {
        "id": 274,
        "room_no": "505",
        "name": "a",
        "price": 1,
        "c\r\nurrency": "GEL"
      }
    ]
  },
  {
    "id": 74,
    "name": "SUPER VIP",
    "price": 110,
    "currency": "EUR",
    "room": [
      {
        "id": 54,
        "room_no": "102",
        "name": "A",
        "price": 100,
        "currency": "GEL"
      },
      {
        "id": 284,
        "room_no": "606",
        "name": "a",
        "price": 1,
        "currency": "GEL"
      }
    ]
  },
  {
    "id": 84,
    "name": "DOUBLE",
    "price": 110,
    "currency": "GEL",
    "room": [
      {
        "id": 204,
        "room_no": "103",
        "name": "b",
        "price": 120,
        "currency": "GEL"
      }
    ]
  }
]

I wand to iterate over each category's each room's.

     getCategory(branch_id).then(firstRecords => {
        let promises = firstRecords.map(function (record) {
            return getRoom(branch_id, record.id)
                .then(roomData => {
                    var res = Object.assign({}, record, { room: roomData });
                    return res;
                })
        });
        return Promise.all(promises);
//HERE i have data that is placed above.
    }).then(secondRecords => {
        let promises = secondRecords.map(function (category) {
            return category.room;
        }).map(function (rooms) {
            console.log('SECOND', rooms);
            return rooms;
        }).map(function (reservation) {
            console.log('THERD', reservation);
            return reservation;
        })
        return Promise.all(promises);
    }).then(reservation => {
        console.log("Reservation", reservation);
    })

UPDATE#2 final solution is here

getCategory(branch_id) .then( categories => {

  let roomPromises = categories.map( category => {
    return getRoom(branch_id, category.id)
    .then( rooms => Object.assign({}, category, { rooms }) )   });

  return Promise.all(roomPromises) }) .then( category_rooms => {

  let finalPromise = category_rooms.map( category => {

    let reservationPromises = category.rooms.map( room => {
      return getReservationL(room.id, start_date, end_date)
      .then( reservations => Object.assign({}, room, { reservations }) )
    })

    return Promise.all(reservationPromises)
    .then( room_reservations => {
      return Object.assign({}, category, { rooms: room_reservations })
    });   })

  // const flattenPromise = finalPromise.reduce( (a, b) => a.concat(b), []);   // return Promise.all(flattenPromise);
     return Promise.all(finalPromise) }) .then( data => console.log('final: ', data) )

Answer 1

As your logic contains lot of loop statements for data selection and transformation, you need to refactor it into something like:

getCategory(brandId)
    .then(addRoomData)
    .then(addReservationData)
    .then(handleFinalData)
    .catch(handleError);

For addRoomData and addReservationData functions, you may need to use Promise.all() combined with .forEach().

If you want to write your code in a more readable way, consider using async / await.

Answer 2

I think the more optimize way is to use async.waterfall function of async module. https://caolan.github.io/async

async.waterfall([
function(callback) {
    getSomething(options, function (err, result) {
        if (err) {
            callback(new Error("failed getting something:" + err.message));
            // we should return here
        }
        // since we did not return, this callback still will be called and
        // `processData` will be called twice
        callback(null, result);
    });
},
function(data, callback){
  //do something
}
], function(err,results){
  //get final results
});

Answer 3

You can resolve a promise using .then and can chain then to resolve multiple promises in a synchronous fashion.

Maybe this will solve your use case.

getCategory()
.then( firstRecords => {

  console.log('firstRecords: ', firstRecords);

  let promises = firstRecords.map( record => getRoom(record) );
  return Promise.all(promises);
})
.then( secondRecords => {

  console.log('secondRecords: ', secondRecords);

  let promises = secondRecords.map( record => getReservationL(record) );
  return Promise.all(promises);
})
.then( thirdRecords => {

  console.log('thirdRecords: ', thirdRecords);
})

Reference: Promise then chaining

The then method returns a Promise which allows for method chaining.

If the function passed as handler to then returns a Promise, an equivalent Promise will be exposed to the subsequent then in the method chain

Refrence: Promise all

The Promise.all() method returns a single Promise that resolves when all of the promises in the iterable argument have resolved or when the iterable argument contains no promises. It rejects with the reason of the first promise that rejects.

Promise.all([ { key: 1 }, Promise.resolve(3), 1, true ])
.then( results => {
    results[0]; // { key: 1 }
    results[1]; // 3
    results[2]; // 1
    results[3]; // true
})

Update #1

Promise.all only accept an array of promise and not object with promises on the key.

# wrong
Promise.all([
    { key: Promise.resolve(1) },
    { key: Promise.resolve(2) },
    { key: Promise.resolve(3) },
])

# right
Promise.all([ 
    Promise.resolve(1), 
    Promise.resolve(2), 
    Promise.resolve(3) 
])

you could do something like this to achieve the what you have mentioned in comments.

getCategory(branch_id)
.then( firstRecords => {

  console.log('firstRecords: ', firstRecords);

  let promises = firstRecords.map( record => {
    return getRoom(branch_id, record.id)
    .then( roomData => Object.assign({}, record, { room : roomData }) )
  });

  return Promise.all(promises)

})

If you want to append the data of the first and second promise, then resolve the promises there only to access the data of both in one place.

Update #2

As you mentioned in comments, this code might help you.

getCategory(branch_id)
.then( categories => {

  let roomPromises = categories.map( category => {
    return getRoom(branch_id, category.id)
    .then( rooms => Object.assign({}, category, { rooms }) )
  });

  return Promise.all(roomPromises)

})
.then( category_rooms => {

  let finalPromise = category_rooms.map( category => {

    let reservationPromises = category.rooms.map( room => {
      return getReservationL(room.id, start_date, end_date)
      .then( reservations => Object.assign({}, room, { reservations }) )
    })

    return Promise.all(reservationPromises)
    .then( room_reservations => {
      return Object.assign({}, category, { rooms: room_reservations })
    });
  })

  return Promise.all(finalPromise)
})
.then( data => console.log(data) )