Gruppieren von Nachrichten nach der letzten Antwort im Konversations-Threading

Question

Ich brauche ein einfaches internes Nachrichtensystem zwischen Benutzern.

Meine Tische:

+--------------+         +---------------------+
|   messages   |         |        users        |
+----+---------+         +---------------------+
| id | message |         | id | username | ... 
+----+---------+         +---------------------+

+------------------------------------------------------------------------------+
|                                 users_messages                               |
+------------------------------------------------------------------------------+
| id | from_usr_id | to_usr_id | msg_id | thread_id | read | sent_at | read_at |
+------------------------------------------------------------------------------+

INT 'thread_id' stellt den Gesprächsfaden dar und dient der Gruppierung von Nachrichten.

BOOLEAN 'read' gibt an, ob der Nutzer die Nachricht geöffnet/angesehen hat oder nicht.

Ich möchte Nachrichten gruppieren nach 'thread_id' , sortiert nach 'sent_at' damit ich dem Benutzer seine letzten Nachrichten nach Thema anzeigen kann. Ich möchte auch die Nachrichten in jedem Thema zählen.

Ich möchte etwas Ähnliches für eine bestimmte Benutzerkennung erhalten:

+----------------------------------------------------------------------------
| last_messages_by_conversation
+----------------------------------------------------------------------------
| message | from_username | sent_at | count_thread_msgs | count_unread_msg |
+----------------------------------------------------------------------------

TEXT 'message' ist die letzte Nachricht in der spezifischen 'thread_id'

VARCHAR 'from_username' y DATETIME 'sent_at' sich auf die letzte Nachricht beziehen.

INT 'count_thread_msgs' y INT 'count_unread_msg' beziehen sich auf den Thread und geben die Gesamtzahl der Nachrichten und die Anzahl der ungelesenen Nachrichten im Thread an.

Jede Zeile steht für einen Thread/eine Konversation (gruppiert nach 'thread_id' ), die die letzte Nachricht anzeigt (sortiert nach 'sent_at' ) für diesen speziellen Thread.

Answer 1

Sie suchen nach dem gruppenweises Maximum gefunden werden, indem man zunächst die users_messages Tabelle von thread_id und die Auswahl MAX(sent_at) und fügt dann das Ergebnis wieder in die users_messages Tabelle, um die anderen Felder dieses Maximaldatensatzes zu finden.

Ich finde, dass NATURAL JOIN ist hier eine sehr praktische Abkürzung:

SELECT   messages.message,
         users.username AS from_username,
         t.sent_at,
         t.count_thread_msgs,
         t.count_unread_msg
FROM     users_messages NATURAL JOIN (
  SELECT   thread_id,
           to_usr_id,
           MAX(sent_at)  AS sent_at,
           COUNT(*)      AS count_thread_msgs,
           SUM(NOT read) AS count_unread_msg
  FROM     users_messages
  WHERE    to_usr_id = ?
  GROUP BY thread_id
) t JOIN messages ON messages.id = users_messages.msg_id
    JOIN users    ON users.id    = users_messages.from_usr_id

Answer 2

SELECT 
      users.id,
      users.username,
      user_messages.thread_id,
      user_messages.unread ,
      messages.message 
FROM users
LEFT JOIN (SELECT 
                 from_usr_id , 
                 msg_id,
                 count(thread_id)) as thread_id,
                 count(read_at) as  unread  
          FROM user_messages)as user_messages on user_messages.from_usr_id = users.id
LEFT JOIN messages on messages.id = user_messages.msg_id

Answer 3

Sie können diese Lösung ausprobieren:

SELECT   c.message,
         d.username AS from_username,
         b.sent_at,
         a.count_thread_msgs,
         a.count_unread_msg
FROM     (
         SELECT   MAX(id)  AS maxid,
                  COUNT(*) AS count_thread_msgs,
                  COUNT(CASE WHEN `read` = 0 AND <uid> = to_usr_id THEN 1 END) AS count_unread_msg
         FROM     users_messages
         WHERE    <uid> IN (from_usr_id, to_usr_id)
         GROUP BY thread_id
         ) a
JOIN     users_messages b ON a.maxid       = b.id
JOIN     messages c       ON b.msg_id      = c.id
JOIN     users d          ON b.from_usr_id = d.id
ORDER BY b.sent_at DESC

---

Damit wird die letzte Nachricht in jedem Thread abgerufen, den der Benutzer <uid> begonnen hat oder daran beteiligt ist.

Die letzte Meldung basiert auf der höchsten id der einzelnen thread_id.

Diese Lösung geht von folgenden Annahmen aus:

• Les id in users_messages ist ein eindeutiger, automatisch inkrementierender Wert für jede neue Zeile.
• Jeder Thread enthält Korrespondenz zwischen nie mehr als zwei Benutzern.

Wenn der Thread mehr als zwei Benutzer enthalten kann, muss die Abfrage leicht angepasst werden, um eine genaue Zählung zu erhalten.

Answer 4

Probieren Sie es aus und lassen Sie es mich wissen, ändern Sie $$ für Ihre user ID ..

select u.username,msg.message,m.sent_at,

(select count(*) from user_message where read=0 and to_usr_id=$$) as count_thread_msgs,

(select count(*) from user_message where to_usr_id= $$) as count_unread_msg

from users as u join user_messages as m

on u.id=m.id where u.id=$$ 

join messages as msg on msg.id=m.id

group by u.id;`

Answer 5

Versuchen Sie diese Abfrage -

SELECT
  m.message,
  u.username from_username,
  um1.sent_at,
  um2.count_thread_msgs,
  um2.count_unread_msg
FROM users_messages um1
  JOIN (
       SELECT
         thread_id,
          MAX(sent_at) sent_at,
          COUNT(*) count_thread_msgs,
          COUNT(IF(`read` = 1, `read`, NULL)) count_unread_msg
        FROM users_messages GROUP BY thread_id) um2
    ON um1.thread_id = um2.thread_id AND um1.sent_at = um2.sent_at
JOIN messages m
  ON m.id = um1.msg_id
JOIN users u
  ON u.id = um1.from_usr_id
-- WHERE u.id = 100 -- specify user id here

Antworten auf Ihre Fragen:

1. Zum letzten Termin: Ich habe die Abfrage ein wenig geändert, versuchen Sie einfach eine neue.
2. Über bestimmte Benutzer: WHERE-Bedingung hinzufügen, um Benutzer zu filtern - ...WHERE u.id = 100 .
3. Über viele Datensätze: weil Sie eine andere Tabelle verbinden ( messages y users ), und es kann mehr als einen Datensatz mit demselben thread_id . Um dies zu vermeiden, sollten Sie die Ergebnismenge nach thread_id Feld und verwenden Sie die Aggregatfunktion, um ein einzelnes Ergebnis zu erhalten, z. B. mit der Funktion GROUP_CONCAT.