Home JS Promises with MySQL to create org structure from flat table
Reply: 1

JS Promises with MySQL to create org structure from flat table

Stangn99
1#
Stangn99 Published in 2018-01-13 04:48:58Z

I've been trying to get this work for a few days now and can't seem to get it to work.

I have a MySQL table which looks like this:

    empName               empID             empManagerID
    ------------------------------------------------------------
    Alex Manager          125354            987654
    Jane Doe              223535            125354
    Floyd Guzman          654354            125354

    Irma Fletcher         325150            212335
    Toni Copeland         456842            325150
    Benjamin Pratt        231543            325150
    Cassandra Gardner     656560            325150


    Fredrick Brogdon      783487            239873
    Deangelo Glandon      823402            783487
    Geraldine Brar        *230123           783487
    Jayna Lemmond         182739            783487
    Summer Wirtz          849734            783487
    Claretha Sheffer      983787            783487


    Tanna Boelter         345623            *230123
    Charlesetta Debolt    923476            230123
    Michael Gouin         456782            230123
    Dominick Piraino      956235            125354
    Shavonne Ovellette    108934            125354

I'd like to be able to find ALL of the names of people reporting to a specific managerID by providing the manager's employeeID as the starting point.

For example:

Select * from tableName where empManagerID = '125354';

This should return:

Jane Doe              223535            125354
Floyd Guzman          654354            125354
Dominick Piraino      956235            125354
Shavonne Ovellette    108934            125354

I then need to take each one of the empID's of the result and create multiple queries using the empID as the empManagerID to find all the people reporting (if any) to the people in the result.

This needs to happen until the final query is essentially empty.

With the help of my TA from a course I took I was able to get this far:

    function getEmployees(employerID) {
        const query = `
            SELECT empName, empID, empManagerID
            FROM myTable
            WHERE empManagerID = ${employerID}
        `;

      return new Promise((resolve, reject) => {
            con.query(query, (err, rows, fields) => {
                return resolve(rows);
        });
      });
    }

    getEmployees(125354)
        .then((employees) => {
            // push returned results to an array of some sort. 

            const empPromises = employees.map((e) =>  { 
                return  getEmployees(e.empID)           
            });
        return Promise.all(empPromises);
        })

        // fs.writeFile out to a reports.json file for future reference. 

The code above works, but doesn't seem to pull results recursively and I can't figure out why. The actual table has thousands of rows with many levels of employe => manager => sr manager relationships

If I attempt to chain on another .then() with another call to getEmployees it just returns a bunch of promises - not the results. If I console.log (rows) however, I can see the results.

I'm fairly new to JS and still learning...but I just cannot figure this one out.

Any help would be greatly appreciated.

Roamer-1888
2#
Roamer-1888 Reply to 2018-01-15 05:18:35Z

For recursion you need a function that calls itself (or somehow causes itself to be called).

And at each iteration, you need :

  • to find the given employee's subordinates (some or none)
  • to return a list of the subordinates plus each subordinate's subordinates

Due to recursion, the same is performed at the next level resulting, eventually, in the original caller eventually being delivered a list of all subordinates, subordinates' subordinates, subordinates' subordinates' subordinates, and so on.

// modified data, expressed as a javascript object-literal
var allEmployees = [
    { 'empName':'Alex Manager',       'empID':'125354', 'empManagerID':'987654' },
    { 'empName':'Jane Doe',           'empID':'223535', 'empManagerID':'125354' },
    { 'empName':'Floyd Guzman',       'empID':'654354', 'empManagerID':'125354' },
    { 'empName':'Irma Fletcher',      'empID':'325150', 'empManagerID':'125354' },
    { 'empName':'Toni Copeland',      'empID':'456842', 'empManagerID':'325150' },
    { 'empName':'Benjamin Pratt',     'empID':'231543', 'empManagerID':'325150' },
    { 'empName':'Cassandra Gardner',  'empID':'656560', 'empManagerID':'325150' },
    { 'empName':'Fredrick Brogdon',   'empID':'783487', 'empManagerID':'239873' },
    { 'empName':'Deangelo Glandon',   'empID':'823402', 'empManagerID':'783487' },
    { 'empName':'Geraldine Brar',     'empID':'230123', 'empManagerID':'231543' },
    { 'empName':'Jayna Lemmond',      'empID':'182739', 'empManagerID':'783487' },
    { 'empName':'Summer Wirtz',       'empID':'849734', 'empManagerID':'783487' },
    { 'empName':'Claretha Sheffer',   'empID':'983787', 'empManagerID':'783487' },
    { 'empName':'Tanna Boelter',      'empID':'345623', 'empManagerID':'230123' },
    { 'empName':'Charlesetta Debolt', 'empID':'923476', 'empManagerID':'230123' },
    { 'empName':'Michael Gouin',      'empID':'456782', 'empManagerID':'230123' },
    { 'empName':'Dominick Piraino',   'empID':'956235', 'empManagerID':'125354' },
    { 'empName':'Shavonne Ovellette', 'empID':'108934', 'empManagerID':'125354' }
];

// simple filter in place of SQL
function getEmployeesOf(employerID) {
    return Promise.resolve(allEmployees.filter(emp => emp.empManagerID === employerID));
}

// recursive function (it calls itself)
function drillDown(employerID) {
    return getEmployeesOf(employerID).then(employees => {
        let promises = employees.map(emp => drillDown(emp.empID));
        return Promise.all(promises).then(emps => employees.concat(...emps)); // spreading here avoids the need to flatmap later.
    });
}

drillDown('125354').then(results => {
    console.log('Results: ', results);
});

DEMO with the modified data above, this should give a list of 12 employees.

For comparison, this is what the two functions would look like if everything was synchronous:

function getEmployeesOf(employerID) {
    return allEmployees.filter(emp => emp.empManagerID === employerID);
}

function drillDown(employerID) {
    let employees = getEmployeesOf(employerID);
    return employees.concat(...employees.map(emp => drillDown(emp.empID)));
}

DEMO with the same modified data

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.431802 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO