He creado un sistema de mensajería para usuarios, que les permite enviar un mensaje a otro usuario. Si es la primera vez que hablan, se inicia una nueva conversación, si no, la conversación anterior continúa.

La bandeja de entrada de los usuarios enumera todas las conversaciones que el usuario ha tenido con todos los demás usuarios, que luego son ordenadas por la conversación que tiene la última publicación.

Un usuario solo puede tener una conversación con otro usuario.

Cuando un usuario hace clic en una de estas conversaciones, pasa a una página que muestra toda la conversación que han tenido con las publicaciones más recientes en la parte superior. Así que es como una funcionalidad de chat de mensajería.

Tengo dos mesas:

userconversationmensajes de uso

userconversation

Contiene un ID de incremento automático que es el ID de conversación, junto con el ID de usuario y el ID de amigo.

Quienquiera que inicie la primera conversación siempre será userId y el destinatario friendId, esto nunca cambiará para esa conversación.

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

mensajes de uso

Contiene los mensajes específicos, junto con un indicador de lectura, el tiempo y la conversación.

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

Cómo funciona

Cuando un usuario va a enviar un mensaje a otro usuario, se ejecutará una consulta para verificar si ambos usuarios tienen una coincidencia en la tabla de conversión de usuarios, si es queconversationId se utiliza y la conversación continúa, si no se crea una nueva fila para ellos con un únicoconversationId.

Donde se complica

Hasta ahora todo está bien, sin embargo, cuando se trata de mostrar el buzón de entrada de todas las conversaciones, ordenado en la última publicación, es difícil hacerlo con una sola consulta ...

Para poder enumerar las conversaciones, primero debe encontrar la última publicación de cada conversación, pero como no puede ordenar antes de un grupo, es imposible hacerlo con una consulta en dos tablas, así que tengo que usar lo siguiente:

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

Simplemente no creo que esta sea la mejor manera de hacerlo, pero ¿no puedo pensar en otras maneras en las que podemos enfocarlo?

La tabla de la base de datos es InnoDB para acelerar las uniones y mejorar la integridad de los datos, por lo que no puedo tener dos filas de incremento automático.

¿Hay alguna otra manera de deshacerme de la tabla de conversión de usuarios y crear una identificación única para colocarla en la columna conversationId? Entonces podría simplemente mover userId y friendId a los mensajes de usuario ... ¿pero esto crearía una gran cantidad de datos redundantes?