Query to get recent messages in a 'threaded' view

Question

Query to get recent messages in a 'threaded' view

I'm trying to rebuild an existing messaging system so it looks similar to Twitter's DM service. Currently I am only showing unread messages to my users, but I would like to show messages sent between 2 users in a single 'thread' like this:

enter image description here

Here is my current database structure and some sample data: http://sqlfiddle.com/#!2/574e3/1/0

I've tried building the query step by step by breaking it down, but I'm confused. This is a list of the steps that I have taken:

1. Get a list of messages sent between two users

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1

2. Show the last message in the 'conversation' between two users

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1
ORDER BY timestamp DESC

3. Group the conversation by user_id (obviously this would be a user's name with a bunch of joins in a production application).

SELECT * FROM uc_user_messages
WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1
GROUP BY uc_user_messages.user_id_1 AND uc_user_messages.user_id_2
ORDER BY timestamp DESC

And this is where I'm stuck... The problem is that the first message sent in a conversation between two users will be shown, instead of the most recent message: http://sqlfiddle.com/#!2/942fb/2

What can I do about this? Hopefully I won't have to make any major database design changes. Any help is greatly appreciated.

Cheers,

Joel

php
mysql
node.js

Answer 1

SELECT  *
FROM    uc_user_messages
WHERE   (LEAST(user_id_1, user_id_2),GREATEST(user_id_1, user_id_2),TIMESTAMP) 
        IN 
        (
            SELECT  LEAST(user_id_1, user_id_2) AS x,
                    GREATEST(user_id_1, user_id_2) AS y,
                    max(TIMESTAMP) AS msg_time
            FROM    uc_user_messages
            GROUP   BY x, y
        );

SQLFiddle Demo

OUTPUT

╔════╦═══════════╦═══════════╦═════════════════╦════════════╦══════╗
║ ID ║ USER_ID_1 ║ USER_ID_2 ║     MESSAGE     ║ TIMESTAMP  ║ READ ║
╠════╬═══════════╬═══════════╬═════════════════╬════════════╬══════╣
║  3 ║         1 ║         2 ║ third message   ║ 1366577336 ║    1 ║
║  4 ║         2 ║         0 ║ a message reply ║ 1366577346 ║    1 ║
╚════╩═══════════╩═══════════╩═════════════════╩════════════╩══════╝

Answer 2

Assuming I've understood you correctly, and you're looking for the most recent message between users 1 and 2, then this is one way to do it.

SELECT * FROM uc_user_messages
WHERE id = (
  SELECT MAX(id) FROM uc_user_messages
  WHERE uc_user_messages.user_id_1 = 1 OR uc_user_messages.user_id_2 = 1
)

This relies on the id being ascending though, which may not be a great idea. An alternative would be to use the timestamp, but that would only work if it could be guaranteed to be unique for those users.