Ответ 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 для выбора идентификатора последнего ответа. Если он существует (т.е. Если есть ответы), используется reply_message.message. Если он не существует (ответа не найдено для корневого сообщения), то используется root_message.message.
Вы также должны подумать о структуре таблицы. Например, было бы разумнее, если reply содержал либо NULL, если это родительское сообщение, либо идентификатор существующего сообщения. В настоящее время вы устанавливаете значение 0 (ID несуществующего сообщения), что неверно. Типы viewed и archived также странные.
Изменить: вам также следует избегать использования предложения having. Используйте where вместо этого, если это возможно.
Вот новый запрос, который должен соответствовать вашим требованиям. Если с ним возникла какая-либо проблема (например, если он возвращает неверные данные), сообщите мне.
Как и первый запрос, он:
- использует подзапрос
reply_summaryдля сбора данных о ответах (идентификатор последнего ответа, количество ответов и количество непрочитанных ответов); - присоединяет этот подзапрос к базовой таблице;
- присоединяет
cms_messages as reply_messageк подзапросу, основанному наreply_summary.max_reply_id, для получения данных о последнем ответе (message, datetime).
Я упростил способ определения last_datetime - теперь он принимает либо время последнего ответа (если есть какой-либо ответ), либо время оригинальной записи (когда ответы не найдены).
Я не фильтровал ответы полями from и to. Если это необходимо, необходимо обновить предложение where подзапроса reply_summary.
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;