在线程中获取最新消息 [英] Getting the most recent message in a thread

问题描述

我有一个查询，该查询可以获取消息传递系统主页所需的所有信息(包括未读消息计数等)...，但是当前它会检索原始线程消息.我想增加以下查询，以代替每个线程中最多的 recent 消息.

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.

该查询非常接近，但是我平庸的SQL技能使我无法完成所有事情……

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!

(数据库)

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日期时间顺序返回所有邮件
• 从父级返回最新的message，或者在有回信的情况下回复(例如gmail)

• 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)

推荐答案

如果您只有2级消息(即，只有父级消息和直接答复)，则可以尝试以下查询:

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

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

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.

您还应该考虑表的结构.例如，如果reply包含NULL(如果它是父消息)或现有消息的ID，则更有意义.当前，您将其设置为0(不存在的消息的ID)，这是错误的. viewed和archived的类型也很奇怪.

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.

您还应该避免使用having子句.尽可能使用where.

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.

像第一个查询一样:

• 使用子查询reply_summary累积有关答复的数据(上次答复的ID，答复数和未读答复的数)；
• 将此子查询加入基表；
• 基于reply_summary.max_reply_id将cms_messages as reply_message联接到子查询，以获取有关上次答复(消息，日期时间)的数据.

• 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).

我已经简化了确定last_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).

我尚未按from和to字段过滤回复.如有必要，应更新reply_summary子查询的where子句.

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;

这篇关于在线程中获取最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！