获取线程中的最新消息

Question

我有一个查询，可以获取消息系统主页所需的所有信息（包括未读消息计数等）...但它当前检索原始线程消息。我想扩充下面的查询以获取每个线程中最新的消息。

这个查询非常接近，但是我平庸的 SQL 技能使我无法完成任务...

$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;
}

提前感谢您提供的任何帮助！

编辑：（数据库）

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;

编辑2：（要求）

• 返回特定user_id的所有父消息：$cms_user['id']
• 返回该父消息的回复数：num_replies
• 返回该父消息的未读回复数：unread_replies
• 返回父消息的日期或最近的回复：last_datetime
• 返回消息是否在存档中：message_archive
• 返回消息是否已被查看：message_viewed
• 返回所有消息按 DESC 日期时间顺序
• 返回来自父级的最新消息，或者回复（如果有）（例如 gmail）

Answer 1

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

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），这是错误的。 viewed 和 archived 的类型也很奇怪。

编辑：您还应该避免使用 having 子句。如果可能，请使用 where 代替。

---

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

与第一个查询一样，它：

• 使用子查询 reply_summary 来累积有关回复的数据（最后回复的 ID、回复数和未读回复数）；
• 将此子查询连接到基表；
• 根据 reply_summary.max_reply_id 将 cms_messages 作为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;

Answer 2

你的问题是你只获取 m 条记录，无论 r 条记录的顺序是什么。

尝试添加

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

Answer 3

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