MSYQL SELECT and group conversations by user and show last message

Question

I have a user table and a message table.

In the message table (named messaggi) there are msg_to and msg_from, their message (msg_text) and msg_date (which is a date and time).

I'm trying to achieve to have a list of:

1. all users from the utenti table

2. For each user where a message was SENT or RECEIVED the last should show

3. Hide message from people who are not self (in this case id_utente 1 = self).

This is what I came up with but I keep on either getting all messages or double users, and so forth...

SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
       u.email,
       u.color,
       CONCAT(u.fname, " ", u.lname) AS full_name,
       MAX(m.msg_date) AS msg_date,
       m.msg_text
FROM utenti u
     INNER JOIN messaggi m ON m.msg_to = u.id_utente
WHERE m.msg_to = 1
GROUP BY m.msg_to,
         m.msg_from
UNION
SELECT CONCAT(LEFT(u2.fname, 1), LEFT(u2.lname, 1)) AS iniziali,
       u2.email,
       u2.color,
       CONCAT(u2.fname, " ", u2.lname) AS full_name,
       "",
       ""
FROM utenti u2
WHERE u2.id_utente NOT IN
(
    SELECT id_utente
    FROM utenti u
         INNER JOIN messaggi m ON m.msg_to = u.id_utente
    WHERE m.msg_to = 1
          AND u.id_utente = 1
    GROUP BY m.msg_to,
             m.msg_from
);

Here is a Fiddle:

http://sqlfiddle.com/#!9/106319/1

The output I want to have should be:

| iniziali |       email |  color |    full_name |            msg_date |       msg_text |
|----------|-------------|--------|--------------|---------------------|----------------|
|       BV |  456@me.com | (null) |   Bill Villa | 2018-04-20 12:29:20 | Msg 2 (1 to 2) |
|       MG |  789@me.com | (null) |    Max Gazze | 2018-04-09 14:59:39 | Msg 1 (3 to 1) |
|       JB |  101@me.com | (null) |    Jack Blue |                     |                |

In this case, me being id#1 I only see the messages sent FROM ME and TO ME, and for each user I only see the last one received or sent. Like WhatsApp, Facebook Messanger, Telegram, etc... You see the contact and the last message sent/received for each one.

Messages to and from other users (i.e: user 2 to user 3) are not shown.

