John Davidson

Message:

I want to display the last conversation (messages) between a user and other users along with the name and profile picture of the other user. Whatsapp and Messenger homepages are typical examples of what I want to achieve.

I have 2 tables: users and chat tables

users table

user_id     username      photo

    1        elexis        img

    2        rooney        img

    3        wayne         img

chat table

   id     user_id       friend_id       message         msg_time

    1        1             2             hello        21-08-19 04:00

    2        2             1             i'm good     21-08-19 04:00

    3        3             1             hey          21-08-19 04:00

The expected result for elexis should be:

 1   2    I'm good  **promise**  img    21-08-19 04:00

 1   3    hey       **wayne**    img    21-08-19 04:00

The expected result for promise should be:

 1   2    I'm good  **elexis**  img    21-08-19 04:00

However, what I am getting is:

1   2    hello  **promise**  img    21-08-19 04:00

2   1    hello  **elexis**   img    21-08-19 04:00

2   1    I'm good  **promise**  img    21-08-19 04:00

1   2    I'm good  **elexis**   img    21-08-19 04:00

3   1    hey       **wayne**    img    21-08-19 04:00

1   3    hey       **elexis**   img    21-08-19 04:00

My code is thus:

SELECT c.*, u.username, 

FROM users u 

INNER JOIN (

  SELECT user_id, friend_id,id, message 

  FROM chat 

  WHERE user_id = 1 OR friend_id = 1 

  UNION SELECT friend_id, user_id,id, message 

  FROM chat 

  WHERE friend_id = 2 OR user_id = 2 

  ORDER BY id DESC) c 

ON c.friend_id = u.user_id

---

My Answer:
To achieve the desired result of displaying the last messages of all users, you can modify your SQL query as follows:

php
SELECT c.id, c.user_id, c.friend_id, c.message, c.msg_time, u.username, u.photo AS profile_picture
FROM chat c
INNER JOIN users u ON (c.friend_id = u.user_id)
WHERE c.id IN (
SELECT MAX(id)
FROM chat
WHERE user_id = 1 OR friend_id = 1
GROUP BY user_id, friend_id
)
ORDER BY c.id DESC;


In this query:
1. We select the necessary columns from the `chat` table and join it with the `users` table based on the `friend_id`.
2. We filter the messages to only include the last message for each user by using a subquery that selects the maximum `id` for each user.
3. We order the results by the `id` in descending order to display the latest messages first.

This query should give you the expected result of displaying the last messages of all users along with the name and profile picture of the other user.