Я работаю над системой обмена сообщениями с двумя таблицами и еще одной таблицей с информацией о пользователях.
Диалог может вестись от 2 и более пользователей. Каждый разговор имеет UID, и каждое сообщение, которым обмениваются пользователи, помечается этим UID разговора.
Вот таблицы:
conversation_list
: каждая строка в этой таблице связывает user_id
и conversation_id
, она также содержит последний раз, когда пользователь просматривал беседу.
`id` -> unique ID, autoincremented
`user_id` -> This contains the user associated with the conversation.
`conversation_id` -> This contains the UID of the conversation
`date_lastView` -> This field has the time that the user viewed the conversation last
conversation_messages
: каждая строка в этой таблице содержит сообщение
`id` -> unique ID, autoincremented
`user_id` -> This contains the user that sent the message.
`conversation_id` -> This contains the UID of the conversation
`date_created` -> This contains the time when the message was posted
`message` -> This contains the message
users
:каждая строка в этой таблице содержит пользователя
`User_ID` -> UID of the user
`FirstName` -> This contains the first name of the user
`LastName` -> This contains the last name of the user
У меня уже есть SQL-запрос для получения последнего сообщения каждого диалога. Вот он:
SELECT *
FROM conversation_messages AS m
JOIN
(SELECT mx.conversation_id,
MAX(mx.date_created) AS MaxTime
FROM conversation_messages AS mx
GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
AND m.date_created = mx.MaxTime
JOIN
(SELECT mu.conversation_id
FROM conversation_list AS mu
WHERE mu.user_id = :USER_ID_CONNECTED
GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id
JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id
GROUP BY mu.conversation_id
ORDER BY m.date_created DESC
Теперь я хотел бы добавить к этому прекрасно работающему запросу возможность возврата:
date_creaded
больше, чем date_lastView
вошедшего в систему пользователя)User_ID
каждого пользователя в каждом разговоре и отсортированный по тому времени, когда они в последний раз публиковали сообщение в разговор.FirstName
и LastName
пользователя.Я попробовал несколько вещей, но мне это не удалось, поэтому теперь я прошу сообщество SO о его драгоценной помощи.
Все это может отображать разговоры только там, где принимает участие вошедший в систему пользователь.
Это помогает, я создал SQLFiddle