Joining in NoSQL to get chat messages for a user



  • In my chat app i have private / group chats and I need to get messages for a user.

    Let me first explain the whole system.

    Messages:

    // group message
    {
      id: 1,
      text: 'hello',
      chat_id: 'AjK954_e5iP',
      created_at: 137615...
    },
    

    // private message
    {
    id: 2,
    text: ':)',
    chat_id: 'Bho9Sr_f51Rz',
    created_at: 137622...
    }

    Chats:

    {
      id: 'AjK954_e5iP',
      type: 'group',
      title: 'My Group yay'
    },
    {
      id: 'Bho9Sr_f51Rz',
      type: 'private',
      participants: ['A', 'B']
    }
    

    Members:
    When we join a group / private chat a new record is created in a table called members.

    // group chat member
    {
      user_id: 'A',
      chat_id: 'AjK954_e5iP',
      type: 'group'
    }
    

    // private chat member
    {
    user_id: 'A',
    chat_id: 'Bho9Sr_f51Rz',
    type: 'private'
    }

    The Problem

    to get the messages i need to perform a join on members and messages but it's not possible in NoSQL. i could solve the problem for private chats but for group chats an inner join is required.

    SQL way:

    SELECT messages.* from `members`
        INNER JOIN `messages`.`chat_id` = `members`.`chat_id`
             WHERE `members`.`user_id` = 'A'
    

    Bad solution

    If there are thousands of chat_ids, doing a where in is slow.

    // Pseudocode
    

    chat_ids = Members.where('user_id', 'A').get(['chat_id']);
    chat_ids = chat_ids.map(item => item.chat_id);

    messages = Messages.whereIn('chat_id', chat_ids).orderBy('created_at').limit(20).get();


  • QA Engineer

    I'm a MongoDB noob, but does this do what you want?

    db.members.aggregate([
      {
        "$match": {
          user_id: "A"
        }
      },
      {
        "$lookup": {
          "from": "messages",
          "localField": "chat_id",
          "foreignField": "chat_id",
          "as": "thechats"
        }
      },
      {
        "$unwind": "$thechats"
      },
      {
        "$replaceWith": "$thechats"
      },
      {
        "$sort": {
          created_at: -1
        }
      },
      {
        "$limit": 20
      }
    ])
    

    Try it at https://mongoplayground.net/p/u8854tctPt6 .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2