CODEWITHSUNDEEP
phpmysqlsqlgroup-by
Freddy
Freddy

Reputation: 349

messages between users: show conversations

I am coding a message system for user communication. In the inbox I do not want to show the user the messages he/she received. I just want to show the conversations. So as an example, if one user send or receive more than one message then in the inbox there should only be the conversation (which includes the newest message, either written or received) with the user and when the user clicks on the conversation he/she can see all past messages.

The table structure (simplified) of 'messages' is as followed:

message_id
user_id_sender
user_id_recipient
message

Now the problem is that the messages are saved in a database where each row is one message, so I have to group these messages in a certain way.

The select statement I came up with is the following:

SELECT * FROM messages
WHERE user_id_sender = 1 OR user_id_recipient = 1
GROUP BY user_id_sender

But now I obviously get two messages because one which has been written by user '1' and one that he has received..

Does anybody have an idea how to solve this?

Upvotes: 1

Views: 2302

Answers (4)

Luca Rainone
Luca Rainone

Reputation: 16468

I've solved this problem some month ago. I suppose you have also a date field. This query give you a well structured results with date of last message and last message.

$qry = 'SELECT 
CONCAT(GREATEST(user_id_sender,user_id_recipient)," ",LEAST(user_id_sender,user_id_recipient)) AS thread, 
MAX(CONCAT(date,"|",message)) as date_message, 
MAX(date) AS last_message, 
messages.* 
     FROM messages
     WHERE user_id_sender= ? || user_id_recipient=? GROUP BY thread ORDER BY last_message DESC';

$rows = $db->fetchAll($qry, Array($current_user_id,$current_user_id));

Upvotes: 2

Laurence
Laurence

Reputation: 10976

Assuming message_id is ascending (i.e. higher ids are for later messages). @user_id is just a placeholder for the user_id of the inbox you are looking at. I've used Andrea's trick for getting the other_recipient_id concisely.

Select 
  mm.other_recipient_id,
  m.*
From (
  Select
    user_id_sender + user_id_recipient - @user_id as other_recipient_id,
    Max(message_id) as message_id
  From
    messages
  Where
    user_id_sender = @user_id Or
    user_id_recipient = @user_id
  Group By
    user_id_sender + user_id_recipient - @user_id
  ) mm
    Inner Join
  messages m
    On
  mm.message_id = m.message_id

Example fiddle http://sqlfiddle.com/#!2/05d191/3/0

Upvotes: 2

Andrea
Andrea

Reputation: 396

Assuming we want to see conversations of user with certain _ ID _, this query can be useful:

SELECT (user_id_sender + user_id_recipient) - _ID_ AS correspondent, COUNT(*) AS total
FROM messages
WHERE user_id_sender = _ID_ OR user_id_recipient = _ID_
GROUP BY (user_id_sender + user_id_recipient)

The resulting query returns the ID of the other user of the conversation ("correspondent") and the number of messages between the two users ("total").

Regards

Upvotes: 0

Olaf Dietsche
Olaf Dietsche

Reputation: 74118

You get only one message, as long as the messages (message_id) are unique. You will get multiple messages, of course, if there is a longer communication going on.

N.B.: You don't need group by here, because this is used only for aggregating columns, e.g.:

SELECT user_id_sender, sum(*) FROM messages
GROUP BY user_id_sender;

where you get the sum of messages each user has sent.

So, if you want to see the communication between two users:

SELECT * FROM messages
WHERE user_id_sender = 1 OR user_id_recipient = 1;

You can restrict this further, if you store the timestamps as well, message_time for example or limit the number of messages displayed:

select * from ... limit 10;

Upvotes: 0

Related Questions