3

I have a "chat" mongoose Schema which has the following properties:

const schema = mongoose.Schema({
    ...
    recipient: {
        type: mongoose.Types.ObjectId,
        required: true,
        ref: 'User',
    },
    sender: {
        type: mongoose.Types.ObjectId,
        required: true,
        ref: 'User',
    },
    content: {
        type: String,
    },
    ...
}, {
    timestamps: true,
});

Generally, I want to fetch the last message of each coversation that a user has. Meaning that I need to provide a user id (that can be either stored in the sender or recipient field) and get back the last message (indicated by createdAt) the user had with each of the other users.

Example: Let's say I have the following documents:

[
  {
    recipient: "One",
    sender: "Two",
    createdAt: ISODate("2014-01-01T08:00:00Z"),

  },
  {
    recipient: "One",
    sender: "Three",
    createdAt: ISODate("2014-02-15T08:00:00Z")
  },
  {
    recipient: "Two",
    sender: "One",
    createdAt: ISODate("2014-02-16T12:05:10Z")
  }
]

Instering "One" as input - the desired result from Model.find(...) is:

[
  {
    recipient: "One",
    sender: "Three",
    createdAt: ISODate("2014-02-15T08:00:00Z")
  },
  {
    recipient: "Two",
    sender: "One",
    createdAt: ISODate("2014-02-16T12:05:10Z")
  }
]
Ron Rofe
  • 738
  • 1
  • 9
  • 25
  • Does this answer your question? [Get distinct records values](https://stackoverflow.com/questions/20655506/get-distinct-records-values) – SuleymanSah May 23 '20 at 12:25

3 Answers3

2

You can do this by aggregation as shown in below query

Working example - https://mongoplayground.net/p/wEi4Y6IZJ2v

db.collection.aggregate([
  {
    $sort: {
      recipient: 1,
      createdAt: 1
    }
  },
  {
    $group: {
      _id: "$recipient",
      createdAt: {
        $last: "$createdAt"
      }
    }
  },
  {
    $project: {
      _id: 0,
      recipient: "$_id",
      createdAt: "$createdAt"
    }
  }
])

If you have two fields to match, then you can use below query

Working Example - https://mongoplayground.net/p/Rk5MxuphLOT

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          sender: "One"
        },
        {
          recipient: "One"
        }
      ]
    }
  },
  {
    $addFields: {
      other: {
        $cond: {
          if: {
            $eq: [
              "$recipient",
              "One"
            ]
          },
          then: "$sender",
          else: "$recipient"
        }
      }
    }
  },
  {
    $sort: {
      createdAt: 1
    }
  },
  {
    $group: {
      _id: "$other",
      createdAt: {
        $last: "$createdAt"
      },
      recipient: {
        $last: "$recipient"
      },
      sender: {
        $last: "$sender"
      }
    }
  },
  {
    $project: {
      _id: 0,
      recipient: "$recipient",
      sender: "$sender",
      createdAt: "$createdAt"
    }
  }
])
Puneet Singh
  • 3,477
  • 1
  • 26
  • 39
  • https://mongoplayground.net/p/E0G67cotQvL Thank you. What if I want the same, but for two fields - I mean I want to search for a specific user and fetch the documents in which the user exists in the `sender` or `recipient` - but after find them, take only those of the "last". For being more clear - I have a collection of messages between people (private chat) - I want to fetch the last user's message on each conversation he had (whether he is the sender or the recipient) – Ron Rofe May 25 '20 at 04:05
  • @RonRofe Updated answer added a new example for the use case mentioned in the comment – Puneet Singh May 26 '20 at 08:20
2

Using the example data:

[
  {
    recipient: "One",
    sender: "Two",
    createdAt: ISODate("2014-01-01T08:00:00Z"),
    content: "Hi Mr. One! - Two"
  },
  {
    recipient: "One",
    sender: "Three",
    createdAt: ISODate("2014-02-15T08:00:00Z"),
    content: "Hello One! - Three"
  },
  {
    recipient: "Two",
    sender: "One",
    createdAt: ISODate("2014-02-16T12:05:10Z"),
    content: "Whats up, Two? - One"
  }
]

Have a look at the following aggregation: https://mongoplayground.net/p/DTSDWX3aLWe

It...

  • Uses $match to filter all messages by recipient or sender. Returns the ones matching the current user (One)
  • Adds a conversationWith field using $addFields that contains the recipient if it is a message to user One or the sender if it is a message sent by user One
  • Sorts the messages by date using $sort
  • Groups all the messages using $group by the new conversationWith field and returns the most recent message as firstMessage

The full aggregation pipeline:

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          $or: [
            {
              recipient: "One"
            },
            {
              sender: "One"
            }
          ],

        },

      ]
    }
  },
  {
    $addFields: {
      conversationWith: {
        $cond: {
          if: {
            $eq: [
              "$sender",
              "One"
            ]
          },
          then: "$recipient",
          else: "$sender"
        }
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $group: {
      _id: "$conversationWith",
      firstMessage: {
        $first: "$$ROOT"
      }
    }
  }
])

Using mongoplayground you can remove the aggregation steps one-by-one to see what each step does.

Try:

  • Only the $match step
  • $match + $addFields
  • $match + $addFields + $sort
  • [..]

for best understanding.

Benno
  • 968
  • 6
  • 15
0

if you want to rely on mongodb to filter out duplicates, its better to never even allow to have duplicates by creating a unique index.

since it seems your recipients are nested in a parent scheme, I would do the filtering of the duplicates in nodejs since its hard to untangle this in a mongodb query. If you need to use mongodb, use the distinct function or the aggregate pipeline and be inspired by this article

japrescott
  • 4,736
  • 3
  • 25
  • 37