CODEWITHSUNDEEP
mysqlsql
tsiro
tsiro

Reputation: 2383

sql display unread messages based on user conversation

I having the following Chat table structure:

+----+---------+-------+--------+------------+
| id | from_id | to_id | pet_id | created_at |
+----+---------+-------+--------+------------+
| 72 |      41 |    39 |    136 |   15000621 |
| 73 |      41 |    39 |    156 |    1111111 |
| 74 |      41 |    43 |    158 |     222222 |
+----+---------+-------+--------+------------+

Message table:

+------+--------------+--------------+---------+------------+---------+-------------+---+
|  id  | chat_room_id | user_role_id | message | created_at |  read   |             |   |
+------+--------------+--------------+---------+------------+---------+-------------+---+
| 354  |          72  |           41 |  hello  |  1500621660 | 1 |
| 355  |          72  |           39 |  geiaaa | 1500621697  | 0 |
| 356  |          72  |           39 |  again  | 1500621702  | 0 |
| 357  |          73  |           41 |  fgh    | 1500621725  | 1 |
| 358  |          73  |           39 |  fhh    | 1500621736  | 0 |
| 359  |          73  |           39 |  ert    | 1500621739  | 0 |
| 360  |          74  |           41 |  ER     | 1500621780  | 0 |
+------+--------------+--------------+---------+------------+---------+-------------+---+

User table:

 +----+
 | id | 
 +----+
 | 44 |

And UserRole table: 

 +-----+---------+------+
 | id  | user_id | role |
 +-----+---------+------+ 
 | 41  | 44      | 1    |
 | 44  | 44      | 2    |
 | 45  | 44      | 3    |
 +-----+---------+------+

I would like to take the last last message of a specific user chats and also the total unread messages for each chat. The query i have tried solves the first part of the question:

(SELECT ChatRoom.id, message FROM DogVIP.ChatRoom 
     left join Message on ChatRoom.id = Message.chat_room_id
     left join UserRole on ChatRoom.from_id = UserRole.id
where 
    to_id in (select UserRole.id from UserRole left join User on UserRole.user_id = User.id where UserRole.user_id = 44) and
    Message.id in (SELECT MAX(id) FROM Message GROUP BY Message.chat_room_id)
)
union distinct
(SELECT ChatRoom.id, message FROM DogVIP.ChatRoom 
     left join Message on ChatRoom.id = Message.chat_room_id
     left join UserRole on ChatRoom.to_id = UserRole.id
where from_id in (select UserRole.id from UserRole left join User on UserRole.user_id = User.id where UserRole.user_id = 44) and
Message.id in (SELECT MAX(id) FROM Message GROUP BY Message.chat_room_id));

The output is:

+----+---------+
| id | message |
+----+---------+
| 72 | again   |
| 73 | ert     |
| 74 | ER      |
+----+---------+

And the desired output i would like to be:

+----+---------+--------+
| id | message | total  |
+----+---------+--------+
| 72 | again   | 2      |
| 73 | ert     | 2      |
| 74 | ER      | 1      |
+----+---------+--------+

Upvotes: 0

Views: 764

Answers (1)

Peter
Peter

Reputation: 16923

This will list chat room id, last message and total of all chat rooms.

Now when you get an idea you can add desired WHERE condition and JOINs

SELECT
  tlatest.chat_room_id,
  tlastmessage.message,
  COUNT(tunread.id) as total
FROM
(
SELECT
  chat_room_id,
  MAX(created_at) as last_created_at
FROM
  Message
GROUP BY
  chat_room_id
) tlatest
LEFT JOIN 
  Message tm ON tm.chat_room_id = tlatest.chat_room_id AND tm.created_at = tlatest.last_created_at
LEFT JOIN 
  Message tunread ON tunread.chat_room_id = tlatest.chat_room_id AND tunread.read = 0
GROUP BY tlatest.chat_room_id

in your case I guess it will be

SELECT
  tlatest.chat_room_id,
  tlastmessage.message,
  COUNT(tunread.id) as total
FROM
(
SELECT
  chat_room_id,
  MAX(created_at) as last_created_at
FROM
  Message
JOIN 
  Chat ON Chat.id = Message.chat_room_id
WHERE
  Chat.from_id in (select UserRole.id from UserRole left join User on UserRole.user_id = User.id where UserRole.user_id = 44) OR
  Chat.to_id in (select UserRole.id from UserRole left join User on UserRole.user_id = User.id where UserRole.user_id = 44)
GROUP BY
  chat_room_id
) tlatest
LEFT JOIN 
  Message tm ON tm.chat_room_id = tlatest.chat_room_id AND tm.created_at = tlatest.last_created_at
LEFT JOIN 
  Message tunread ON tunread.chat_room_id = tlatest.chat_room_id AND tunread.read = 0
GROUP BY tlatest.chat_room_id

Upvotes: 1

Related Questions