Получение последнего сообщения в потоке

Ответ 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;

Ответ 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

Ответ 3

Я боюсь, что вы не сможете решить эту проблему с помощью одного запроса. Либо вы должны использовать больше запросов и собирать информацию в окружающем коде, либо вам придется перепроектировать структуру базы данных для вашей системы обмена сообщениями litte (таблицы: потоки, сообщения и т.д.). Если вы решите переработать структуру базы данных, вы также должны позаботиться о том, как вы обрабатываете поля viewed и archived. То, как вы используете поля (только varchar 255!), Может работать для некоторых пользователей, но как только появится больше пользователей и более высокие идентификаторы пользователей, ваша система сообщений сломается.