php - how to sort users based on the latest chats

Solution:

By using this query:

"SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";
Copy code

• What you are currently doing is querying for all available DMs sent to or from the current user(in session). All DMs mean duplicate entries for the same conversation aka chat
• But what you want to achieve is to list out all unique 'chats' of this user PLUS sort this list based on latest activity on each chat

Correct me if I am wrong.

What you need to do is group the chats based on the similarity of the combination of thesentBy andsentTo columns. This can never be similar if you want to skip duplicates. In addition, you want to order the chats based on thedate column in descending.

From an accepted answer to a mostly similar question I got "one method of doing what you want that uses a correlated subquery, to find the minimum created date/time for a matching conversation".

It also sorts the results based on the date.

So making these corrections and customizing the query from the older question to your context - the above query you used should be changed to(Replacewhere 1 by concatenating the id from session: $_SESSION['id']):

SELECT m.*
FROM dms m
WHERE 1 in (sentBy, sentTo) AND
      m.date = (SELECT MAX(m2.date)
                   FROM dms m2
                   WHERE (m2.sentBy = m.sentBy AND m2.sentTo = m.sentTo) OR
                         (m2.sentBy = m.sentTo AND m2.sentTo = m.sentBy) 
                  )
ORDER BY m.date DESC
Copy code

I run this query on an identical dataset of your dms table and got 2 unique results.

One additional thing you may need to check is the link you want to generate by using the anchor:

echo "<a href='dms.php?talkingTo=".$row2['id']."'>".$row2['username']."</a>";
Copy code

This is echoing out theid of the current user. I think you were supposed to echo out theid of the who they are chatting to? If so you need to run the query to get who it is sent from instead.

Hope this helps.

Solution:

We need to group the dms records based on who the chat is with, knowing that I (id 1) am either the sender or recipient.

If it is sentTo 1 (Me) and sentBy 2 (You) then it is sentBy we are interested in. Conversely, if it is sentTo 2 (You) and sentBy 1 (Me) then it is sentTo we are interested in.

Putting this into SQL we have -

IF(sentTo = 1, sentBy, sentTo)
Copy code

We can now use this in our query of the dms table to group by who we are interacting with and use MAX to find the most recent interaction -

SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
FROM dms
WHERE sentTo = 1 OR sentBy = 1
GROUP BY chatWith
Copy code

In your code you are running an additional query per row returned by the first query. This is not necessary as we can join to the users table -

SELECT u.id, u.username, t.mostRecent
FROM (
    SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
    FROM dms
    WHERE sentTo = 1 OR sentBy = 1
    GROUP BY chatWith
) t
JOIN users u ON t.chatWith = u.id
ORDER BY t.mostRecent DESC
Copy code

Putting all of this into your code we end up with -

<?php

$sql = "
    SELECT u.id, u.username, t.mostRecent
    FROM (
        SELECT IF(sentTo = {$_SESSION['id']}, sentBy, sentTo) chatWith, MAX(date) mostRecent
        FROM dms
        WHERE sentTo = {$_SESSION['id']} OR sentBy = {$_SESSION['id']}
        GROUP BY chatWith
    ) t
    JOIN users u ON t.chatWith = u.id
    ORDER BY t.mostRecent DESC";

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "<a href='dms.php?talkingTo={$row['id']}'>{$row['username']}</a><br>";
    }
} else {
    echo "<p>It's empty</p>";
}
Copy code

I have tested this with 1M randomly (ish) generated rows and it returns consistently in less than 0.02s on my local dev machine.

UPDATE - some test results

EXPLAIN output for each of the three queries with a test dataset of 10k rows - users.id between 1 and 500 and date between 2021-01-01 and current.

/* groupwise max */
EXPLAIN
SELECT u.id, u.username, t.mostRecent
FROM (
    SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
    FROM dms
    WHERE sentTo = 1 OR sentBy = 1
    GROUP BY chatWith
) t
JOIN users u ON t.chatWith = u.id
ORDER BY t.mostRecent DESC;

# Serverside execution time: 0.001s
# Rows examined: 66
# Rows returned: 22
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.