CODEWITHSUNDEEP
phpmysqlsql
Tim
Tim

Reputation: 7056

mysql get last message in conversation

I'm currently creating an inbox of private messages and want to show the last message that was either sent or received between each unique user. I currently have this

SELECT
 Message.id, Message.from, Message.body, Message.isread, Message.timestamp,
 User.id, User.username,
FROM messages as Message 
 LEFT JOIN users as User ON (Message.from = User.id)
    WHERE Message.id IN
     (SELECT MAX(`id`) FROM `messages` 
        WHERE `to` = '.$this->currentUser["id"].'
         OR `from` = '.$this->currentUser["id"].'
    GROUP BY `from`,`to`)
 ORDER BY Message.id DESC;

But it gets both the last sent AND received message, I just want the last message that was sent between the current user and any other one and the user that the conversation is with. 

 `Message`.`id` is auto incremented, indvidual message id.
 `Message`.`from` is the ID of the user the message is from.
 `Message`.`to` is the ID of the user the message us to.

Thanks

Upvotes: 1

Views: 2423

Answers (5)

ajit diwakar
ajit diwakar

Reputation: 1

Try this

SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.from = m2.from AND m1.id < m2.id) WHERE m2.id IS NULL;

Upvotes: 0

Zerus
Zerus

Reputation: 158

understand my answer may come waaay too late, but still. This solved it for me:

SELECT id, user_to, user_from, body, msg_time FROM chat
WHERE id IN (SELECT MAX(id) FROM chat WHERE user_to=:id OR user_from=:id GROUP BY user_to,user_from) ORDER BY msg_time DESC

:id is later on changed to $id var like so (if you use PDO) - 

        $stmt=$this->conn->prepare("SELECT id, user_to, user_from, body, msg_time FROM chat
            WHERE id IN (SELECT MAX(id) FROM chat WHERE user_to=:id OR user_from=:id GROUP BY user_to,user_from) ORDER BY msg_time DESC");
        $stmt->execute(array(":id"=>$id));
        $stmt=$stmt->fetchAll(PDO::FETCH_ASSOC);
        return $stmt;

Hope this helps to those still looking - it took me quite a time ;) Cheers.

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

To find only latest sent message by current user, then

Change: 

WHERE `to` = '.$this->currentUser["id"].'
OR `from` = '.$this->currentUser["id"].'

To: 

WHERE `from` = '.$this->currentUser["id"].'

To find any of the latest sent or received message, then

Change: 

WHERE Message.id IN
 (SELECT MAX(`id`) FROM `messages` 
    WHERE `to` = '.$this->currentUser["id"].'
     OR `from` = '.$this->currentUser["id"].'
GROUP BY `from`,`to`)

To: 

WHERE Message.id =
 (SELECT MAX(`id`) FROM `messages` 
    WHERE `to` = '.$this->currentUser["id"].'
     OR `from` = '.$this->currentUser["id"].'
  GROUP BY `from`,`to` ORDER BY 1 DESC LIMIT 1)

To find only latest sent message by any user, then

Change: 

WHERE Message.id IN
  (SELECT MAX(`id`) FROM `messages` 
     WHERE `to` = '.$this->currentUser["id"].'
      OR `from` = '.$this->currentUser["id"].'
   GROUP BY `from`,`to`)

To: 

WHERE Message.id IN
  (SELECT MAX(`id`) FROM `messages` 
   GROUP BY `from`,`to`)

Upvotes: 0

Brainiac
Brainiac

Reputation: 295

Its actually more simple. If you are using a usefull timestamp-Format (Where the highest inkremental number is the last one, like the time in ms after UNIX-Time, or the format YYYY-MM-DD) you can do it like that:

mysql_query("SELECT
 M.id, M.from, M.body, M.isread, M.timestamp,
 U.id, U.username,
FROM messages M 
LEFT JOIN users U ON (M.from = U.id)
WHERE `to` = $this->currentUser["id"]
 OR `from` = $this->currentUser["id"]
ORDER BY M.timestamp DESC
LIMIT 0 , 1");

Upvotes: 0

user468891
user468891

Reputation: 321

and something simple like this?

SELECT Message.id, Message.from, Message.body, Message.isread, Message.timestamp, User.id, User.username,
FROM messages as Message 
 LEFT JOIN users as User ON (Message.from = User.id)
ORDER BY Message.timestamp DESC
Limit 0, 1;

Upvotes: 0

Related Questions