SELECT
c.title,
ou.name,
m.text,
FROM_UNIXTIME(m.date) AS dt
FROM chat_users cu
INNER JOIN chat_users cuu ON cuu.chat_id = cu.chat_id AND cuu.user_id != cu.user_id
INNER JOIN users ou ON ou.id = cuu.user_id
INNER JOIN chat c ON c.id = cu.chat_id
INNER JOIN messages m ON m.user_id = cuu.user_id AND m.chat_id = cu.chat_id
WHERE cu.user_id = :ID_USER -- ID пользователя по которому выбирать чаты.
ORDER BY c.title ASC, m.date DESC