Привет. Такой вопрос.
Есть 3 таблицы (упрощённо):
1. chat (id, type, virtual_id). type - тип чата. Может быть: 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 - это последнее прочитанное сообщение таким то юзером в таком то чате).

Админ может слать сообщения всем, всем студентам, всем преподавателям. Технически это делается как отдельный чат специального вида all, all_teachers, all_students. Но в индивидуальных чатах с этим админом на экране будут видны эти сообщения (т.е. там смесь сообщений из реального индивидуального чата с админом + сообщения от чата типа all, all_teachers, all_students).
Количество не прочитанных сообщений для каждого индивидуального чата я получаю следующим образом одним запросом:
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

однако в этих не прочитанных сообщениях не учитываются "общие" сообщения от админа (all, all_teachers, all_students). Можно как то доработать запрос, чтобы они тоже учитывались? Или хотя бы сделать отдельным запросом который бы получал для каждого индивидуального чата количество непрочитанных сообщений от админа? Т.е. получал на вход id юзера и массив virtual_id индивидуальных чатов, а на выходе бы строил таблицу вида:

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

где в столбцах all, all_teachers, all_students - было бы указано количество не прочитанных сообщений данного типа с учётом того что все id этих не прочитанных сообщений были бы больше чем chat_message_read.last_read_message_id каждого соответствующего индивидуального чата.
Надеюсь понятно описал. Спасибо.