Ich habe ein Nachrichtensystem für Benutzer erstellt, mit dem sie eine Nachricht an einen anderen Benutzer senden können. Wenn es das erste Mal ist, dass sie gesprochen haben, wird eine neue Konversation eingeleitet, wenn nicht, wird die alte Konversation fortgesetzt.

Der Posteingang des Benutzers listet alle Gespräche auf, die der Benutzer mit allen anderen Benutzern geführt hat. Diese werden dann nach dem Gespräch sortiert, in dem sich der neueste Beitrag befindet.

Ein Benutzer kann nur eine Konversation mit einem anderen Benutzer führen.

Wenn ein Benutzer auf eine dieser Konversationen klickt, wird eine Seite mit der gesamten Konversation mit den neuesten Beiträgen oben angezeigt. Es ist also eine Art Messaging-Chat-Funktion.

Ich habe zwei Tische:

BenutzergesprächeBenutzermeldungen

Benutzergespräche

Enthält eine Auto-Inkrement-ID, die die Konversations-ID zusammen mit der Benutzer-ID und der Freund-ID ist.

Wer die erste Konversation initiiert, ist immer userId und die friendId des Empfängers. Dies ändert sich dann nie für diese Konversation.

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

Benutzermeldungen

Enthält die spezifischen Nachrichten zusammen mit einem Leseflag, der Uhrzeit und der Konversations-ID

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

Wie es funktioniert

Wenn ein Benutzer eine Nachricht an einen anderen Benutzer sendet, wird eine Abfrage ausgeführt, um zu überprüfen, ob beide Benutzer eine Übereinstimmung in der Benutzerkonversationstabelle habenconversationId wird verwendet und die Konversation wird fortgesetzt, wenn für sie keine neue Zeile mit einem eindeutigen Wert erstellt wirdconversationId.

Wo es kompliziert wird

Bisher ist alles in Ordnung, aber wenn es darum geht, den Posteingang aller Konversationen anzuzeigen, sortiert nach dem neuesten Beitrag, wird es schwierig, mit einer Abfrage fertig zu werden.

Um die Konversationen auflisten zu können, müssen Sie zuerst den neuesten Beitrag jeder Konversation finden. Da Sie jedoch nicht vor einer Gruppe bestellen können, ist dies mit einer Abfrage in zwei Tabellen unmöglich. Daher muss ich Folgendes verwenden:

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

Ich glaube einfach nicht, dass dies der beste Weg ist, aber kann ich mir nicht vorstellen, wie andere es auch angehen?

Die Datenbanktabelle ist InnoDB, um die Verknüpfungen zu beschleunigen und die Datenintegrität zu verbessern, sodass keine zwei Zeilen mit automatischer Inkrementierung zulässig sind.

Gibt es eine andere Möglichkeit, die Benutzerkonversationstabelle zu entfernen und eine eindeutige ID zu erstellen, die in die conversationId-Spalte eingefügt wird? Ich könnte dann einfach die userId und die friendId auf usermessages verschieben ... aber das würde eine Menge redundanter Daten erzeugen?