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?