Group by and join-like behavior with Mongoose

Question

I'm struggling with Mongoose/NodeJS in order to get the data I want. Let me explain the scenario:

I have a set of loads, a set of carriers and a set of customers. These loads may be carried by 1 or more carriers, and I would like to list the loads by carrier. Each customer have their carriers, that can be shared with other customers. I have 3 collections (simplified for sake of comprehension): the first one is users, and is used also for authentication. When the customer logs in the system, he knows his own AccountID:

{
        "Name" : "Customer",
        "AccountID" : ObjectId("52a704ff4ae2d0f00a00000c"),
        "Role" : "Customer"
}
{
        "Name" : "Carrier 1",
        "AccountID" : ObjectId("52a7057d4ae2d0f00a00000e"),
        "Role" : "Carrier"
}
{
        "Name" : "Carrier 2,
        "AccountID" : ObjectId("52a705e54ae2d0f00a000010"),
        "Role" : "Carrier"
}

The second one is customercarrier, that says which carrier belongs to which customer:

{
    "OwnerID" : "52a704ff4ae2d0f00a00000c", 
    "CarrierID" : "52a7057d4ae2d0f00a00000e", 
}
{
    "OwnerID" : "52a704ff4ae2d0f00a00000c", 
    "CarrierID" : "52a705e54ae2d0f00a000010", 
}

The third one is the set of loads. Each load can be carried by one or more carriers, and their IDs are assigned on Instances.Assignees.AssigneeID :

{
    "LoadNumber" : 1,
    "Instances" : [
        {
            "_id" : ObjectId("52bda0144ce315980d000015"),
            "OwnerID" : "52a704ff4ae2d0f00a00000c",
            "OwnerName" : "Customer 1",
            "Assignees" : [
                {
                    "AssigneeID" : "52a7057d4ae2d0f00a00000e",
                    "AssigneeName" : "Carrier C1",
                    "_id" : ObjectId("52bd9ffc4ce315980d00000d"),
                }
            ]
        }
    ]
}
{
    "LoadNumber" : 2,
    "Instances" : [
        {
            "_id" : ObjectId("52bd9ffc4ce315980d00000f"),
            "OwnerID" : "52a704ff4ae2d0f00a00000c",
            "OwnerName" : "Customer 1",
            "Assignees" : [
                {
                    "AssigneeID" : "52a705e54ae2d0f00a000010",
                    "AssigneeName" : "Carrier C2",
                    "_id" : ObjectId("52bd9ffc4ce315980d000010"),
                }
            ]
        }
    ]
}
{
    "LoadNumber" : 3,
    "Instances" : [
        {
            "_id" : ObjectId("52bda02c4ce315980d00001b"),
            "OwnerID" : "52a704ff4ae2d0f00a00000c",
            "OwnerName" : "Customer 1",
            "IncomingStatus" : null,
            "Assignees" : [
                {
                    "AssigneeID" : "52a7057d4ae2d0f00a00000e",
                    "AssigneeName" : "Carrier C1",
                    "_id" : ObjectId("52bda02c4ce315980d00001c"),
                },
                {
                    "AssigneeID" : "52a705e54ae2d0f00a000010",
                    "AssigneeName" : "Carrier C2",
                    "_id" : ObjectId("52bda02c4ce315980d00001f"),
                }
            ]
        }
    ]
}

I would like to list the loads by carriers, like this:

Carrier C1:
   Load 1
   Load 3
Carrier C2:
   Load 2
   Load 3

I tried with the following code, but I'm struggling with callbacks. I tried to put the callback after the outermost loop, but in this condition, it returns nothing. After the innermost, it returns after the first loop iteration:

this.model('customercarrier').find({ "OwnerID" : accountID }, {}, {}, function(err, rst) {
    if (!err) {
        if(rst.length != 0){
            var rstret = [];
            for (var i = 0; i < rst.length; i++) {
                var searchObj = { "Instances.Assignees" : { $elemMatch : { AssigneeID : rst[i].CarrierID } } } ;
                this.model('load').find(searchObj, { }, { }, function(err, results) {
                    if (!err) {
                        for (var x = 0; x < results.length; x++) {
                            var myInstance = _.find( results[x].Instances, function( el ) {
                                return el.OwnerID === accountID;
                            } );
                            rstret.push(results);
                        }

                    } else {
                        cb(err,results);
                    }

                });
            }
            cb(err,rstret);
        }
        else{
            cb(err,[]);
        }
    }
    else{
        console.log("Error while retrieving carriers");
    }
});

I know this is the expected behavior for an asynchronous function with callback, but I need that behavior that I wrote before, but I couldn't figure out. Is this possible with Mongoose in NodeJS?

Thank you so much! Any idea is greatly appreciated!

just wondering if you have tried the [mongodb/mongoose aggregation pipeline](http://mongoosejs.com/docs/api.html#aggregate-js)? not sure it will be of use since i haven't fully comprehended your question yet. ...just an instinctual response :) — zamnuts, Dec 30 '13 at 02:54
You probably already know this, but [mongodb sucks at joins](http://stackoverflow.com/questions/4067197/mongodb-and-joins). Personally I would rewrite the schema to have [embedded documents](http://stackoverflow.com/questions/6722080/mongoose-joining-data) — verybadalloc, Dec 30 '13 at 13:23
Also, where exactly are you passing your parameter `cb`? Can you add some logs for the error case, to make sure that is not the root of the problem? — verybadalloc, Dec 30 '13 at 13:27

Group by and join-like behavior with Mongoose

0 Answers0