mysql - sql根据用户对话显示未读消息

Question

我有以下聊天表结构:

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

消息表:

+------+--------------+--------------+---------+------------+---------+-------------+---+
|  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 |
+------+--------------+--------------+---------+------------+---------+-------------+---+

用户表:

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

和UserRole表:

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

我想获取特定用户聊天的最后一条消息以及每个聊天的未读消息总数。我尝试过的查询解决了问题的第一部分:

(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));

输出为:

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

我想要的输出是:

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

Answer 1

这将列出聊天室 ID、最后一条消息以及所有聊天室的总数。

现在，当您有了想法时，您可以添加所需的 WHERE 条件和 JOIN

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

就你的情况而言，我猜会是

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