MongoDB and NodeJS get related data from 3 collections

Question

i have a mongoDB query to get data with $group and $count. This data contains the _id from other documents collection. How can i get the other documents by its _id in NodeJS and MongoDB asyncrohnous?

 db.orders.aggregate([
                 {$match: { 'works.TechnicianId': {$in:['53465f9d519c94680327965d','5383577a994be8b9a9e3f01e']}, 
                           'works.Date': {$gte: ISODate("2013-05-21T06:40:20.299Z"), $lt: ISODate("2016-05-21T06:40:20.299Z")}}},
                 {$unwind: "$works" },
                 {$group: {_id: "$works.TechnicianId",total:{$sum:'$works.price'},ordersId: { $push: "$_id" }}},
                   ])

This is the result:

    {
    "result" : [ 
        {
            "_id" : "53465f9d519c94680327965d",
            "total" : 198,
            "ordersId" : [ 
                ObjectId("537b5ea4c61b1d1743f4341f"), 
                ObjectId("537b4633021d75bd36863f29")
            ]
        }, 
        {
            "_id" : "5383577a994be8b9a9e3f01e",
            "total" : 22,
            "ordersId" : [ 
                ObjectId("537b5ea4c61b1d1743f4341f"), 
                ObjectId("537b4633021d75bd36863f29")
            ]
        }
    ],
    "ok" : 1
}

Now i need to get from orders collection the documents with id from ordersId, and from other collection the documents with _id from the result _id field.

I try with this:

var collection = db.collection('orders');
    var result = [];

    collection.aggregate([
            {
                $match: {
                    'works.TechnicianId': {
                        $in: ids
                    },
                    'works.Date': {
                        $gte: new Date(startDate),
                        $lt: new Date(endDate)
                    }
                }
            },
            {
                $unwind: "$works"
            },
            {
                $group: {
                    _id: "$works.TechnicianId",
                    total: {
                        $sum: '$works.price'
                    },
                    orderId: {
                        $push: "$_id"
                    }
                }
            }
        ],
        function (e, docs) {
            if (e) {
                error(e);
            }
            var usersCollection = db.collection('users');

            _.each(docs, function (doc) {
                usersCollection.findOne({_id: new ObjectID(doc._id)}, function (e, doc) {
                    doc.tech = doc;
                });
                doc.orders = [];
                _.each(doc.orderId, function (queryOrder) {
                    collection.findOne({_id: new ObjectID(queryOrder._id)}, function (e, order) {
                        doc.orders.push(order);
                    });
                });
                success(docs);
            });
        });

But the success its called before all the _.eachs are finished..Any help, or idea?

Edit:

I try with Q promises, this is my code:

 var usersCollection = db.collection('users');
            var promises = [];

            _.each(reports, function (report) {
                var promise = usersCollection.findOne({_id: new ObjectID(report._id)}).then(
                    function (e, orderUserReported) {
                        if (e) {
                            error(e);
                        }
                        report.tech = orderUserReported;
                        _.each(orderUserReported.orderId, function (queryOrder) {
                            collection.findOne({_id: new ObjectID(queryOrder._id)}, function (e, order) {
                                report.orders.push(order);
                            });
                        });
                    });
                promises.push(promise);
            });
            Q.allSettled(promises).then(success(reports));

and the error:

/Users/colymore/virteu/aa/server/node_modules/mongodb/lib/mongodb/connection/base.js:245
        throw message;      
              ^

TypeError: Cannot call method 'then' of undefined

Andrei Beziazychnyi Andrei Beziazychnyi · Accepted Answer · 2014-05-27T14:39:25

Because of asynchronous execution you have to wait until results are returned. There are several options available:

async library https://github.com/caolan/async
promises https://github.com/kriskowal/q

Async is closer to your current code, you could use async.parallel https://github.com/caolan/async#parallel to wait untill you get data back

Update

Mongoose functions don't return Q promises, so you need to convert mongoose calls to promises by using something like Q.denodeify(User.findOne.bind(models.User))({ _id: userId}).then(...

For your case Q.denodeify(userCollection.findOne.bind(userCollection))({_id: new ObjectID(report._id)}).then(...

MongoDB and NodeJS get related data from 3 collections

2 Answers