Abfrage, um die eindeutige (letzte in der Zeitachse) Verbindung zwischen 2 IDs in einer Tabelle zu erhalten

Question

Ich habe eine Tabelle "messages", die du im folgenden Bild sehen kannst:

Aus dieser Tabelle ist meine ID als Benutzer 2. Manchmal bin ich Sender, manchmal Empfänger.

Ich benötige eine Abfrage, die nur die letzte Nachricht zurückgibt, die ich mit jedem Benutzer ausgetauscht habe. Egal, ob ich sie gesendet habe oder empfangen habe. Ich bin mir nicht sicher, ob ich hier DISTINCT benutzen sollte. Ich weiß nicht wirklich, wie man es benutzt.

Um es genauer zu erklären, sollte in der obigen Tabelle zum Beispiel Folgendes zurückgegeben werden:

Zeile mit der ID=4 (die letzte Nachricht, die ich mit Benutzer 4 hatte)
Zeile mit der ID=6 (die letzzte Nachricht, die ich mit Benutzer 1 hatte)
Zeile mit der ID=7 (die letzte Nachricht, die ich mit Benutzer 3 hatte)
Zeile mit der ID=5 (die letzte Nachricht, die ich mit Benutzer 7 hatte)
Zeile mit der ID=19 (die letzte Nachricht, die ich mit Benutzer 15 hatte)

Answer 1

Sie können das versuchen:

SELECT * FROM messages WHERE sender_id = 2 OR receiver_id = 2 ORDER BY Id DESC LIMIT 1

Answer 2

SELECT * FROM messages
JOIN (
    SELECT MAX(id) AS id
    FROM messages
    WHERE 2 IN (receiver_id, sender_id) -- sinnlos, wenn die Tabelle wirklich nur Nachrichten zwischen Ihnen und einer anderen Person enthält
    GROUP BY IF(receiver_id = 2, sender_id, receiver_id) -- GRUPPIEREN NACH sender_id ODER receiver_id, je nachdem, an welchem Ende des Austauschs Sie sich befinden (wird sehr schlecht funktionieren)
) AS last_message USING (id)

Nach knittls Kommentar geht obiges davon aus, dass eine monoton steigende id vorliegt (wie z.B. eine auto_increment-Spalte). Wenn dies nicht der Fall ist, müssen Sie zu etwas so Hässlichem wie diesem zurückgreifen:

SELECT * FROM messages
JOIN (
    SELECT MAX(datetime) AS datetime, IF(receiver_id = 2, sender_id, receiver_id) AS other_id
    FROM messages
    WHERE 2 IN (receiver_id, sender_id)
    GROUP BY other_id
) AS last_message ON
    last_message.datetime = messages.datetime
    AND last_message.other_id = IF(receiver_id = 2, sender_id, receiver_id) -- diese Zeile ist überflüssig, wenn nur eine Nachricht innerhalb einer bestimmten Sekunde gesendet oder empfangen werden kann

Answer 3

Das Folgende sollte das machen, was du willst:

SELECT messages.*
FROM (
  SELECT receiver_id, sender_id, MAX(datetime) maxtime
  FROM messages
  WHERE receiver_id = 2
  OR sender_id = 2
  GROUP BY receiver_id, sender_id
) latest
LEFT JOIN messages
ON latest.receiver_id = messages.receiver_id
AND latest.sender_id = messages.sender_id
AND latest.maxtime = messages.datetime

(demo)