Getting the most recent message in a thread

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.archivedfrom    -- 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_idwhere    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_datetimefrom    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_idwhere    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;

your problem is that you are fetching only m records no matter what the order of the r records.

try adding

SELECT m.*, r.*

or

SELECT r.*, m.*

if you are using PDO::FETCH_ASSOC as your PDO fetch mode (assuming you are using PDO to access your database), the result will be an associative array where if the result set contains multiple columns with the same name, PDO::FETCH_ASSOC returns only a single value per column name. not sure which order takes presidence, so you would have to try both.

if your columns are defined in the right order, they will return the r.* value if one exists, or the m.* value if no r records exist. does this make sense? this way your result set will contain the latest record no matter which table (m or r) contains them.

http://www.php.net/manual/en/pdo.constants.php

I am afraid that you wont be able to solve this problem with a single query. Either you have to use more queries and gather the informations in the surrounding code or you will have to redesign the database structure for your messaging system a litte (tables: threads, posts, etc.). If you decide to redesign the database structure, you should also take care of the way you handle the viewed and archived fields. The way you use the fields (varchar 255 only!) might work for some users, but as soon as there are more users and higher user IDs your message system will break down.