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();