SQL за показване на броя на непрочетените съобщения, включително непрочетените отговори

Създавам система за съобщения за CMS и не мога да намеря начин да получа броя на непрочетените съобщения във верига от съобщения.

Имам нужда от начин да разбера дали основното съобщение или някое от съобщенията за отговор са непрочетени.

Имам таблица, наречена „съобщения“, която има полетата: id, active[1,0], subject, message, datetime, user_from, user_to, reply, viewed, removed

Когато се прочете съобщение, съхранявам този потребителски идентификатор, разделен със запетая, в полето за преглед: ,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
правилно, но ще трябва да намеря дали някои са били прегледани от текущия потребител. Бих използвал id в заявката 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