Make an ORDER in Mysql from another TABLE

Question

I was wondering if it’s possible on a mysql_query search and ORDER HER according to more recent updates from another table for example..

I have this query, when the user logs into the chat it gives a list of all registered users, but just shows what your friends are added.

$sqln = mysql_query("SELECT * FROM users 
                     WHERE (id = '$id') 
                     OR (id IN (SELECT user2_id FROM following WHERE user1_id='$id'))
                     ORDER BY id ASC
                    ");

But I would like to know if instead of searching in the users table, listing ASC ordained by id, search in another table chat

where he would find the person’s id, (id_de), and would bring back the latest messages to get something more up-to-date, listing first the most recent conversations.. as in applications like WHATSAPP.

It seemed?

Answer 1

SELECT chat.*,
       users.* 
       FROM chat
       INNER JOIN users ON(users.id=chat.id_de)
       WHERE chat.id_de = '$id_pessoa'
       ORDER BY chat.data DESC

Answer 2

As it comes to two tables, you would have to make one JOIN. I believe you can do it this way:

SELECT users.* FROM users
JOIN chat ON chat.id_de = users.id 
WHERE (id = '$id') 
OR (id IN (SELECT user2_id FROM following WHERE user1_id='$id'))
GROUP BY chat.id_de
ORDER BY chat.data DESC, chat.hora DESC

In that case, I used the GROUP BY not to duplicate users, since the table chat could have more than one message per person

Answer 3

I was going to post a reply similar to @Wallace, but after some tests here I saw that it wouldn’t work because Order is done after Group By, to solve you have to use the Max() function to get the last message from each user and then Order By, more or less like this:

SELECT u.id, u.nome, MAX(c.data_hora) AS last_msg FROM chat c 
INNER JOIN users u ON c.id_para = u.id
WHERE c.id_de = 1
GROUP BY c.id_para ORDER BY last_msg DESC;

Will list all users who have had some conversation with the user in question (as in Whatsapp) with the correct ordering, just make the modifications you need.

(In my test I joined the date and time in a single Timestamp column)