Я создал систему обмена сообщениями для пользователей, которая позволяет им отправлять сообщения другому пользователю. Если они разговаривают впервые, то начинается новый разговор, если нет, то старый разговор продолжается.

Входящие пользователи перечисляют все разговоры, которые пользователь имел со всеми другими пользователями, затем они упорядочиваются по разговору, в котором содержится последнее сообщение.

Пользователь может иметь только один разговор с другим пользователем.

Когда пользователь щелкает один из этих разговоров, он перенаправляется на страницу, показывающую весь разговор, который у него был, с последними сообщениями вверху. Так что это похоже на функциональность чата с сообщениями.

У меня есть две таблицы:

userconversation usermessages

userconversation

Содержит идентификатор автоинкремента, который является идентификатором разговора, а также идентификатор пользователя и идентификатор друга.

Кто бы ни инициировал первый разговор, он всегда будет userId и friendId получателя, тогда он никогда не изменится для этого разговора.

+----+--------+----------+
| id | userId | friendId |
+----+--------+----------+

usermessages

Содержит конкретные сообщения, а также флаг чтения, время и идентификатор разговора

+----+---------+--------+------+------+----------------+
| id | message | userId | read | time | conversationId |
+----+---------+--------+------+------+----------------+

How it works

Когда пользователь отправляет сообщение другому пользователю, запускается запрос, чтобы проверить, совпадают ли оба пользователя в таблице разговоров пользователей, если это так,conversationId используется, и разговор продолжается, если для них не создан новый ряд с уникальнымconversationId.

Where it gets complicated

Пока все хорошо, однако, когда дело доходит до отображения входящих сообщений всех разговоров, отсортированных по последнему сообщению, становится сложно сделать один запрос ...

Чтобы иметь возможность составить список бесед, вы должны сначала найти самую последнюю запись каждого разговора, но, поскольку вы не можете упорядочить их перед группой, это невозможно сделать с одним запросом к двум таблицам, поэтому я должен использовать следующее:

SELECT  
    c.id,
    c.userId,
    c.friendId,
    m2.message,
    m2.read,
    UNIX_TIMESTAMP(m2.time),      
    user1.username,
    user2.username  
FROM 
    (SELECT MAX(m1.id) AS MessageID 
     FROM usermessages m1 
     GROUP BY m1.conversationId) latest_msg

INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id 
INNER JOIN userconversation c ON m2.conversationId = c.id
INNER JOIN user user1 ON c.userId = user.id
INNER JOIN user user2 ON c.friendId = user.id

WHERE c.userId = :userId OR c.friendId = :userId
ORDER BY m2.id DESC
LIMIT 10

Я просто не думаю, что это самый лучший способ, которым это можно сделать, но не могу придумать, как другие тоже подходят к нему?

Таблица базы данных - это InnoDB для ускорения соединений и улучшения целостности данных, поэтому у меня не может быть двух строк с автоинкрементом.

Есть ли другой способ, которым я мог бы избавиться от таблицы разговоров пользователей и создать уникальный идентификатор, чтобы поместить в столбец разговор? Затем я мог бы просто переместить userId и friendId в сообщения пользователя ... но это создаст много избыточных данных?