I have 2 collections.

Collection Name: chats
Chat {
     _id : Object(Id)
     mssg : string
     room : Object Ref (room _id)
     creationDate: DateTimeStamp
}

Collection Name: rooms
Room {
     _id : Object(Id)
     chatMssgs: [Object Ref (Chat_id_1), Object Ref (Chat_id_2), Object Ref (Chat_id_x)]
     roomName: string
     creationDate: DateTimeStamp
}

Im trying to use mongoose aggregation pipeline to sort the rooms in order of latest chat message. To do that, I'm doing the following:

1. Sort by latest chat mssg (using creation date)
2. Use Lookup to get the referenced room for the chat mssg
3. unwind the room array that results
4. use replaceRoot to bring the room array results as the main objects in the array.
5. Return unique list of rooms without losing ordering of the latest chat mssg dates.

So far the following code works up to point 4.

await Chat.aggregate([
     {$sort: {creationDate: -1}},
     {$lookup: {
                from: "rooms",
                localField: "room",
                foreignField: "_id",
                as: "chat_room"
               }
      },
      {$unwind: "$chat_room"},
      {$replaceRoot: {newRoot: "$chat_room"}},
])

This is where I am lost. If I keep the current aggregation code as is, it works, but my room ids have duplicates. This is because a room can have multiple chat messages. If I use $group after replaceRoot, my ordering goes away and the order of latest chat message is lost.

Ideally, I am trying to sort the rooms by the last chat messages. Im using aggregation because I also need to perform some counts on the rooms afterwords using addField and Im using pagination for this query so offset and limit are needed to keep the query relatively fast.

What is the best way to return rooms with the latest chat messages in descending order? So room 1 will have the latest mssg, room 2, ... etc till room x shows the earliest mssg posted.

Update: After trying the suggested solution in the first reply, I get:

[
  {
    "_id": 3,
    "creationDate": ISODate("2014-01-03T08:00:00Z"),
    "mssg": "ghi",
    "room": 2
  },
  {
    "_id": 2,
    "creationDate": ISODate("2014-01-02T08:00:00Z"),
    "mssg": "def",
    "room": 1
  }
]

This was the issue I ran into with my approach as well. How do I get the output to look like this?

[
      {
        "_id": 2,
         "roomName": "room 2"
        "creationDate": ISODate("2014-01-01T08:00:00Z"),
        "chatMssgs": [3 ],
      },
      {
        "_id": 1,
        "roomName": "room 1"
        "creationDate": ISODate("2014-01-01T08:00:00Z"),
        "chatMssgs": [1,2 ],
      }
    ]

CodePudding user response：

Find max date and then filter it.

db.chats.aggregate([
  {
    "$match": {
      _id: {
        "$in": [
          1,
          2,
          3
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$room",
      "latestMsgDate": {
        "$max": "$creationDate"
      },
      "Msg": {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$set": {
      "Msg": {
        "$filter": {
          "input": "$Msg",
          "as": "m",
          "cond": {
            "$eq": [
              "$$m.creationDate",
              "$latestMsgDate"
            ]
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$first": "$Msg"
      }
    }
  },
  {
    "$sort": {
      creationDate: -1
    }
  }
])

mongoplayground

CodePudding user response：

Ok. Thanks for YuTing's answer, I was able to get what I was looking for (See Update section in question). For anyone that's interested:

   db.chats.aggregate([
      {$lookup: {
          from: "rooms",
          localField: "room",
          foreignField: "_id",
          as: "chat_room",
        },
      },
    { $group: {
       _id: "$chat_room",
       latestMsgDate: { $max: "$creationDate" },
       },
     },
    {$sort: { latestMsgDate: -1 }},
{$unwind: "$_id"},
{$replaceRoot: { newRoot: "$_id" }}
    ])

You can test in mongoplayground. Output is:

[
  {
    "_id": 2,
    "chatMssgs": [
      3
    ],
    "creationDate": ISODate("2014-01-01T08:00:00Z"),
    "roomName": "room 2"
  },
  {
    "_id": 1,
    "chatMssgs": [
      1,
      2
    ],
    "creationDate": ISODate("2014-01-01T08:00:00Z"),
    "roomName": "room 1"
  }
]