Mongoose - How to group by and populate?

Question

I use MongoDB and Mongoose as my ODM and I'm trying to make a query using populate and group by in the same statement.

Here is my simple documents models :

var userSchema = new Schema({
    username: String
});

var messageSchema = new Schema({
    from: { type: Schema.ObjectId, ref: 'User' },
    to: { type: Schema.ObjectId, ref: 'User' },
    message: String,
    date: { type: Date, default: Date.now }
});

I'm just trying to get every messages for one user, group by each users he talks with. I tried like this :

this.find({ 'to': user })
    .sort({ 'date': 1 })
    .group('from')
    .populate(['from', 'to'])
    .exec(callback);

But, unfortunately, my model doesn't have group method. Do you have any solution, to get this working ?

Thank you.

Answer 1

Example using $lookup populate, lookup populates as an array, hence the $unwind.

Message.aggregate(
    [
        { "$match": { "to": user } },
        { "$sort": { "date": 1 } },
        { "$group": { 
            "_id": "from",
            "to": { "$first": "$to" },
            "message": { "$first": "$message" },
            "date": { "$first": "$date" },
            "origId": { "$first": "$_id" }
        }},
        { "$lookup": {
             "from": "users",
             "localField": "from",
             "foreignField": "_id",
             "as": "from"
        }},
        { "$lookup": {
             "from": "users",
             "localField": "to",
             "foreignField": "_id",
             "as": "to"
        }},
        { "$unwind": { "path" : "$from" } },
        { "$unwind": { "path" : "$to" } }
    ],
    function(err,results) {
        if (err) throw err;
        return results;
    }
)

Answer 2

The better option to use here is .aggregate(), which is a native code implementation unlike the .group() method of MongoDB which uses the JavaScript engine to process results.

Methods like .populate() are not directly supported though, and this is by design since the aggregation pipeline and other methods do not strictly return a response that is based on the current model's schema. Since it would be wrong to "assume" that is what you are doing, it is just a raw object response.

There is however nothing stopping you from "casting" the response as mongoose documents and then calling the model form of .populate() with the required paths:

Message.aggregate(
    [
        { "$match": { "to": user } },
        { "$sort": { "date": 1 } },
        { "$group": { 
            "_id": "from",
            "to": { "$first": "$to" },
            "message": { "$first": "$message" },
            "date": { "$first": "$date" },
            "origId": { "$first": "$_id" }
        }}
    ],
    function(err,results) {
        if (err) throw err;
        results = result.map(function(doc) { 
            doc.from = doc._id
            doc._id = doc.origId;
            delete doc.origId;
            return new Message( doc ) 
        });
        User.populate( results, { "path": "from to" }, function(err,results) {
            if (err) throw err;
            console.log( JSON.stringify( results, undefined, 4 ) );
        });
    }
)

Of course, that just really returns the $first message from each "from" as is implied by the operator.

Perhaps what you really mean by "group by" is actually to "sort":

Message.find({ "to": user })
    .sort({ "from": 1, "date": 1 })
    .populate("from to")
    .exec(function(err,messsages) {
        if (err) throw err;
        console.log( JSON.stringify( messages, undefined, 4 ) );
    });

As your context says "all messages" and not something that would otherwise be implied by a grouping operator such as with .aggregate() or the .group() collection method. So the messages a "grouped together" via sorting, rather than any particular grouping.

The latter sounds like what you are really asking, but if you actually intended real "grouping" then there is the aggregation example along with how to use .populate() with that.

Answer 3

This is how i grouped units by unit type

Before grouping

[
{
    "_id": "5f68d604d47d3517ac3f00a1",
    "active": true,
    "unitName": "3",
    "unitType": [
        {
            "_id": "5f5b0a20c546f803d36f43b2",
            "active": true,
            "facilities": [
                "5f5b0977c546f803d36f43b0",
                "5f5b096ac546f803d36f43ae"
            ],
            "typeName": "Deluxe Room",
            "numberOfBeds": 2,
            "rate": 15000,
            "__v": 0
        }
    ],
    "status": "CLEANING",
    "createdAt": "2020-09-21T16:34:12.189Z",
    "__v": 0
},
{
    "_id": "5f6adb612bf2c33614d9d28e",
    "active": true,
    "unitName": "1",
    "unitType": [
        {
            "_id": "5f5b0a20c546f803d36f43b2",
            "active": true,
            "facilities": [
                "5f5b0977c546f803d36f43b0",
                "5f5b096ac546f803d36f43ae"
            ],
            "typeName": "Deluxe Room",
            "numberOfBeds": 2,
            "rate": 15000,
            "__v": 0
        }
    ],
    "status": "READY",
    "createdAt": "2020-09-23T05:21:37.746Z",
    "__v": 0
}]

In aggregation

Unit.aggregate([
    {
        $match:{
            _id : {
                $nin : reservedUnits
            }
        }
    },
    {
        $lookup: {
            from: 'unittypes',
            localField: 'unitType',
            foreignField: '_id',
            as: 'unitType'
        }
    },
    {
        $project: {
            unitType: {
                createdAt: 0
            }
        }
    },
    {$group : {_id : "$unitType", units: { $push: "$$ROOT" }}},
    {
        $project: {
            _id : {
                facilities: 0
            },
            units: {
                unitType: 0
            }
        }
    },
]);

Result

[
{
    "_id": [
        {
            "_id": "5f5b0a20c546f803d36f43b2",
            "active": true,
            "typeName": "Deluxe Room",
            "numberOfBeds": 2,
            "rate": 15000,
            "__v": 0
        }
    ],
    "units": [
        {
            "_id": "5f68d604d47d3517ac3f00a1",
            "active": true,
            "unitName": "3",
            "status": "CLEANING",
            "createdAt": "2020-09-21T16:34:12.189Z",
            "__v": 0
        },
        {
            "_id": "5f6adb612bf2c33614d9d28e",
            "active": true,
            "unitName": "1",
            "status": "READY",
            "createdAt": "2020-09-23T05:21:37.746Z",
            "__v": 0
        }
    ]
}]