相同的id，但只获得1个结果

Question

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.

Answer 1

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.