正在获取线程中的最新消息

如果您只有两个级别的消息（即，只有父消息和直接答案），您可以尝试以下查询：

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

它使用子查询max_reply_id作为数据源来选择最新答案的ID。如果它存在（即，如果有答案），则使用reply_message.message。如果它不存在（未找到根消息的答案），则使用root_message.message。

你们还应该考虑一下表格的结构。例如，如果reply包含NULL（如果它是父消息）或现有消息的ID，则更有意义。当前，您将其设置为0（不存在消息的ID），这是错误的。CCD_ 15和CCD_。

编辑：您还应该避免使用having子句。尽可能使用where。

这里有一个新的查询，应该可以满足您的要求。如果它有任何问题（即，如果它返回错误的数据），请告诉我。

与第一个查询一样，它是：

• 使用子查询reply_summary来累积关于回复的数据（最后回复的ID、回复的数量和未读回复的数量）
• 将此子查询联接到基表
• 在reply_summary.max_reply_id的基础上，将cms_messages as reply_message加入子查询，以获取关于最后一个回复（消息、日期时间）的数据

我简化了确定last_datetime的方式——现在需要最后一次回复的时间（如果有回复的话），或者原始帖子的时间（没有回复的时候）。

我没有按from和to字段筛选答复。如有必要，应更新reply_summary子查询的where子句。

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;

您的问题是，无论r记录的顺序如何，都只能获取m条记录。

尝试添加

SELECT m.*, r.*

或

SELECT r.*, m.*

如果使用PDO:：FETCH_ASSOC作为PDO获取模式（假设使用PDO访问数据库），则结果将是一个关联数组，如果结果集包含多个同名列，则PDO:：FETCH-ASSOC每个列名仅返回一个值。不确定哪一个命令需要主持，所以你必须同时尝试。

如果按正确的顺序定义列，则它们将返回r.*值（如果存在），或者返回m.*值（不存在r记录）。这有道理吗？这样，无论哪个表（m或r）包含最新的记录，结果集都将包含这些记录。

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

恐怕您无法用一个查询解决这个问题。要么你必须使用更多的查询并收集周围代码中的信息，要么你必须重新设计消息系统的数据库结构（表：线程、帖子等）。如果你决定重新设计数据库结构，你还应该注意处理viewed和archived字段的方式。您使用字段的方式（仅限varchar 255！）可能适用于某些用户，但一旦有更多用户和更高的用户ID，您的消息系统就会崩溃。