How to take query repetition

Asked

Viewed 118 times

0

I created a chat, and I have a query to display the other accounts of the site to start a conversation. I put a select all, and it displays all normally, but now I need to put an order by to sort them according to the most recent messages, so I had to put an Inner Join with the message table.

My select is like this: 

SELECT contas.Key_User, contas.Login_User, contas.User_Image, chat.Key_User1, chat.Key_User2, chat.Date 
FROM contas 
INNER JOIN chat 
ON contas.Key_User = chat.Key_User1 OR contas.Key_User = chat.Key_User2 OR contas.Key_User > 0 
WHERE `Key_User` not like '".$_SESSION['Key_User']."'
ORDER BY chat.Date DESC

The Where is not to show the user’s own account. However, with Inner Join, now it repeats the accounts for every message there is of it, so if the user has ten messages in all, will appear the profile ten times, and wanted to limit to appear only once, or another way to give an order by the date of the message.

I tried to use distinct, but I could not fit the group by.

chat.Key_User1: Key_User do usuário logado
chat.Key_User2: Key_User do usuário que vai receber a mensagem.
php mysql sql

1 answer

3


You can use aggregation functions with GROUP BY to group lines with the same fields and limit to the latest date using MAX.

SELECT
    contas.Key_User,
    contas.Login_User,
    contas.User_Image,
    chat.Key_User1,
    chat.Key_User2,
    MAX(chat.Date) -- Esta linha limita os resultados a data mais recente
FROM contas

INNER JOIN chat ON (
    contas.Key_User = chat.Key_User1
    OR contas.Key_User = chat.Key_User2
    OR contas.Key_User > 0
)

WHERE `Key_User` not like '".$_SESSION['Key_User']."'

GROUP BY contas.Key_User,
    contas.Login_User,
    contas.User_Image,
    chat.Key_User1,
    chat.Key_User2

ORDER BY chat.Date DESC

Like the field chat.Key_User2 is not in any aggregation function there will be lines with repeated users but with different recipients, if you want there to be only one line per user use an aggregation function in it or remove the field chat.Key_User2.

Link about aggregation functions: https://www.devmedia.com.br/sql-funcoes-de-agregacao/38463

  • It worked! Actually, I was still repeating, but only twice now, and I was able to solve it by taking out the chat.Key_user1 and chat.Key_user2 from GROUP BY. And I removed the chat.Key_user2 from SELECT as well, as you suggested, and it was perfect.

  • But for some reason, ORDER BY isn’t working. Whenever I give F5, it changes the order of the users, but it shouldn’t happen because there are no new messages. You know what could be?

  • 1

    It may occur that two users have a conversation at the same time, as the ordering is only by chat.Date it uses an internal criterion to sort data with the same date, one option is to add a secondary criterion in the GROUP BY thus: ORDER BY chat.Date DESC, contas.Key_User

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.