how to sort users based on the latest chats

Question

i have a chat website and i want the user to have a list available of users sorted by who they last chatted with(like whatsapp). how do i do this? i tried many stack overflow answers but none of them worked for me so far. when using the code i use now the names of the users repeat for every message that exists. this query isn't working: "SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";" this is what my database looks like:

this is my code:

<?php
                $sql = "SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";";
                $result = mysqli_query($conn, $sql);
                if (mysqli_num_rows($result) > 0) {
                    while ($row = mysqli_fetch_assoc($result)) {
                        $sql2 = "SELECT id, username FROM users WHERE id = ".$row['sentTo'].";";
                        $result2 = mysqli_query($conn, $sql2);
                        if (mysqli_num_rows($result2) > 0) {
                            while ($row2 = mysqli_fetch_assoc($result2)) {
                                echo "<a href='dms.php?talkingTo=".$row2['id']."'>".$row2['username']."</a>";
                            }
                        }else{
                            echo "<p>It's empty</p>";
                        }
                    }
                }else{
                    echo "<p>It's empty</p>";
                }
            ?>

@KenLee then it would sort the messages when i need to sort the users based on the messages — cavia cavia, Nov 15 '22 at 17:58
@KenLee when i do that the list just flips, it re orders. but not based on who was last sent a message. — cavia cavia, Nov 15 '22 at 18:03
@KenLee yes that is what in trying to do. i do not understand how ordering the date in descending order would help. — cavia cavia, Nov 15 '22 at 18:07
**Hint:** Think on how whatsapp (or similar) does it - it is normal that the user record will have a data field storing the "last datetime" of activities — Ken Lee, Nov 15 '22 at 18:09
@KenLee by sorting the users based on the last sent message? — cavia cavia, Nov 15 '22 at 18:10
@KenLee yes, i could store it but i dont know how with the way my database was made and there must be another way. — cavia cavia, Nov 15 '22 at 18:14

Billion Shiferaw · Accepted Answer · 2022-11-15T19:43:59.340

By using this query:

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

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 the sentBy and sentTo columns. This can never be similar if you want to skip duplicates. In addition, you want to order the chats based on the date 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(Replace where 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

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>";

This is echoing out the id of the current user. I think you were supposed to echo out the id 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.

it's not working for me so im probably still doing something wrong, but ill accept the answer because its the most helpful. thank you so much! edit: its working now idk how thank you again, so much! — cavia cavia, Nov 15 '22 at 20:15

user1191247 · Answer 2 · 2022-11-16T12:16:26.967

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)

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

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

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>";
}

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		ALL					43	100.00	Using filesort
1	PRIMARY	u	eq_ref	PRIMARY	PRIMARY	2	t.chatWith	1	100.00	Using where
2	DERIVED	dms	index_merge	IDX_from,IDX_to,IDX_from_to,IDX_to_from	IDX_to,IDX_from	4,4		43	100.00	Using union(IDX_to,IDX_from); Using where; Using temporary

/* correlated subquery 1 */
EXPLAIN
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;

# Serverside execution time: 0.122s
# Rows examined: 440022
# Rows returned: 22

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	m		ALL					9847	100.00	Using where; Using filesort
2	DEPENDENT SUBQUERY	m2		ALL	IDX_from,IDX_to,IDX_from_to,IDX_to_from				9847	1.99	Range checked for each record (index map: 0x1E)

/* correlated subquery 2 */
EXPLAIN
SELECT m.*
FROM dms m
WHERE (sentTo = 1 OR sentBy = 1) 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;

# Serverside execution time: 0.122s
# Rows examined: 430022
# Rows returned: 22

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	m		index_merge	IDX_from,IDX_to,IDX_from_to,IDX_to_from	IDX_to,IDX_from	4,4		43	100.00	Using union(IDX_to,IDX_from); Using where; Using filesort
2	DEPENDENT SUBQUERY	m2		ALL	IDX_from,IDX_to,IDX_from_to,IDX_to_from				9847	1.99	Range checked for each record (index map: 0x1E)

Summary of performance observations based on 10k, 100k & 1M datasets

10k	Groupwise max	Correlated subquery 1	Correlated subquery 2
Serverside execution time (s)	0.001	0.119	0.115
Rows examined	63	420,021	410,021
Rows returned	21	21	21

100k	Groupwise max	Correlated subquery 1	Correlated subquery 2
Serverside execution time (s)	0.003	11	10
Rows examined	597	38,900,199	38,800,199
Rows returned	199	199	199

1M	Groupwise max	Correlated subquery 1	Correlated subquery 2
Serverside execution time (s)	0.018	1072	1046
Rows examined	1,497	3,999,000,499	3,998,000,499
Rows returned	499	499	499

how to sort users based on the latest chats

2 Answers2