SkyCoder
4 months ago

Ideal chat migration

Posted 4 months ago by SkyCoder

I was wondering is there an ideal migration structure for private chats between users.

I used something like:

user_id - as sender
receiver_id - as receiver
conversation_id - as id made by random hash with both user_id and receiver_id, created first time as someone sends a message
message - as message

Now to get list of messages I used that conversation_id as:

SELECT id, MAX(created_at) AS created_at, message, user_id, receiver_id, conversation_id FROM messages WHERE user_id = my_id OR receiver_id = my_id GROUP BY conversation_id

This outputs only last messages per conversation. Then to get messages I get it by conversation_id...

Is there a better way to do this?

Please sign in or create an account to participate in this conversation.