Recursive Raw Query - Recursive Promise

[Rui Brito]

I am using MySQL, and have two tables **Project** and **Dependencies** :

|    |   Project   |         |

|:--:|:-----------:|---------|

| PK | idProject   | INT     |

|    | projectname | VARCHAR |

|       |     Dependencies    |         |

|:-----:|:-------------------:|---------|

| PK/FK | idProject           | INT     |

| PK/FK | idProjectDependency | VARCHAR |

**Dependencies is a ***Many-To-Many*** relationship between **Project**

For example, we can have:

| idProject | idProjectDependency |

|:---------:|:-------------------:|

| 5         | 3                   |

| 3         | 2                   |

So if I want to know the Dependencies of Project 5, I should received 3 and 2.

For the life of me, I couldn't find a way to do this in sequelize, so I result to do multiple queries, the logic is:

```sql

SELECT idProject, idProjectDependency 

FROM Project JOIN Dependencies ON WP_idWorkPackage = idProject 

WHERE idProjectDependency = 5

```

And I keep doing this until I receive a empty array.

My code so far is:

```javascript

var getListDependencies = function (wpID, total) {

    // Creates the query

    var sqlQuery = createDependencySQLQuery(wpID);

    return sequelize.query(sqlQuery, {type: sequelize.QueryTypes.SELECT}).done(function (result) {

        if (result.length > 0) {

            for (var i = 0; i < result.length; i++) {

                var dep = result[i].idProjectDependency;

                total.push(result[i].idProjectDependency);

                total = total.concat(getListDependencies(dep, total));

                return total;

            }

        } else {

            return total;

        }

    })

}

```

I keep transporting the total, from one loop to loop, the code (even though is not pretty) works except  that it returns before everything is completed and prints undefined because of this.

This is where I call the above function:

```javascript

module.exports = {

    getWorkPackages: function (req) {

        var reqWorkPackage = 5; // for testing

        return Promise.resolve().then(function () {

            return getListDependencies(reqWorkPackage, []);

        }).then(function (list) {

            console.log("\n\nLIST", list) // for testing

            

            return {"list": something};

        }).catch(function (msg) {

            throw msg; // <- throw the message

        });

    }

}

```

I have been wrapping my head around this for 2 days, so I decided to come here and ask you guys, how can I make the promise wait for the recursive search to the database? 

[Mark Lester]

Hope this helps (and actually runs!)

var global_thing=[];
getChildren(originId,global_thing)
    .then(function(){
        // global_thing is now an array of dependent project ids.
    });


function getChildren(parentId,global_thing){
  return Dependencies.findWhere({idProject:parentId}))
    .then(function(children){
        return _.each(children,function(child){
            global_thing.push(child.get('idProjectDependency');
            return getChildren(child.get('idProjectDependency'),global_thing);
        });
    });
}

[Mick Hansen]

Take a look at Bluebird and helper methods like each, all and map.

--

Mick Hansen

@mhansendev
mhansen.io