2

I use mongo to store the messages of the users.
This is my model for the collection.

_id            // mongo id of the message
subject        // subject of the message
msg            // message
recipients     // array of users id
sender         // user id
inReplyTo      // id of the message that i'm replying
references     // array of all the ids of the conversation
sended         // date of dispatch

I want to make a list with 30 messages per page that I received. If there is a conversation, in the list I wanna see only the last message of the conversation.
I use mongoose and nodejs.

These are 2 messages of one conversation.

[
       // the first message of the conversation
       {
            "_id" : ObjectId("5b9a7218b83256001799a114"),
            "inReplyTo" : null,
            "references" : [ ],
            "recipients" : [ 1, 2, 3 ],
            "subject" : "Subject of the first message",
            "msg" : "Text of the first message",
            "sender" : 4,
            "sended" : ISODate("2018-09-13T16:20:08.997+02:00"),
        },
        // the reply to the first message
        {
            "_id" : ObjectId("5b9bc0d67d6acc001732a58a"),
            "inReplyTo" : ObjectId("5b9a7218b83256001799a114"),
            "references" : [
                ObjectId("5b9a7218b83256001799a114")
            ],
            "recipients" : [ 4 ],
            "subject" : "FDW: Subject of the first message",
            "msg" : "Text of the reply",
            "sender" : 1,
            "sended" : ISODate("2018-09-14T16:08:22.934+02:00"),
        }
]

How can I use aggregate?
Or I have to filter them after the query? And how?

Stefano
  • 293
  • 1
  • 4
  • 22
  • Better if you use Firebase instead – letsintegreat Sep 17 '18 at 14:53
  • I can't. I have to use this systems – Stefano Sep 17 '18 at 15:09
  • I don't think your message model is too good for this use case. Do you have any chance to add a `conversation` model that contains the participants? Then have each message reference to a conversation? This would make querying more straight forward and the documents more expressive. – Benno Sep 17 '18 at 15:45
  • I'm may try to construct a query for you case but need some more information: **1)** Is the `inReplyTo` field always `undefined` for the first message of each conversation and only for the first? **2)** Does only the first message of every conversation have the `references` field populated or each of them? – Benno Sep 17 '18 at 17:56
  • 1) Yes 2) The first message have the `references` field `undefined` and every message of the conversation has all the previous messages id (in references) – Stefano Sep 18 '18 at 07:30
  • Does your sample data represent two separate documents or the contents of some field inside another document (which would be missing in that case)? – dnickless Sep 18 '18 at 08:36
  • The sample data are 2 rows of the collection, so they represent 2 separate documents – Stefano Sep 18 '18 at 08:45

2 Answers2

0

Update 1: I updated my answer code to fit the example data you posted. This one searches out every message that was sent to or by the user, groups them by conversation and returns only the first (last) of each.

Message.aggregate([
    // Get the first message of each conversation concerning user
    { 
        $match: { 
            $and: [
                {  
                    $or: [
                        { recepient: USER_ID }, // User is in recepients 
                        { sender: USER_ID }     // or the sender
                    ],
                },
            ]
        }
    },
    // Add a conversation field to group them by conversation
    {
        $addFields: {
            conversation: {
                $cond: {
                    if: {
                        $references:  { $exists: false } 
                    },
                    then: '$_id',
                    else: { $arrayElemAt: ['$references', 0]}
                }
            }
        }
    },
    // Sort messages by 'sended' field (descending, most-recent first)
    { 
        $sort: {
            sended: -1
        }
    },

    // Group by conversation, collect only the most recent (first) message of each conversation 
    // { _id: conversation-id#1, message: { ...}},  { _id: conversation-id#2, message: { ...}}, ...
    { 
        $group: { 
            _id:  '$conversation',
            message: { $first: '$$ROOT' }
        }
    }
], function(err, recentMessages) {
    if(err) { /** Handle error.. */ }
});
Benno
  • 968
  • 6
  • 15
  • I want to make a list with 30 messages per page that I received. If there is a conversation, in the list I wanna see only the last message of the conversation. – Stefano Sep 19 '18 at 07:30
  • 1
    @Stefano I updated my answer in response to your clarification. Please have a look if this helps you out – Benno Sep 19 '18 at 09:35
  • What exactly does not work? Please try the aggregation steps one by one to find out which one does not work as expected. – Benno Sep 24 '18 at 08:38
  • Hi Benno - can you plesae check why doesint it work here? https://mongoplayground.net/p/gohg8zQ-wi- Thank you. – Ron Rofe May 25 '20 at 05:06
  • 1
    @RonRofe First, you are misspelling `recipient` in the query. Second, this query doesn't match your use case because you don't have the `references`field. Have a look at this example: https://mongoplayground.net/p/NuRBqTyPW4D Please open a new stackoverflow question where you describe what you are exactly trying to do if you have any further questions. – Benno May 26 '20 at 06:45
  • @BennoThanks. I'd appreciate your help here: https://stackoverflow.com/questions/61969645/mongoose-find-ignore-duplicate-values – Ron Rofe May 26 '20 at 07:09
0

Here is how you would do that:

db.collection.aggregate({
    $match: {
        "recipients": userId // keep only documents where my userId is in the list of recipients
    }
}, {
    $sort: {
        "sended": -1 // sort by "most recent first" - kindly note that it should be "sent" not "sended"
    }
}, {
    $limit: 1 // return no more than one document
})
dnickless
  • 10,733
  • 1
  • 19
  • 34