Could someone review an accepted answered I gave on Stack Overflow?
The use-case is as follows:
Given a messaging system where a user can receive a message from a single user and send messages to one or more users, return the most recent communication (sent or received) between a passed userId and the individual(s) that user communicated with.
For the example, I have three tables:
id user_name 1 Walker 2 John 3 Kate
id senderid body time 1 1 ignored 1 2010-04-01 00:00:00.000 2 1 ignored 2 2010-04-02 00:00:00.000 3 3 ignored 3 2010-04-03 00:00:00.000 4 1 msg A to john and kate 2010-04-10 00:00:00.000 5 3 msg b from kate to walker and john 2010-04-11 00:00:00.000
id messageid userid 1 1 2 2 1 3 3 2 2 4 3 1 5 4 2 6 4 3 7 5 1 8 5 2
The data is tailored in such a way that I want a list of communications between user Walker and the people Walker has spoken with.
You can see a list of these messages by running the following SQL statement:
SELECT u2.user_name AS Sender, u1.user_name AS Receiver, m.body, m.time FROM messages m JOIN messages_recipients mr ON m.id = mr.messageid JOIN users u1 ON mr.userid = u1.id JOIN users u2 ON m.senderid = u2.id ORDER BY time DESC
Now that we have the test scenario, the part I want reviewed: returning the most recently communicated message between Walker, John, and Kate.
BEGIN DECLARE @UserId INT = 1 --A. Main Query SELECT CASE WHEN mtemp.senderid = 1 --@UserId THEN CONCAT('Message To: ', receivers.user_name) ELSE CONCAT('Message From: ' , senders.user_name) END AS MessageType, mtemp.body, mtemp.time FROM messages mtemp INNER JOIN users senders ON mtemp.senderid = senders.id INNER JOIN ( --B. Inner Query determining most recent message (based on time) -- between @UserID and the person @UserID -- Communicated with (either as sender or receiver) select userid,max(maxtime) as maxmaxtime from ( --C.1. First part of Union Query Aggregating sent/received messages on passed @UserId SELECT m2.body, kk.* FROM `messages` m2 INNER JOIN ( SELECT DISTINCT userid, MAX(m.time) AS MaxTime FROM messages m INNER JOIN messages_recipients mr ON m.id = mr.messageid AND m.senderid = 1 --@UserId GROUP BY mr.userid ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId UNION --C.2. Second part of Union Query Aggregating sent/received messages on passed @UserId SELECT m1.body, jj.* FROM `messages` m1 INNER JOIN ----C.2a. Inner most query of users users who sent message to userid (SELECT DISTINCT senderid as userid, MAX(m.time) AS MaxTime FROM messages m INNER JOIN messages_recipients mr ON m.id = mr.messageid AND mr.userid = 1 --@UserId GROUP BY m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid ) MaximumUserTime group by MaximumUserTime.userid ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime INNER JOIN users receivers on AggregatedData.userid = receivers.id ORDER BY `time` DESC END
To test in
phpMyAdmin, you'll have to remove the comments and the
DECLARE statements as well. I just wanted to post this as if it would look in a procedure.
When I run this query I get the following results:
MessageType body time Message From: Kate msg b from kate to walker and john 2010-04-11 00:00:00.000 Message To: John msg A to john and kate 2010-04-10 00:00:00.000
That's the most recent communications concerning Walker among all those users who have communicated with Walker.
Is there a better way to run this query?