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

JS Promises with MySQL to create org structure from flat table

user3493
1#
user3493 Published in May 22, 2018, 4:36 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO