I'm about to make a message system. Instead of making 2 tables like conversations and messages, I would just like to have messages. There would then be a lot of rows with the same user id's like this:
id to_id from_id message
1 1 2 text...
2 1 3 text...
3 2 1 text...
4 1 2 text...
5 1 2 text...
6 1 2 text...
The thing is, that I want to get one of each conversation. If id 1 writes to id 2, then thats 1 conversation, but if id 2 writes a reply to id 1, then it would STILL be only 1 conversation.
In the above example, there is only 2 conversations (id 1 to id 3) and (id 1 to id 2 and reverse).
How can i Achieve this. My idea is that there must be something simpler than DISTINCT.
I might go for a related table but to keep it simple, just add the column conv_id.
When a message is created, create a new conv_id and add it to the table row with the other data. When a message is replied to just use that conv_id for the new row.
When you want to retrieve conversation(s) either select WHERE conv_id=x and/or use a GROUP BY conv_id.
Why don`t you use group by to join conversations :
select * from table where to_id = 1 group by from_id