0

I am building a messaging system for a CMS and can not find a way to get the number of unread messages in a message chain.

I need a way to find out if the main message or any of it's reply messages are unread.

I have a table called "messages" that has the fields: id, active[1,0], subject, message, datetime, user_from, user_to, reply, viewed, removed

When a message is read I store that users id comma-delimited in the viewed field: ,3,4,12,

Then to find out if it's viewed I look for the sender's id like this:

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

I just figure out how to get that logic into this SQL select so that I can see if a parent message should be marked as unread. The following SQL works great and grabs all the messages and the number of replies and orders them by datetime.

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

Any help would be greatly appreciated... I can't wrap my head around it!

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
RANGER
  • 1,643
  • 2
  • 17
  • 31
  • Check this similar problem: http://stackoverflow.com/questions/4318010/print-hierachical-data-in-a-parent-child-form-unordered-list-php/4318846#4318846 – ypercubeᵀᴹ Mar 23 '11 at 07:59
  • Side questions: How long are the `viewed` and `removed` fields? What happens when more than 100 users view a message? You really should try normalizing the table than using comma-delimited fields. – ypercubeᵀᴹ Mar 23 '11 at 08:01
  • Also this question: http://stackoverflow.com/questions/5291054/hierarchical-sql-problem – ypercubeᵀᴹ Mar 23 '11 at 08:10
  • The CMS is very tightly managed and does not support that many users... so the datatype is just a VARCHAR(255) which can hold more than enough ids. That article was in the right direction... but I don't think it will help me with my current issue. I THINK I just need a a small addition to my SQL query... I'm just not sure where. – RANGER Mar 23 '11 at 08:14
  • If you think the maximum level of message-reply-reply-...-reply is small, then it can be handled by a decent SQL query. – ypercubeᵀᴹ Mar 23 '11 at 08:21

1 Answers1

1

You should check this article: Managing Hierarchical Data in MySQL

--EDIT--UPDATE

OK, you only have 1 level of replies so no need for the above.

Try this: 

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
;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • @ypercube I forgot to explain, there is only one level of replies per message... there are messages and replies to those messages. You can not reply to a reply. – RANGER Mar 23 '11 at 08:33
  • Any further advice based on this simplified structure? I see adding another table just to track who has viewed a message to be complicating things. The SQL to see if a message is viewed is straightforward... I just need a way to incorporate it into my existing SELECT. – RANGER Mar 23 '11 at 21:37
  • The SQL to see if a message has not been viewed by anyone is `viewed = ','` ? – ypercubeᵀᴹ Mar 23 '11 at 21:47
  • correct, but i would need to find whether any have been viewed by the current user. I would use the id in the query `m.viewed LIKE '%,$user_id,%'` where $user_id would be a number like 3 searching `viewed` which would contain something like ",2,3,6," if they've viewed it ... or ",2,7,12," if they haven't – RANGER Mar 23 '11 at 21:56
  • OK. I think i understand what you want. – ypercubeᵀᴹ Mar 23 '11 at 22:29