1

I have a message table :

CREATE TABLE OneToOneMessages (
    MessageID int NOT NULL AUTO_INCREMENT,
    AuthorID int NOT NULL,
    RecipientID int NOT NULL,
    MessageText TEXT,
    SentTime DATETIME,
    PRIMARY KEY (MessageID),
    FOREIGN KEY (AuthorID) REFERENCES Users(ID),
    FOREIGN KEY (RecipientID) REFERENCES Users(ID)
);

I need to get the latest message from every conversation in order. I've found some queries online but I have no idea how they work and am not sure they get the job done.

I think I could just go one by one, getting the last message from each conversation and then sorting them by the sent time. If there was one query I could use that gets all the information, that would be best.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • How do you define a *conversation*? – GMB Sep 17 '19 at 19:24
  • If there is at least one message from user a to user b or vice versa, that is a conversation –  Sep 17 '19 at 19:25
  • See the linked duplicate question. Use `GROUP BY LEAST(AuthorID, RecipientID), GREATEST(AuthorID, RecipientID)` to group by conversation, without regard to which direction the messages were sent. – Barmar Sep 17 '19 at 19:39

1 Answers1

0

You can use a NOT EXISTS condition with a correlated subquery to filter the table. The condition ensures that no other record exists with the same sender/recipient or recipient/sender tuple and a greater send time:

SELECT t.*
FROM OneToOneMessages t
WHERE NOT EXISTS (
    SELECT 1
    FROM OneToOneMessages t1
    WHERE 
        (
            t.AuthorID = t1.AuthorID AND t.RecipientID = t1.RecipientID)
            OR t.RecipientID = t1.AuthorID AND t.AuthorID = t1.RecipientID
        )
        AND t1.SentTime > t.SentTime
)

If you are using MySQL 8.0, this can be done potentially more efficiently using window function ROW_NUMBER():

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(
            PARTITION BY LEAST(AuthorID, RecipientID), GREATEST(AuthorID, RecipientID) 
            ORDER BY SentTime DESC
        ) rn
    FROM OneToOneMessages t
)x 
WHERE rn = 1

The inner query ranks records by descending send time within groups of records having the same sender/recipient or recipient/sender tuple. The outer query filters only on the top record in each group.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • `1` is used to simplify db work. That way engine doesn't have to look any fields on the table. Exists only check if something exists, and `1` is something not null. – Juan Carlos Oropeza Sep 17 '19 at 19:30
  • 1
    I wanted to only get the conversations from a particular user, I just added this before the the not exists: ` (t.AuthorID = $userID OR t.RecipientID = $userID) ` I also wanted to get them by time, so I used an Order By SentTime DESC. Thank you all so much for your help! –  Sep 18 '19 at 14:48