Привет. Такой вопрос.
Есть 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). Можно как то доработать запрос, чтобы они тоже учитывались? Или хотя бы сделать отдельным запросом который бы получал для каждого индивидуального чата количество непрочитанных сообщений типа 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 каждого соответствующего индивидуального чата.
Надеюсь понятно описал. Спасибо.
