Asynchronous Node Js "For" Loop With Database Query

Here is the 'for' loop to run query(SQLite3 database) for each 'id' in an array. The problem is that the for loop continues before the query is finished. Is there a way to check i

Solution 1:

pls import async module.

qry = "SELECT patients.*, patient_visits.visit_id,patient_visits.patient_id, patient_visits.visitdate, patient_visits.visittime FROM patients LEFT JOIN patient_visits ON patients.id = patient_visits.patient_id " + where + " GROUP BY patients.id ORDER BY patients.id  DESC LIMIT " + limit + " OFFSET " + offset;
db.all(qry, (err, results) => {
    if (err) {
        response.error = err;
        res.send(response);
    } else {
        response.patients = patients;

        async.forEachOf(patients, function (patient, key, callback) {
            db.each("SELECT visit_id FROM patient_visits where patient_id='" + patients[key] + "' AND visitdate >='" + moment().format('YYYY-MM-DD') + "'", function (err, row) {
                if (row) {
                    response.patients[i].check = "true";
                }else{
                    callback();
                }
            });
        }, function (error) {
            if (error) {
                console.log(error)
            } else {
                res.send(response);
            }
        })

    }
});

Solution 2:

Here is the simple way to do but not recommended. There is a chance it will execute res.send(response); multiple times. I suggest you learn how to use promise.

qry = "SELECT patients.*, patient_visits.visit_id,patient_visits.patient_id, patient_visits.visitdate, patient_visits.visittime FROM patients LEFT JOIN patient_visits ON patients.id = patient_visits.patient_id "+where+" GROUP BY patients.id ORDER BY patients.id  DESC LIMIT "+limit+" OFFSET "+offset;
db.all(qry, (err, results) => {
    var loopCount = 0;
    if(err){
        response.error = err;
        res.send(response);
    }else{
        response.patients = patients;
        for (var i = 0; i < patients.length; i++) {
            response.patients[i].check = "false";
            var patient = response.patients[i];
            db.each("SELECT visit_id FROM patient_visits where patient_id='"+patient.id+"' AND visitdate >='"+moment().format('YYYY-MM-DD')+"'", function(err, row) {
                if (row) {
                    response.patients[i].check = "true";
                    loopCount++;
                    if(loopCount == patients.length){
                        res.send(response);
                    }
                }
            });
        }
    }

});

Solution 3:

Use foreach and something called promises. Since Nodejs is async, to make it wait for query results, you have to use promises.

Solution 4:

Use Promise.all to handle multiple asynchronous requests.Write a new function to get the visit_id from the database. Something like this,

function getPatientVisitsByVisitId(visitId){
 // return a new promise.
 }
 let promises= [];
 for(var i = 0; i < patients.length; i++){
  patientVisits.push(getPatientVisitsByVisitId(response.patients[i]));
 }
 Promise.all(promises).then((results) => {
 // results will have visit id's.
 })
 .catch((error) => {
  // handle error here
  })

Solution 5:

Is there a way to check if the query has finished?

You can use Promise.all() to know whether all the async calls were completed or not.

'use strict';

function fetchPatients(where, limit, offset) {
  let qry = "SELECT patients.*, patient_visits.visit_id,patient_visits.patient_id, patient_visits.visitdate, patient_visits.visittime FROM patients LEFT JOIN patient_visits ON patients.id = patient_visits.patient_id " + where + " GROUP BY patients.id ORDER BY patients.id  DESC LIMIT " + limit + " OFFSET " + offset;
  return new Promise((resolve, reject) => {
    db.all(qry, (err, patients) => {
      if (err) {
        return reject(err);
      }
      resolve(patients);
    });
  });
}

function queryVisitsByPatientId(patient) {
  return new Promise((resolve, reject) => {
    patient.check = "false";
    db.each("SELECT visit_id FROM patient_visits where patient_id='" + patient.id + "' AND visitdate >='" + moment().format('YYYY-MM-DD') + "'", function (err, row) {
      if (err) {
        return reject(`Failed for ${patient.id}`);
      }
      if (row) {
        patient.check = "true";
      }
      return resolve(patient);
    });
  });
}

fetchPatients(where, limit, offset).then(patients => {

  let allPatients = patients.map(patient => {
    return queryVisitsByPatientId(patient);
  });

  return Promise.all(allPatients);
}).then(patientDetails => {
  return res.send({
    patients: patientDetails
  });
}).catch(err => {
  return res.send({
    error: err
  });
});