A list of conversations in threads for all users

Question

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

I need a list of messages sorted by thread and start date, the idea is to see each message in its context.

A conversation is a list of interactions between two users.

I have no table conversation, only tables message.

From this sample data:

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 receive:

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

Currently, I more simply wanted to receive the first messages of each conversation as a first step to the desired 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 it right, I get two conversations instead of one for all bi-directional conversations:

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

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

: SQL Fiddle

Answer 1

, id :

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

:

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

Answer 2

- SqlFiddle

SELECT m.*, CASE 
                WHEN sender <= recipient THEN concat(sender,'-',recipient)
                ELSE concat(recipient,'-', sender)
            END as conversation
FROM message m
ORDER BY conversation, id

OUTPUT

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

aproach, marc-rémi, , MIN() .

SqlFiddleDemo

SELECT conversation_id, T.id, T.sender, T.recipient, T.conversation
FROM (
    SELECT CASE 
               WHEN sender <= recipient THEN concat(sender,'-',recipient)
               ELSE concat(recipient,'-', sender)
           END as conversation,
           MIN(id) as conversation_id
    FROM message m
    GROUP BY conversation    
    ) as convesation_start
JOIN (
    SELECT m.*, CASE 
                    WHEN sender <= recipient THEN concat(sender,'-',recipient)
                    ELSE concat(recipient,'-', sender)
                END as conversation
    FROM message m
    ) as T
  ON 
  convesation_start.conversation = T.conversation
ORDER BY conversation_id, T.id

OUTPUT

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

Answer 3

:

SELECT id, sender, recipient
FROM (SELECT message.id, message.sender , message.recipient
            ,concat(greatest(message.sender,message.recipient)
                   ,least(message.sender,message.recipient)
                   ) as conv
       FROM message) as tab1
INNER JOIN 
    (SELECT min(id) as min
           ,concat(greatest(message.sender,message.recipient)
                  ,least(message.sender,message.recipient)
                  ) as conv
      FROM message 
    GROUP BY conv) as tab2
ON tab1.conv = tab2.conv
ORDER BY tab2.min

• conv -

: http://sqlfiddle.com/#!9/fc66b/41