MySql SELECT only newest message from distinct threads order by timestamp -Private Messages Inbox Similar to Facebooks(2013)

Question

Trying to recreate a private message system similar to what Facebooks setup these days. The part im having a problem with is SELECT only newest message from distinct threads order by timestamp.

Here is the closest query I could come up with so far. The problem is the inbox should only return one row for each thread and currently my query returns multiple rows from the same thread:

SELECT m.created_on, m.thread_id, m.message_id, m.created_by, m.body, u.first_name, u.last_name, u.thumb_img

FROM pms_messages AS m, pms_recips AS r, users AS u

WHERE r.uid =19

AND r.status

IN ('A', 'N'
)

AND r.thread_id = m.thread_id

AND (SELECT max(r.message_id)) = m.message_id 

AND m.created_by = u.uid

ORDER BY created_on DESC

Here is a img of my current db setup , im open to changes to my tables if it would simplify my querys and get the job done. Further down is more detailed explanation of my tables/the inbox specs.

To be more specific:

*Messages between any two users occurs on a single , ongoing thread. All messages between any 2 users is just a continuation of one ongoing conversation(thread) Even if both users delete all previous messages between each other any future messages will still occur on the same thread.

*individual messages are tracked by message_id.

*Each thread is only displayed once in the inbox at any given time and will display the most recent msg from the the thread.

*Instead of a inbox and a outbox messages being received and sent will be displayed in the same inbox.

So if im User A and I have a message I havent read yet from user B-20 minutes ago, I responded to a msg user C sent me yesterday- 10 minutes ago and another message from user D - 2 minutes ago my inbox should look like this:

Thread with USER D - displaying msg user D sent me.

---

Thread with USER C- - displaying msg I sent to user C

---

Thread with USER B - displaying msg user B sent me

---

*the threads being displayed will be sorted by most recent - to oldest .

Answer 1

JOIN with the table pms_messages with the following:

SELECT thread_id, MAX(Created_ON) AS NewestDate
FROM messages 
GROUP BY thread_id

Then it will remove all the messages but the latest one that has MAX(Created_ON). Like this:

SELECT 
  m.created_on, 
  m.thread_id, 
  m.message_id, 
  m.created_by, 
  m.body, 
  u.first_name, 
  u.last_name, 
  u.thumb_img
FROM pms_messages AS m
INNER JOIN
(
   SELECT thread_id, MAX(Created_ON) AS NewestDate
   FROM messages 
   GROUP BY thread_id
) AS m2  ON m.created_on = m2.Newestdate
        AND m.thread_id  = m2.thread_id
INNER JOIN pms_recips AS r ON r.thread_id  = m.thread_id
INNER JOIN users      AS u ON m.created_by = u.uid
WHERE r.uid =19
  AND r.status IN ('A', 'N')
ORDER BY created_on DESC;

---

Update 1

Add r.message_id = m.message_id to the JOIN condition between the two tables pms_messages, pms_recips. Like this:

SELECT 
  m.created_on, 
  m.thread_id, 
  m.message_id, 
  m.created_by, 
  m.body, 
  u.first_name, 
  u.last_name, 
  u.thumb_img
FROM pms_messages AS m
INNER JOIN
(
   SELECT thread_id, MAX(Created_ON) AS NewestDate
   FROM pms_messages
   GROUP BY thread_id
) AS m2  ON m.created_on = m2.Newestdate
        AND m.thread_id  = m2.thread_id
INNER JOIN pms_recips AS r  ON r.thread_id  = m.thread_id 
                           AND r.message_id = m.message_id
INNER JOIN users      AS u  ON m.created_by = u.uid
WHERE r.uid =19
  AND r.status IN ('A', 'N')
ORDER BY created_on DESC;

SQL Fiddle Demo

Answer 2

    SELECT * FROM 
        (SELECT * FROM messages ORDER BY created_at DESC) messages
    GROUP BY IF(from_user_id < to_user_id, CONCAT_WS('_', from_user_id, to_user_id), CONCAT_WS('_', to_user_id, from_user_id));