Get latest DMs from chat collection

Working with Data
1

Hello,
I am creating a project that requires a chat app and I used the following schema

{
    _id:
    sender:
    receiver:
    message:
    createdAt:
}

Based on this schema, what would be the best way to get a list of latest conversations/chats. For more context, I want to have a sidebar that shows the list of chats like( (User_a (message) 2m ago). Sorry if I’m not framing the question well.

2

Hello @Oluwapelumi_Adegoke
Welcome to the community forum!!

By looking at the schema design, I would recommend you to use aggregation and compare the createdAt time stamp value with the current time and sort the data, such that the list with the most recent chats will appear at the top.

I tried to load a sample data and tried the following query which worked for my dataset:

db.test.aggregate([{$addFields: {difference: {$subtract: [“$$NOW”, “$createdAt”]}}}, {$sort: {“difference”:-1}}])

where you can add other validation as well to check is the receiver is same for all the messages.

However, the date operators are available post MongoDB version 5.0 and would recommend to upgrade is you are using a lower version. MongoDB 5.0
Also, if the query does not seem to wok for you, could you provide a sample data for the above mentioned scheme so we could try to find the resultant for it.

Thanks
Aasawari

3

Thank you. I was able to query all previous messages. However, the problem comes from sorting the latest message in a conversation. I can get all messages received or sent by user A but I don’t know how to sort it to give me the last message sent between User A and (User B, User C, User D) in order of time

4

Hello @Oluwapelumi_Adegoke

Regarding the above sentence, could you please confirm that User A being the receiver while others ( User B , User C, User D) being the sender.
Based on the above statement, the following query will sort the data based on the time difference calculated between the current time and createAt time stamp value.

const aggregation = [
{ 
    $match: {
       receiver: "Robert Huff"
    }
},
{
  $addFields: {
    duration: {$divide: [{$subtract: ["$$NOW", "$createdAt"]}, 360000]
    }
  }
},
{$project: {message: 1, duration: 1, _id: 0}}
                  ];
db.test.aggregate(aggregation);

Please let me know if you have any questions with the above query.

Also, if you could help me with a sample data which would help me understand the case in better way.

Thanks
Aasawari

5

Thanks for your time. User A is not just the receiver, it could also be that User A sent a message. If User A sends user B a message then User B should be at the top of the query result. If User A then receives a message from user C, the query results should return User C, User B.

I understand that I may not be communicating the problem well. The query is for a direct messages list. By sample data do you mean the message schema, example message documents or desired output? Sorry I’m still new to asking questions so thanks for your patience.

6

Hello @Oluwapelumi_Adegoke

Apologies if the response has not been clear from our end. Let’s take a step back to understand more precisely.

Could you help me with a few details:

  1. Some sample documents about messages with User A being the sender and receiver.
  2. The expected output for both the scenarios.
  3. How does the output changes if a new message arrives?
  4. From our previous discussion, I understand you are trying to sort the messages in reverse chronological order. Please help me to understand, why this cannot be achieved using sorting the result by createdAt field. Is there a special requirement.

Thanks
Aasawari

1 Like
7

I apologize for the delay.

1.Sample Documents: Sender A is

    {
        "sender": "senderA",
        "receiver": "senderB",
        "message": "Why would you say that?",
        "createdAt": "2022-03-13T03:13:38.740Z"
    }
    {
        "sender": "senderC",
        "receiver": "senderA",
        "message": "Don't know I was just testing something",
        "createdAt": "2022-03-13T03:14:51.502Z"
    },
    {
        "sender": "senderB",
        "receiver": "senderA",
        "message": "It's cool",
        "createdAt": "2022-03-13T04:08:11.178Z"
    },

After message 1: Sender A’s message list will have the message they sent to Sender B at the top of the list
After message 2: Sender A’s message list will have the message they received from Sender C at the top
After message 3: Sender A’s message list will have the message they recived from Sender B at the top.

The special requirement is that the result should also include messages were the Sender A received message.

I again apologize for the late response

8

Hi @Oluwapelumi_Adegoke

Thank you for sharing the sample document.

To make the process of helping you efficiently, could you please help me in understanding, why the query below does not help?

Also, if you could share your own aggregation query and an output that you have been expecting.

Thanks
Aasawari

9

If “Robert Huff” sent a message to John Doe, this query would not return the message sent at the top of the query. I’d use the aggregation you provided if I only wanted the received messages

10

Hi @Oluwapelumi_Adegoke

The following

db.test.aggregate([
    { 
        $match: {
                      $or: [
                                {receiver: "Robert Huff"},
                                {sender: "Robert Huff"}
                       ]
        }
    },
    {
      $addFields: {
        duration: {
            $divide: 
            [{
                $subtract: ["$$NOW", "$createdAt"]}, 36000]
        }
      }
    },
    {$sort: {duration: -1}},
    {
        $project: {
            message: 1, duration: 1, _id: 0}
    }
                      ]);

This query comprise of three parts:
$match with filter all the documents where sender and receiver are both “Robert Huff”
$addFields will calculate the the duration based on the current time and createdAt timestamp value.
$sort this will sort the most recent messages.
$project will display only the messages and the duration calculated from the pipeline.

This query is for only a sample data based on the above described schema.

Please let us know if this aggregation query helps with the requirement.

Thanks
Aasawari

11

So it gives all the messages corresponding to Robert Huff. However, it should give only the latest message for each interaction. So if Robert Huff has correspondence with John Doe and Jane Doe, it should only return the latest message between (Robert Huff and Jane Doe) and (Robert Huff and John Doe).

12

I am also stuck at that. For single user it is easy but for all it is bit complicated. i dont know why that lady from mongo teams left unanswered you

13

Hi , have you resolved this issue? if you did then could you pls share the query .