Getting the most recent message in a thread

Question

I have a query that gets all the info I need for a messaging system's main page (including unread message count, etc)... but it currently retrieves the original threads message. I would like to augment the below query to grab the most recent message in each thread instead.

This query is very close, however my mediocre SQL skills are keeping me from wrapping things up...

$messages = array();
$unread_messages_total = 0;

$messages_query = "
SELECT m.*
    , COUNT(r.id) AS num_replies
    , MAX(r.datetime) AS reply_datetime
    , (m.archived NOT LIKE '%,".$cms_user['id'].",%') AS message_archive
    , (m.viewed LIKE '%,".$cms_user['id'].",%') AS message_viewed 
    , SUM(r.viewed NOT LIKE '%,".$cms_user['id'].",%') AS unread_replies 
    , CASE
        WHEN MAX(r.datetime) >= m.datetime THEN MAX(r.datetime)
        ELSE m.datetime
        END AS last_datetime
FROM directus_messages AS m
LEFT JOIN directus_messages as r ON m.id = r.reply
WHERE m.active = '1'  
AND (m.to LIKE '%,".$cms_user['id'].",%' OR m.to = 'all' OR m.from = '".$cms_user['id']."') 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY last_datetime DESC";

foreach($dbh->query($messages_query) as $row_messages){
    $messages[] = $row_messages;
    $unread_messages_total += (strpos($row_messages['archived'], ','.$cms_user['id'].',') === false && ( (strpos($row_messages['viewed'], ','.$cms_user['id'].',') === false && $row_messages['unread_replies'] == NULL) || ($row_messages['unread_replies']>0 && $row_messages['unread_replies'] != NULL) ) )? 1 : 0;
}

Thanks in advance for any help you can provide!

EDIT: (Database)

CREATE TABLE `cms_messages` (
  `id` int(10) NOT NULL auto_increment,
  `active` tinyint(1) NOT NULL default '1',
  `subject` varchar(255) NOT NULL default '',
  `message` text NOT NULL,
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `reply` int(10) NOT NULL default '0',
  `from` int(10) NOT NULL default '0',
  `to` varchar(255) NOT NULL default '',
  `viewed` varchar(255) NOT NULL default ',',
  `archived` varchar(255) NOT NULL default ',',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

EDIT 2: (Requirements)

• Return all parent messages for a specific user_id: $cms_user['id']
• Return the number of replies for that parent message: num_replies
• Return the number of unread replies for that parent message: unread_replies
• Return the date of the parent message or it's most recent reply: last_datetime
• Return whether the message is in the archive: message_archive
• Return whether the message has been viewed: message_viewed
• Return all messages in DESC datetime order
• Return the newest message, from the parent or replies if there are some (like gmail)

Answer 1

If you have only 2 levels of messages (i.e., only parent messages and direct answers), you might try this query:

select
    root_message.id,
    root_message.active,
    root_message.subject,
    case
        when max_reply_id.max_id is null then 
            root_message.message
        else
            reply_message.message
    end as message,
    root_message.datetime,
    root_message.reply,
    root_message.from,
    root_message.to,
    root_message.viewed,
    root_message.archived
from
    -- basic data
    cms_messages as root_message
    -- ID of last reply for every root message
    left join (
        select 
            max(id) as max_id, 
            reply as parent_id 
        from 
            cms_messages
        where
            reply <> 0 
        group by 
            reply
    ) as max_reply_id on max_reply_id.parent_id = root_message.id                                              
    left join cms_messages as reply_message on reply_message.id = max_reply_id.max_id
where
    root_message.reply = 0

It uses subquery max_reply_id as source of data to select ID of the latest answer. If it exists (i.e., if there are answers), reply_message.message is used. If it does not exist (no answer has been found for root message), then root_message.message is used.

You should also think about structure of table. E.g., it would make more sense if reply contained either NULL, if it is parent message, or ID of existing message. Currently, you set it to 0 (ID of non-existent message), which is wrong. Types of viewed and archived are also weird.

Edit: you should also avoid using having clause. Use where instead, when possible.

---

Here's a new query that should fulfil your requirements. If there is any problem with it (i.e., if it returns wrong data), let me know.

Like the first query, it:

• uses subquery reply_summary to accumulate data about replies (ID of last reply, number of replies and number of unread replies);
• joins this subquery to the base table;
• joins cms_messages as reply_message to the subquery, based on reply_summary.max_reply_id, to get data about the last reply (message, datetime).

I've simplified the way how you determine last_datetime - it now takes either time of last reply (if there is any reply), or time of original post (when no replies are found).

I have not filtered replies by from and to fields. If it is necessary, where clause of reply_summary subquery should be updated.

select
    parent_message.id,
    parent_message.subject,
    parent_message.message,
    parent_message.from,
    parent_message.to,
    coalesce(reply_summary.num_replies, 0) as num_replies,
    last_reply_message.datetime as reply_datetime,
    (parent_message.archived NOT LIKE '%,{$cms_user['id']},%') AS message_archive,
    (parent_message.viewed   LIKE     '%,{$cms_user['id']},%') AS message_viewed,
    reply_summary.unread_replies,
    coalesce(last_reply_message.message, parent_message.message) as last_message,
    coalesce(last_reply_message.datetime, parent_message.datetime) as last_datetime
from
    cms_messages as parent_message
    left join (
        select
            reply as parent_id,
            max(id) as last_reply_id,
            count(*) as num_replies,
            sum(viewed not like '%,{$cms_user['id']},%') as unread_replies
        from
            cms_messages
        where
            reply <> 0 and
            active = 1
        group by
            reply
    ) as reply_summary on reply_summary.parent_id = parent_message.id
    left join cms_messages as last_reply_message on last_reply_message.id = reply_summary.last_reply_id
where
    parent_message.reply = 0 and
    parent_message.active = 1 and
    (parent_message.to like '%,{$cms_user['id']},%' or parent_message.to = 'all' or parent_message.from = '{$cms_user['id']}')
order by
    last_datetime desc;