CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
Rob
Rob

Reputation: 4987

Mongoose NodeJS - Find the last inserted record per user

I'm kind of a newbie when it comes to NoSQL and Mongoose. I've been searching a bit but couldn't really find what i'm looking for (it makes it even harder that I don't really know the answer).

Basically, I have a list of records that are being added by users. These records contain some data, and I would like to have a list of the latest inserted message of every user.

In SQL I would write;

SELECT * FROM records ORDER BY inserted_date GROUP BY user_id;

This would return a set like so;

[
    {user: 2, insert_date: 2015-08-12T15:15:00, message: "Hi"},
    {user: 3, insert_date: 2015-08-12T15:12:00, message: "Also hi"},
    {user: 5, insert_date: 2015-08-12T15:14:00, message: "Not hi"}
]

I have been looking around, found some answers but those didn't seem to work.

I've tried;

https://stackoverflow.com/a/7570091/1493455

And some other aggregate thingie like so;

Record.aggregate(
    {},
    { 
        $group: {
            _id: '$user_id',
            inserted: { $last: "$inserted" },
            message: { $last: "$message" }
        }
    },
    function(err, messages) {
        console.log(err, messages);
    }
);

which i've copied from another answer - but I don't really understand what its doing and what its even supposed to do.

I could ofcourse use a sort and limit query for every user, but this seems very inefficient.

I'd be pleased if someone can point me in the right direction :)

Upvotes: 0

Views: 1223

Answers (1)

Rob
Rob

Reputation: 4987

Yep, duplicate of: Mongoose - find last message from each user

I ended up fixing my code with;

Record.aggregate(
    [
        // Matching pipeline, similar to find
        {
            "$match": {}
        },
        // Sorting pipeline
        {
            "$sort": {
                "inserted": -1
            }
        },
        // Grouping pipeline
        {
            "$group": {
                "_id": "$user_id",
                "message": {
                    "$first": "$message"
                },
                "inserted": {
                    "$first": "$inserted"
                }
            }
        },
        // Project pipeline, similar to select
        {
            "$project": {
                "_id": 0,
                "user_id": "$_id",
                "message": 1,
                "inserted": 1
            }
        }
    ],
    function(err, messages) {
        console.log(err, messages);
    }
);

And updating to the latest mongodb (3.4) instead of the old (2.x) I was running (this gives errors because the aggregate is not supported yet).

Upvotes: 1

Related Questions