MySQL chat system to display the last message of the sender / receiver And the name of another person

Question

I am developing a chat system, and two things should appear in the conversation list:

• The last message about who sent the message (my current user or his user).
• the name of another user

The part where I am having problems is the second. The current request displays the last message of each conversation, but in those cases when I (the current user) sent the last message, instead of my name, it should display a different username.

SELECT SQL_CALC_FOUND_ROWS
    u.id_user AS id,
    i.id_user_from,
    i.id_user_to,
    u.name AS name,
    UNIX_TIMESTAMP(i.date_msg) AS date_msg,
    i.message AS msg

    FROM inbox AS i
    INNER JOIN user AS u ON (u.id_user = i.id_user_from OR u.id_user = i.id_user_to)

    WHERE id_msg IN
    (SELECT MAX(id_msg) AS id FROM
    (
        SELECT id_msg, id_user_from AS id_with
        FROM inbox
        WHERE id_user_to = 1

        UNION ALL

        SELECT id_msg, id_user_to AS id_with
        FROM inbox
        WHERE id_user_from = 1) AS t

        GROUP BY id_with
    )

    ORDER BY i.id_msg DESC

In this example, I am Andufo (id_user = 1). Here's the sqlfiddle link if that helps. Thank!

Answer 1

,

SELECT SQL_CALC_FOUND_ROWS
    u.id_user AS id,
    i.id_user_from,
    i.id_user_to,
    u.name AS name,
    UNIX_TIMESTAMP(i.date_msg) AS date_msg,
    i.message AS msg

    FROM inbox AS i
    INNER JOIN user AS u ON u.id_user = IF(i.id_user_from = 1 /*me*/, i.id_user_to, i.id_user_from)

    WHERE id_msg IN
    (SELECT MAX(id_msg) AS id FROM
    (
        SELECT id_msg, id_user_from AS id_with
        FROM inbox
        WHERE id_user_to = 1

        UNION ALL

        SELECT id_msg, id_user_to AS id_with
        FROM inbox
        WHERE id_user_from = 1) AS t

        GROUP BY id_with
    )
    ORDER BY i.id_msg DESC

. mysql-

Answer 2

:

SELECT SQL_CALC_FOUND_ROWS
       u.id_user AS id,
       i.id_user_from,
       i.id_user_to,
       u.name AS name,
       UNIX_TIMESTAMP(i.date_msg) AS date_msg,
       i.message AS msg
    FROM inbox AS i,
         user  AS u,
         (SELECT MAX(id_msg) AS id_max,
                 id_with
              FROM (
                    SELECT id_msg,
                           id_user_from AS id_with
                        FROM inbox
                        WHERE id_user_to = 1
                    UNION ALL
                    SELECT id_msg,
                           id_user_to
                        FROM inbox
                        WHERE id_user_from = 1
                   ) AS t
              GROUP BY id_with) AS m
    WHERE i.id_msg  = m.id_max
      AND u.id_user = m.id_with
    ORDER BY i.id_msg DESC