CODEWITHSUNDEEP
mongodb
Philip Claren
Philip Claren

Reputation: 2876

Get to bunch of post IDs the latest comment respectively in MongoDB in one query

I have an array of post Ids. Comments to posts are a collection on it's own and not embedded. Now I want to retrieve the latest comment to each post ID ideally in one query. post_id is a field in each comment document and a timestamp of course.

Post:

{
  _id: 123
}

Comment:

{
  _id: 567,
  post_id: 123,
  timestamp: 1498117182
}

Upvotes: 0

Views: 122

Answers (1)

p.streef
p.streef

Reputation: 3815

Since the comment has the post id it is a little simpler than my initial idea. first you get all the comments in your postId array. order the comments by timestamp (latest first) then group then by postId and use the first value for your fields

db.getCollection('comments').aggregate([
    {$match: {
        post_id: {$in : idArray}
        }},
    {$sort:{ timestamp : -1 }}, 
    {$group:{
        _id :  post_id,
        timestamp: { $first: "$timestamp" },
        comment_id: { $first: "$_id" }
        }}

]);

Upvotes: 1

Related Questions