how to sort users based on the latest chats-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

I would group by the person the chat is with -

<?php
$sql = "
    SELECT 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";

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