SQL query for correspondence on the site

There is a messages table in the database. The structure is as follows:

CREATE TABLE `message` (
  `ID_message` bigint(20) NOT NULL auto_increment,
  `ID_user` bigint(20) NOT NULL,
  `ID_user_to` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `is_read_at` datetime default NULL,
  `text` text,
PRIMARY KEY  (`ID_message`)
)

Retrieve all recent conversations for the current user. Let’s say the Id of the user who went to “My Posts” = 5. There are 5 records in the Database with the following id:

ID_user = 5 ID_user_to = 2 created_at = 01/01/2000
ID_user = 5 ID_user_to = 2 created_at = 01/02/2000
ID_user = 3 ID_user_to = 5 created_at = 01/01/2000
ID_user = 2 ID_user_to = 5 created_at = 01/03/2000
ID_user = 5 ID_user_to = 3 created_at = 01/07/2000

So the user should see two lines:
Correspondence with user ID=3 Last message: 01/07/2000, message text
Correspondence with user ID=2 Last message: 01/03/2000, message text

I made a request:

SELECT ID_user,ID_user_to, MAX(created_at)
FROM `message`
WHERE ID_user = 5 OR ID_user_to = 5
GROUP BY ID_user,ID_user_to
ORDER BY created_at DESC

In this example, it will return the lines :

ID_user = 5 ID_user_to = 3 created_at = 01/07/2000
ID_user = 2 ID_user_to = 5 created_at = 01/03/2000
ID_user = 5 ID_user_to = 2 created_at = 01/02/2000
ID_user = 3 ID_user_to = 5 created_at = 01/01/2000

How to make it output:
ID_user = 5 ID_user_to = 3 created_at = 01/07/2000
ID_user = 2 ID_user_to = 5 created_at = 1/3/2000 ?

After all, messages from id=5 for id=2 and from id=2 for id=5 refer to the same correspondence.


Answer 1, authority 100%

  1. Give the entire structure of the table.
  2. Ask a precise and concise question – or better still, give a piece of data in the table and what you want to get as a result of the query.

After all, messages from id=5 for id=2 and from id=2 for id=5 refer to the same correspondence. This is what I expected from you. You need to make one more field – which will indicate that the message belongs to a certain correspondence – and as soon as you do it, everything will fall into place


Answer 2, authority 50%

I felt dizzy while reading the question) If I understood everything correctly, then you need to specify in the request

ID_user != 5

And now the correction:

    :
 this ->     ID=3  : 07.01.2000,  
    ID=2  : 03.01.2000,  

And

 ,     :
 and this ->  ID_user = 5 ID_user_to = 3 created_at = 07.01.2000
 ID_user = 2 ID_user_to = 5 created_at = 03.01.2000 ?

In the course of the question, you are specifying different IDs in two places in the desired result, which is very confusing for the reader of the question!

Leave a reply

Please enter your comment!
Please enter your name here