[Fixed] Mongoose how to combine find, sort, $and

Issue

I am writing API for chat using socket io. I have the following data structure in the database. When user A sends a message to user B, data saved by the following example { "from": "A", "to": "B" }. When user B responds the structure is the following { "from": "B", "to": "A" }.

{
    "_id" : ObjectId("606ea000c9ef8e1501d610d1"),
    "message" : "I am fine!",
    "from" : "User1",
    "to" : "Plurl",
    "time" : ISODate("2021-04-08T06:17:36.757Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("606e9ff8c9ef8e1501d610d0"),
    "message" : "how are u?",
    "from" : "Plurl",
    "to" : "User1",
    "time" : ISODate("2021-04-08T06:17:28.252Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("606e9ff8c9ef8e1505d610d0"),
    "message" : "Hi?",
    "from" : "User4",
    "to" : "User3",
    "time" : ISODate("2021-04-08T06:17:28.252Z"),
    "__v" : 0
}

I need somehow fetch data from DB, for user A with user B, and sort this data by time. I am trying to do it in this way. And there could be a case when there are only messages from user A to B, ie if user B didn’t respond.

const messages = await Message.find( {
    $and: [
      { from: { $eq: req.query.user } },
      { to: { $eq: req.user.name } },
      { from: { $eq: req.user.name } },
      { to: { $eq: req.query.user } }
    ],
    sort:{
      time: -1
    }
  })

Solution

You can use $or operator to find the messages from ‘A’ to ‘B’ and from ‘B’ to ‘A’.

And sort method to get messages in descending order.

const messages = await Message
  .find(
    {
      $or: [
        { from: { $eq: req.query.user }, to: { $eq: req.user.name } },
        { from: { $eq: req.user.name }, to: { $eq: req.query.user } },
      ],
    },
  )
  .sort({time: 'descending'})

or you can use query option

const messages = await Message
  .find(
    {
      $or: [
        { from: { $eq: req.query.user }, to: { $eq: req.user.name } },
        { from: { $eq: req.user.name }, to: { $eq: req.query.user } },
      ],
    },
    null,
    { sort: { time: -1 } }, 
  )