Get latest conversation with last message per user. (MySQL query)

latest_conversation.sql

SELECT
  *
FROM
  messages,
  (
    SELECT
      MAX(id) as lastid
    FROM
      messages
    WHERE
      (
        messages.to_id = 1 -- ID to compare with (logged in users's ID)
        OR messages.from_id = 1 -- ID to compare with (logged in users's ID)
      )
    GROUP BY
      CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) as conversations
WHERE
  id = conversations.lastid
ORDER BY
  messages.created_at DESC
  
/* Another way using join */

SELECT
	*
  FROM messages m
    INNER JOIN 
  (
      SELECT MAX(id) as lastid
      FROM  messages
        WHERE
          (
            messages.to = 1 -- ID to compare with (logged in users's ID)
			OR 
			messages.from_id = 1 -- ID to compare with (logged in users's ID)
          )
      GROUP BY 
	  CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) conversations 
  ON conversations.lastid = m.id
  
  ORDER BY
  m.created_at DESC

Nice

Great

U saved me ❤️

Here is how you can achieve this within Laravel.
https://gist.github.com/hassanrazadev/767046c36aba528918c63bdb8f47ab25

How can i get the number of unread messages that from that user and the receiver is the current loggedUser

thank you a lot