What should be the request?

Question

MySQL

Hello. A question.
There are 3 tables (simplified):
1. chat (id, type, virtual_id). type - chat type. Can be: individual, group, all, all_teachers, all_students.
2. chat_message (id, chat_id, sender_id, message)
3. chat_message_read (id, chat_id, user_id, last_read_message_id (last_read_message_id is the last message read by such and such a user in such and such a chat)
Admin can send messages to all, all students, all teachers Technically, this is done as a separate chat of a special type all, all_teachers, all_students.But in individual chats with this admin, these messages will be visible on the screen (i.e. there is a mixture of messages from a real individual chat with the admin + messages from a chat like all, all_teachers , all_students).
I get the number of unread messages for each individual chat in the following way with one request:

SELECT c.virtual_id AS virtual_id, COUNT(cm.id) AS cnt, MAX(cm.id) AS last_message_id
FROM chat_message cm
INNER JOIN chat c ON cm.chat_id = c.id
WHERE c.virtual_id IN ('ind_4_9', 'ind_4_20', 'ind_4_32')
AND cm.sender_id <> :user_id
AND cm.id > (
    SELECT IF(count(*) > 0, rcm.last_chat_message_id, 0)
    FROM chat_message_read rcm
    WHERE rcm.chat_id = c.id
    AND rcm.user_id = :user_id
)
GROUP BY c.virtual_id

however, these unread messages do not count "general" messages from the admin (all, all_teachers, all_students). Is it possible to modify the request somehow so that they are also taken into account? Or at least make it a separate request that would receive for each individual chat the number of unread messages like all, all_teachers, all_students from the admin? Those. received the user id and the virtual_id array of individual chats as input, and would build a table like this at the output:

virtual_id | all | all_teachers | all_students
--------------------------------------------------------
ind_4_9    | 3   | 4            | 6
ind_4_20   | 5   | 2            | 3
ind_4_32   | 2   | 2            | 1

where the columns all, all_teachers, all_students would indicate the number of unread messages of this type, taking into account the fact that all id of these unread messages would be greater than the chat_message_read.last_read_message_id of each corresponding individual chat.
I hope I explained clearly. Thank you.

Answer 1

You have a clip on the list of virtual chats and it cuts off the general ones.
You can change this condition:
WHERE c.virtual_id IN ('ind_4_9', 'ind_4_20', 'ind_4_32')
something like this:
WHERE (c.virtual_id IN ('ind_4_9', 'ind_4_20', 'ind_4_32') or c. type in (all, all_teachers, all_students))