List messages ordered by conversations for all users

Question

My use case is an admin view to see all the messages in the db.

I would like a list of message ordered by conversations and beginning dates, the idea is to be able to see each message in its context.

A conversation is a list of interactions between two users.

I don't have a conversation table, only a message table.

From this data sample :

id   sender      recipient
--------------------------
1    marc        rémi
2    gépéto      sylvain
3    rémi        marc
4    julie       georgette
5    rémi        marc
6    denis       julie
7    julie       rémi
8    sylvain     gépéto
9    denis       marc
10   denis       julie

I would like to obtain :

id   sender      recipient
--------------------------
1    marc        rémi
3    rémi        marc
5    rémi        marc
2    gépéto      sylvain
8    sylvain     gépéto
4    julie       georgette
6    denis       julie
10   denis       julie
7    julie       rémi
9    denis       marc

For now, I was more simply looking to get the first messages of each conversations as a first step towards the wanted result :

SELECT message.id, message.sender , message.recipient
FROM message
GROUP BY message.sender, message.recipient
HAVING message.id = min(message.id)
ORDER BY message.id DESC;

But I can't get this right, I get two conversations instead of one for all bidirectionals conversations :

id   sender      recipient
--------------------------
8    sylvain     gépéto
2    gépéto      sylvain

So, I'm stuck here ... And I would appreciate some tips !

Almost forgot: SQL Fiddle with the sample and the last query

Answer 1

You can use the following query to get the id of the first message of each conversation:

SELECT MIN(id),
       IF(sender > recipient, sender, recipient) AS participantA, 
       IF(sender > recipient, recipient, sender) AS participantB
FROM message 
GROUP BY participantA, participantB

Now use the above query in a derived table to get desired result:

SELECT id, sender, recipient
FROM (
  SELECT id, sender, recipient,
         IF(sender > recipient, sender, recipient) AS participantA, 
         IF(sender > recipient, recipient, sender) AS participantB
  FROM message) AS t1
INNER JOIN (  
  SELECT MIN(id) AS minId,
         IF(sender > recipient, sender, recipient) AS participantA, 
         IF(sender > recipient, recipient, sender) AS participantB
  FROM message 
  GROUP BY participantA, participantB
) AS t2 ON t1.participantA = t2.participantA AND t1.participantB = t2.participantB
ORDER BY t2.minId

Demo here