-1

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();
6
  • Your tags make your question unclear currently. What database system does your data currently live in for where you're trying to achieve your goal? MySQL, MongoDB, and Cassandra are each different and unrelated database systems.
    – J.D.
    Jan 22 at 18:37
  • @J.D. well yes, I haven't chosen which database but if there's a solution i think works for most of them, Here's a simplified version of the problem. stackoverflow.com/questions/70816066/get-chats-list-mongodb
    – Jacqueline
    Jan 22 at 19:01
  • The implementation of the solution is going to vary from one database system to another, so you'd have to pick a database system first, then a solution to your problem can be provided. If you need to do complex joins and filtering, then typically a relational database management system is going to be a better choice than a NoSQL solution. So of the 3 tags you listed, MySQL would be the only applicable choice if you wanted to go that route. PostgreSQL is another free (if that's part of your criteria for choosing a system) RDBMS that's very powerful too.
    – J.D.
    Jan 22 at 19:06
  • @J.D. I'm a RDBMS person the project is easy that way, But can relational databases handle millions of messages every day? i could also go with 2 databases but updating multiple records in 2 database at the same time without transaction is another story.
    – Jacqueline
    Jan 22 at 19:12
  • 1
    @J.D. Thank you.
    – Jacqueline
    Jan 23 at 9:36

1 Answer 1

0

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 mongoplayground.net.

4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.