As you can see I DO NOT see myself (id#1) in the users list and for Jack Blue I only see his name e no message, as no message was ever sent from/to user 1 and 4. So I end up with a list of users and for each of them I see the most recent message (sent or received) and where no message I only see the user with empty msg_text and msg_date

Answer 1

Ok, so I think that I may have a solution. I have tested it with your fiddle and it seems to work OK.

By combining the MySQL GROUP BY behaviour (which returns only the first of the group) and ORDER BY we can achieve the desired outcome, although the final results are ordered by name. (EDIT: Simply add ORDER BY msg_date desc to the end of the SQL query to order it by date instead)

EDIT: I have altered the query to group on id_utente and also order by msg_date. It works on the fiddle, which is running MySQL version 5.6

SELECT * FROM ((SELECT CONCAT(LEFT(u.fname , 1), LEFT(u.lname , 1)) as iniziali, 
        u.id_utente,
        u.email, 
        u.color, 
        CONCAT(u.fname, " ", u.lname) as full_name, 
        m.msg_date,
        m.msg_text 
  FROM utenti u
  LEFT JOIN messaggi m ON m.msg_to = u.id_utente
  WHERE (m.msg_from = 1) OR m.msg_date is NULL)
  UNION
  (SELECT CONCAT(LEFT(u.fname , 1), LEFT(u.lname , 1)) as iniziali, 
        u.id_utente,
        u.email, 
        u.color, 
        CONCAT(u.fname, " ", u.lname) as full_name, 
        m.msg_date,
        m.msg_text 
  FROM utenti u
  LEFT JOIN messaggi m ON m.msg_from = u.id_utente
  WHERE (m.msg_to = 1) OR m.msg_date is NULL)
  ORDER BY id_utente, msg_date desc) st1
  WHERE id_utente != 1
  GROUP BY id_utente ORDER BY msg_date desc

And in your fiddle this returns

iniziali | id_utente |  email      | color  | full_name  | msg_date             | msg_text
---------+-----------+-------------+--------+------------+----------------------+-------------------
BV       |         2 | 456@me.com  | (null) | Bill Villa | 2018-04-20T12:29:20Z | Msg 2 (1 to 2)
MG       |         3 | 789@me.comm | (null) | Max Gazze  | 2018-04-09T14:59:39Z | Msg 1 (3 to 1)
JB       |         4 | 101@me.comm | (null) | Jack Blue  | (null)               | (null)

Answer 2

Having made the effort I figured I might as well post this even though you've already accepted an answer. This query will also work based on the sample data you provided. I have used a variable to allow testing for different users.

set @uid = 1;
select u1.id_utente as id,
    CONCAT(LEFT(u1.fname, 1), LEFT(u1.lname, 1)) AS iniziali,
    u1.email as email,
    u1.color as color,
    concat(u1.fname, ' ', u1.lname) as full_name, 
    m.msg_date as msg_date,
    m.msg_text as msg_text
from utenti u1
join utenti u2 
    on u1.id_utente != u2.id_utente and u2.id_utente = @uid
left join messaggi m
    on m.msg_to = u1.id_utente and m.msg_from = u2.id_utente or
       m.msg_to = u2.id_utente and m.msg_from = u1.id_utente
where m.msg_date = (select max(msg_date) 
                    from messaggi m2
                    where m2.msg_to = u1.id_utente and m2.msg_from = u2.id_utente or
                    m2.msg_to = u2.id_utente and m2.msg_from = u1.id_utente) or
      m.id_msg is null
group by u1.id_utente
order by msg_date desc

With @uid = 1, output is

id  iniziali    email       color   full_name       msg_date                msg_text
2   BV          456@me.com  (null)  Bill Villa      2018-04-20T12:29:20Z    Msg 2 (1 to 2)
3   MG          789@me.com  (null)  Max Gazze       2018-04-09T14:59:39Z    Msg 1 (3 to 1)
4   JB          101@me.com  (null)  Jack Blue       (null)                  (null)

With @uid = 2, output is

id  iniziali    email       color   full_name       msg_date                msg_text
1   JL          123@me.com  (null)  Joe Lombardi    2018-04-20T12:29:20Z    Msg 2 (1 to 2)
3   MG          789@me.com  (null)  Max Gazze       2018-04-09T15:03:44Z    Msg 1 (3 to 2)
4   JB          101@me.com  (null)  Jack Blue       (null)                  (null)

With @uid = 3, output is

id  iniziali    email       color   full_name       msg_date                msg_text
2   BV          456@me.com  (null)  Bill Villa      2018-04-09T15:03:44Z    Msg 1 (3 to 2)
1   JL          123@me.com  (null)  Joe Lombardi    2018-04-09T14:59:39Z    Msg 1 (3 to 1)
4   JB          101@me.com  (null)  Jack Blue       (null)                  (null)

With @uid = 4, output is

id  iniziali    email       color   full_name       msg_date                msg_text
1   JL          123@me.com  (null)  Joe Lombardi    (null)                  (null)
2   BV          456@me.com  (null)  Bill Villa      (null)                  (null)
3   MG          789@me.com  (null)  Max Gazze       (null)                  (null)

Answer 3

Here is my attempt at this, I make use of a union and the first part is for those users that has messages from/to id 1 and the second part is for those that has not.

SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   m.msg_date,
   m.msg_text
FROM utenti u, messaggi m
WHERE (m.msg_to = u.id_utente OR m.msg_from = u.id_utente)
AND (m.msg_to = 1 and m.msg_from != 1 OR m.msg_to != 1 and m.msg_from = 1)
AND m.msg_date = (SELECT MAX(m2.msg_date) FROM messaggi m2 WHERE (m2.msg_to = u.id_utente AND m2.msg_from = 1) OR (m2.msg_from = u.id_utente AND m2.msg_to = 1))
UNION ALL
SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   '',
   ''
FROM utenti u
WHERE NOT EXISTS (SELECT * FROM messaggi m WHERE (m.msg_to = u.id_utente AND m.msg_from = 1) OR (m.msg_from = u.id_utente AND m.msg_to = 1))
AND u.id_utente != 1
ORDER BY msg_date DESC