A
A
Anna2016-08-04 11:29:45
MySQL
Anna, 2016-08-04 11:29:45

Dinstint by condition, perhaps?

There is a table of private messages
Generally
id - id - messages
time - time
text -text
sender - sender
receiver - receiver
s_del - deleted message sender
r_del - deleted message receiver
The problem is that I need to display dialogs. That is, one last message between users.
For example, your user_id is 1. I get a condition where sender = 1 or receiver=1
I get

SELECT * FROM `messages` WHERE `sender`=1 or `receiver`=1 order by id DESC

In general, I sit for an hour in the head of the manual does not come to mind. At least give me a direction... Thanks in advance

Answer the question

In order to leave comments, you need to log in

3 answer(s)
K
Konstantin Tsvetkov, 2016-08-04
@tsklab

SELECT 'test' FROM `messages` WHERE `sender`=1 or `receiver`=1 order by time DESC

Subquery:
The principle of dialogues in VK
Until you bring (more important even for yourself) the task in terms of the database, nothing will work.

M
Maxim Fedorov, 2016-08-04
@qonand

SELECT * FROM messages WHERE id IN (SELECT max(id) FROM messages WHERE `sender`=1 or `receiver`=1 GROUP BY sender, receiver)

R
Risent Veber, 2016-08-04
@risentveber

The condition of sorting by creation time is equivalent to the condition of sorting by id, only the latter is generally faster.

SELECT *
FROM 'messages' 
WHERE ID IN (
  SELECT max(id) 
  FROM `messages` 
  WHERE `sender`=1 or `receiver`=1 
  GROUP BY `sender`, `reciever`
);

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question