Render my ejs file after my query finishes

Question

I want to make sure that my application will only call res.render('pages/students' ...) after all my queries finishes and I get all the necessary data. However, I completely have no clue how to do this at all and I am having trouble applying all the examples I see online onto my code. Can someone give me a hand? (There are two con.query(...) in my route. I want to only render('pages/students'...) after both of these con.query completely finishes. Therefore, I am using async to run all my queries to completion. However now for some reason my page wont event load.

app.get('/admin', function(req, res) {
    var sql =
        'SELECT events.id, name, description, MONTHNAME(start_time) AS month, DAY(start_time) AS day, ' +
        "YEAR(start_time) AS year, DATE_FORMAT(start_time, '%h:%i%p') AS start_time, HOUR(start_time) AS start_hour, MINUTE(start_time) AS start_minute, " +
        "DATE_FORMAT(end_time, '%h:%i%p') AS end_time, HOUR(end_time) AS end_hour, MINUTE(end_time) AS end_minute, location, max_capacity, hidden_sign_up, " +
        'eventleaders.first_name AS eventLeader FROM events LEFT JOIN eventleaders_has_events ON events.id = eventleaders_has_events.events_id ' +
        'LEFT JOIN eventleaders ON eventleaders_has_events.eventleaders_id = eventleaders.id;';
    var events = {};
    con.query(sql, function(err, results) {
        if (err) throw err;
        async.forEachOf(results, function(result, key, callback) {
            var date = result.month + ' ' + result.day + ', ' + result.year;
            var other = 'N/A';
            if (result.other !== null) {
                other = result.other;
            }
            if (typeof events[date] === 'undefined') {
                events[date] = {};
            }
            var studentAttendees = {};
            var sql =
                'SELECT * FROM students INNER JOIN students_has_events ON students.id = students_has_events.Students_id ' +
                'WHERE students_has_events.Events_id = ?';
            var values = [result.id];
            con.query(sql, values, function(err, results1) {
                if (err) throw err;
                async.forEachOf(results1, function(result1, key, callback) {
                    studentAttendees[result1.first_name + ' ' + result1.last_name] = {
                        netID: result1.netID,
                        phoneNumber: result1.phone,
                        email: result1.email
                    };
                });
                //still need to get the event leader attendees
                events[date][result.name] = {
                    startTime: result.start_time,
                    location: result.location,
                    endTime: result.end_time,
                    description: result.description,
                    eventLeader: result.eventLeader,
                    numberRegistered: result.hidden_sign_up,
                    maxCapacity: result.max_capacity,
                    poster: '/images/sample.jpg',
                    other: other,
                    attendees: studentAttendees
                };
            });
        });
    });
});

Answer 1

Two options:

app.get('/admin', function(req, res) {
    ...
    con.query(sql, function(err, result) {
        ...
            con.query(sql, values, function(err, result) {
                events[date][currRecord.name] = {
                    ...
                };
                // put it here
                console.log(events);
                res.render('pages/admin', {
                  events: events
                });
            });
        }
    });
});

Or use the Promise version of mysql

const mysql = require('mysql2/promise');

// make this async
app.get('/admin', async function(req, res) {
  try {
    ...
    // might want to move this elsewhere inside async function
    const con = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
    const result1 = await con.query(sql);
    // run your logic preferably inside map
    // result2 is an array of Promises
    const result2 = result1.map((result, index) => {
      var currRecord = result[index];
      var date =
          currRecord.month + ' ' + currRecord.day + ', ' + currRecord.year;
      var other = 'N/A';
      if (currRecord.other !== null) {
        console.log('here');
        other = currRecord.other;
      }
      if (typeof events[date] === 'undefined') {
        events[date] = {};
      }
      //get all the student attendees of this event
      var studentAttendees = {};
      var sql =
          'SELECT * FROM students INNER JOIN students_has_events ON students.id = students_has_events.Students_id ' +
          'WHERE students_has_events.Events_id = ?';
      var values = [currRecord.id];
      return con.query(sql)
    })
    // result3 is an array of results. If your results is also an array, the structure might look lik [[student], [student]] etc.
    const result3 = await Promise.all(result2);
    // run your logic to get events
    console.log(events);
    res.render('pages/admin', {
        events: events
    }); 
  } catch (e) {
    // handle error
    console.error(e)
  }
});