reactjs - Mongodb aggregation help about advanced chat

Question

I tell you I need help with a query in MongoDB in which I need to bring data from the chats,

I have a collection of: Users Rooms Messages

It happens that in this aggregation I bring the last message of each chat (in a chat interface to continue with conversations)

Besides I bring information about the user of their respective chat and they are ordered by date "ASC"

What happens is that within the collection of rooms there is an attribute called "deleted_by" which is an arrangement in which a "push" is made every time a user of that conversation deletes the conversation and has: _id (user id) deleted_date (date the conversation was deleted)

What I need is to make the aggregation bring me the last message or not, depending on: If the user ever deleted the conversation and what is the last message that its date "createdAt" is greater than the date of deletion of the conversation "deleted_date" Ideally, you should not be able to bring the chat if there are no messages after the delete date

Something to emphasize is that this deletion date is updated for the user each time they delete the conversation again

If this is not the correct way to do it, I would like someone to guide me on how I should handle the issue of eliminating a conversation (But that it is only eliminated for the user who did it, not for the other) and apart from starting the conversation and the other user continues to have all the messages, but the one who deleted it only has the conversation since the start again

    data_chats = rooms_db.aggregate([
        {
          '$match': {
            'usuarios': ObjectId(usuario['_id']),
          }
        },
        {
          '$lookup': {
            'from': 'mensajes',
            'localField':'mensajes',
            'foreignField': '_id',
            'as': 'mensaje'
          }
        },
        {
          '$lookup': {
            'from': 'usuarios',
            'localField':'usuarios',
            'foreignField': '_id',
            'as': 'usuario'
          }
        },
        {"$unwind":"$usuario"},
        {"$unwind": "$usuario._id"},
        {"$match": {"usuario._id": {"$nin": [ObjectId(usuario['_id'])]}}},
        {
          '$project': {
            'mensaje': {
              '$cond': [
                {'$eq': [ "$deleted_by._id", ObjectId(usuario['_id'])]},
                #{'$slice': ['$mensaje', -1]},
                {'$filter': {
                   'input': "$deleted_by",
                   'as': "deleted_convers",
                   'cond': { '$gte': [ "$$deleted_convers.delete_date", '$mensaje.createdAt'] }
                  }
                },
                {'$filter': {
                   'input': "$deleted_by",
                   'as': "deleted_convers",
                   'cond': { '$gte': [ "$$deleted_convers.delete_date", '$mensaje.createdAt'] }
                  }
                },
              ]
            },#{'$slice': ['$mensaje', -1]},
            'usuarios': {
              'id': '$usuario._id',
              'nombre_usuario': '$usuario.nombre_usuario',
              'foto_url': '$usuario.foto_url'
            },
            'deleted_by': {
              '$filter': {
                   'input': "$deleted_by",
                   'as': "deleted_convers",
                   'cond': { '$eq': [ "$$deleted_convers._id", ObjectId(usuario['_id'])] }
              }
            }
          }
        },
        {
          '$sort': {'mensaje.createdAt': -1}
        },
      ])

message document: [enter image description here][1] [1]: https://i.stack.imgur.com/BiMLe.png

room document: [enter image description here][2] [2]: https://i.stack.imgur.com/LHvNi.png

I need to get this:

{
   "_id":"ObjectId(""5fff8086d1f75d0313fd20d5"")",
   "usuarios":[
      {
         "id":"ObjectId(""5fff7521d1f75d0313fd20d4"")",
         "nombre_usuario":"dagoberticohd",
         "foto_url":"http://etc..."
      },
      {
         "id":"ObjectId(""5fff7521d1f75d0313fd20d4"")",
         "nombre_usuario":"dagoberticohd",
         "foto_url":"http://etc..com"
      }
   ],
   "mensaje":[

   ],
   "deleted_by":[
      {
         "_id":"ObjectId(""5ffb8107e3d065e99e0949bd"")",
         "delete_date":"2021-01-25T23:21:42.580627"
      }
   ]
}

I get this:

{
   "_id":"ObjectId(""5fff8086d1f75d0313fd20d5"")",
   "usuarios":[
      {
         "id":"ObjectId(""5fff7521d1f75d0313fd20d4"")",
         "nombre_usuario":"dagoberticohd",
         "foto_url":"http://etc..."
      },
      {
         "id":"ObjectId(""5fff7521d1f75d0313fd20d4"")",
         "nombre_usuario":"dagoberticohd",
         "foto_url":"http://etc..com"
      }
   ],
   "mensaje":[
      {
         "_id":"ObjectId(""600cd16b3de98712caa8b7dc"")",
         "text":"Xx",
         "createdAt":"2021-01-24T01:46:19.377185",
         "autor":"ObjectId(""5ffb8107e3d065e99e0949bd"")",
         "room":"ObjectId(""5fff8086d1f75d0313fd20d5"")",
         "received":false
      }
   ],
   "deleted_by":[
      {
         "_id":"ObjectId(""5ffb8107e3d065e99e0949bd"")",
         "delete_date":"2021-01-25T23:21:42.580627"
      }
   ]
}

question from:https://stackoverflow.com/questions/65877559/mongodb-aggregation-help-about-advanced-chat

Answer 1

Stages:

1. Get the document where the use present. ( in this example the user with _id === 2)
2. "JOIN" the messages documents.
3. "JOIN" the users documents.
4. Spread the messages.
5. Sort by the messages.createdAt.
6. Limit the result at this stage to 1, because I just need the last one.
7. Add the delete_by JUST if the user has done so.
8. If the "$delete_by.delte_date" > "$messages.createdAt" return null otherwise the list of messages.

db.room.aggregate([
  {
    $match: {
      usuarios: 2
    }
  },
  {
    "$lookup": {
      "from": "message",
      "localField": "messages",
      "foreignField": "_id",
      "as": "messages"
    }
  },
  {
    "$lookup": {
      "from": "user",
      "localField": "usuarios",
      "foreignField": "_id",
      "as": "usuarios"
    }
  },
  {
    "$unwind": "$messages"
  },
  {
    "$sort": {
      "messages.createdAt": -1
    }
  },
  {
    "$limit": 1
  },
  {
    "$addFields": {
      "delete_by": {
        "$filter": {
          "input": "$delete_by",
          "as": "delete_by",
          "cond": {
            "$eq": [
              "$$delete_by._id",
              2
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "messages": {
        "$cond": {
          if: {
            "$gte": [
              "$delete_by.delte_date",
              "$messages.createdAt"
            ]
          },
          then: null,
          else: "$messages"
        }
      }
    }
  }
])

PlayGround