SQL для отображения количества непрочитанных сообщений, включая непрочитанные ответы

Я создаю систему обмена сообщениями для CMS и не могу найти способ получить количество непрочитанных сообщений в цепочке сообщений.

Мне нужен способ узнать, не прочитаны ли основное сообщение или ответные сообщения.

У меня есть таблица под названием «сообщения», в которой есть поля: идентификатор, активный [1,0], тема, сообщение, дата и время, user_from, user_to, ответ, просмотр, удаление

Когда сообщение прочитано, я сохраняю идентификатор пользователя с разделителями-запятыми в просматриваемом поле: ,3,4,12,

Затем, чтобы узнать, просматривался ли он, я ищу идентификатор отправителя следующим образом:

m.viewed LIKE '%,$user_id,%'

Я просто выясняю, как включить эту логику в этот выбор SQL, чтобы я мог видеть, должно ли родительское сообщение быть помечено как непрочитанное. Следующий SQL отлично работает и захватывает все сообщения и количество ответов и упорядочивает их по дате и времени.

SELECT m.*, COUNT(*) AS num_replies, MAX(r.datetime) AS max_datetimeunread_replies
FROM directus_messages AS m
LEFT JOIN directus_messages as r
    ON m.id = r.reply
WHERE m.active = '1' 
AND m.removed NOT LIKE '%,$user_id,%' 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY datetime DESC, max_datetime DESC

Любая помощь будет принята с благодарностью... Я не могу обдумать это!


mysql php messaging
person RANGER    schedule 23.03.2011    source источник
comment
Проверьте аналогичную проблему: stackoverflow.com/questions/4318010/   -  person ypercubeᵀᴹ    schedule 23.03.2011
comment
Побочные вопросы: Какова длина полей viewed и removed? Что происходит, когда более 100 пользователей просматривают сообщение? Вам действительно следует попробовать нормализовать таблицу, а не использовать поля с разделителями-запятыми.   -  person ypercubeᵀᴹ    schedule 23.03.2011
comment
Также этот вопрос: stackoverflow.com/questions/5291054/hierarchical-sql-problem   -  person ypercubeᵀᴹ    schedule 23.03.2011
comment
CMS очень жестко управляется и не поддерживает такое количество пользователей... поэтому тип данных - это просто VARCHAR(255), который может содержать более чем достаточно идентификаторов. Эта статья была в правильном направлении... но я не думаю, что она поможет мне с моей текущей проблемой. Я ДУМАЮ, что мне просто нужно небольшое дополнение к моему SQL-запросу... Я просто не знаю, где.   -  person RANGER    schedule 23.03.2011
comment
Если вы считаете, что максимальный уровень сообщения-ответа-ответа-...-ответа мал, то его можно обработать приличным SQL-запросом.   -  person ypercubeᵀᴹ    schedule 23.03.2011

Ответы (1)


arrow_upward
1
arrow_downward

Вы должны проверить эту статью: Управление иерархическими данными в MySQL

--РЕДАКТИРОВАТЬ--ОБНОВИТЬ

Хорошо, у вас есть только 1 уровень ответов, так что нет необходимости в вышеперечисленном.

Попробуй это:

SELECT m.*
     , COUNT(*) AS num_replies
     , MAX(r.datetime) AS max_datetime
     , (m.viewed LIKE '%,$user_id,%') 
         AS message_viewed    --shows True or False
     , SUM(r.viewed NOT LIKE '%,$user_id,%')
         AS unread_replies    --shows number of unread replies
FROM directus_messages AS m
  LEFT JOIN directus_messages as r
    ON m.id = r.reply
WHERE m.active = '1' 
  AND m.removed NOT LIKE '%,$user_id,%' 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY m.datetime DESC
       , max_datetime DESC
;
person ypercubeᵀᴹ    schedule 23.03.2011
comment
@ypercube Я забыл объяснить, на каждое сообщение есть только один уровень ответов ... есть сообщения и ответы на эти сообщения. Вы не можете ответить на ответ. - person RANGER; 23.03.2011
comment
Любые дальнейшие советы, основанные на этой упрощенной структуре? Я вижу, что добавление еще одной таблицы только для отслеживания того, кто просмотрел сообщение, усложняет ситуацию. SQL, чтобы узнать, просматривается ли сообщение, прост... Мне просто нужен способ включить его в мой существующий SELECT. - person RANGER; 24.03.2011
comment
SQL, чтобы узнать, не просматривалось ли сообщение кем-либо, viewed = ',' ? - person ypercubeᵀᴹ; 24.03.2011
comment
правильно, но мне нужно было бы найти, были ли какие-либо просмотрены текущим пользователем. Я бы использовал идентификатор в запросе m.viewed LIKE '%,$user_id,%', где $user_id будет числом вроде 3, ищущим viewed, которое будет содержать что-то вроде 2,3,6, если они его просмотрели... или 2,7,12, если у них нет - person RANGER; 24.03.2011
comment
В ПОРЯДКЕ. Кажется, я понимаю, чего ты хочешь. - person ypercubeᵀᴹ; 24.03.2011