Stworzyłem system wiadomości dla użytkowników, pozwala im wysłać wiadomość do innego użytkownika. Jeśli rozmawiają po raz pierwszy, inicjowana jest nowa rozmowa, jeśli nie, stara rozmowa jest kontynuowana.

Skrzynka odbiorcza użytkowników zawiera listę wszystkich rozmów, które użytkownik przeprowadził ze wszystkimi innymi użytkownikami, a następnie są one sortowane przez rozmowę, która zawiera najnowszy post.

Użytkownik może prowadzić tylko jedną rozmowę z innym użytkownikiem.

Gdy użytkownik kliknie jedną z tych rozmów, zostanie przeniesiony na stronę pokazującą całą rozmowę, którą przeprowadzili z najnowszymi wpisami na górze. Więc jest to trochę jak funkcja czatu w wiadomościach.

Mam dwie tabele:

konwersacja użytkownikawiadomości użytkownika

konwersacja użytkownika

Zawiera identyfikator automatycznej inkrementacji, który jest identyfikatorem konwersacji, wraz z identyfikatorem użytkownika i przyjacielem.

Ktokolwiek zainicjuje pierwszą rozmowę, zawsze będzie userId, a odbiorca friendId, to się nigdy nie zmieni w tej rozmowie.

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

wiadomości użytkownika

Zawiera określone wiadomości wraz z flagą odczytu, czasem i rozmowąId

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

Jak to działa

Gdy użytkownik przejdzie do wysłania wiadomości do innego użytkownika, uruchomi się zapytanie, aby sprawdzić, czy obaj użytkownicy mają zgodność w tabeli konwersji użytkowników, jeśli tak, toconversationId jest używany i rozmowa jest kontynuowana, jeśli nie zostanie utworzony dla nich nowy wiersz z unikalnymconversationId.

Gdzie się komplikuje

Jak dotąd wszystko jest w porządku, jednak jeśli chodzi o wyświetlanie skrzynki odbiorczej wszystkich rozmów posortowanych w najnowszym poście, trudno jest zrobić jedno zapytanie.

Aby móc wymienić rozmowy, musisz najpierw znaleźć najnowszy post każdej konwersacji, ale jak nie możesz zamówić przed grupą, nie da się tego zrobić za pomocą jednego zapytania na dwóch tabelach, więc muszę użyć następujących:

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

Po prostu nie uważam, że jest to najlepszy sposób, w jaki można to zrobić, ale nie potrafię myśleć o innych sposobach, aby się do tego zbliżyć?

Tabela bazy danych to InnoDB, aby przyspieszyć połączenia i poprawić integralność danych, więc nie mogę mieć dwóch wierszy automatycznego przyrostu.

Czy jest inny sposób, w jaki mogę pozbyć się tabeli konwersji użytkowników i utworzyć unikalny identyfikator, który można umieścić w kolumnie rozmowa? Mogłem wtedy po prostu przenieść userId i friendId do wiadomości użytkownika ... ale to spowodowałoby wiele zbędnych danych?