Question 41670 in PHP Question posted in
Official manual can be found here.

how to sort users based on the latest chats – PHP

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:
enter image description here

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

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 partitions 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search