Mysql order by before group by

[sean04]

I have a table as follows:

 

id    reply_id    to_user    from_user    message    date_sent

------------------------------------------------------------------------

1    0              345          223            hey            2012-06-01 12:33:23

2    1              223          345            hey man    2012-06-01 12:37:23

3    1              345          223            how r u?    2012-06-01 12:45:23

4    0              223          345            sup            2012-06-01 13:17:23

5    4              345          223            msg            2012-06-01 13:37:23

6    4              223          345            msg2          2012-06-01 13:58:23

7    0              345          222            test            2012-06-02 09:33:23

 

How do I show the most recent message in a conversation and not all of them? Inbox should look like this...

 

test            2012-06-02 09:33:23

how r u?    2012-06-01 12:45:23

msg2          2012-06-01 13:58:23

 

I've gotten close but I run into difficulties because any message with a reply_id of 0 needs to show up ONLY IF its never been replied to. If it has been replied to it shouldn't show up.

 

Thanks in advance!

[cpd]

I'm not convinced your database structure is the best for what your trying to achieve.

 

Can you clarify what it is you want to do?

 

1) A continuous messaging system where messages are just appended. 2) Individual conversations even if the recipient and origin users are the same. Every time you click a "New Message" button a new conversation is started. 3) Similar to 2 but instead of showing all messages you just display the reply to your previous message?

 

 

[sean04]

Thanks for the response.

 

Yes, I'm trying to make a continuous message system. When a new message is started it has a reply id of 0. A person can then reply to that message. Pretty much trying to attempt what Facebook does. One message should show in the inbox and that should be the last reply to you. When you click on the message you can then see all the conversation messages.

 

I was pretty sure this is the way to do it but if there is another way please explain.

 

Thanks for the help!

[sean04]

The problem is is that any message the doesn't have a reply to it yet has to show up and messages that have a reply to it the most recent has to show up. Any ideas?

 

Thanks!

[fenway]

The problem is is that any message the doesn't have a reply to it yet has to show up and messages that have a reply to it the most recent has to show up. Any ideas?

 

Thanks!

Without the query, we can't possibly help.

[Barand]

try

SELECT m.message, m.date_sent
FROM message m
INNER JOIN (
    SELECT reply_id, MAX(date_sent) as lastdate
    FROM message
    GROUP BY reply_id
) as x
ON m.reply_id = x.reply_id AND m.date_sent = x.lastdate
ORDER BY date_sent DESC;

[Barand]

Test data :That data for which the program works.

 

Forget my last query. It is a clear example of the above definition.

 

SELECT m.*
FROM message m
INNER JOIN (
    SELECT reply_id, MAX(date_sent) as lastdate
    FROM message
    GROUP BY reply_id
    ) as x
ON m.reply_id = x.reply_id AND m.date_sent = x.lastdate
WHERE m.reply_id <> 0
UNION 
SELECT m.*
FROM message m
LEFT JOIN message m2 ON m.id = m2.reply_id
WHERE m.reply_id = 0 AND m2.reply_id IS NULL
ORDER BY date_sent DESC;

[sean04]

Thanks for the help Barand. I appreciate it.

 

The query works great!

 

In your opinion though is this the right way to approach this? Should I have two different tables one for the start of new messages and one that holds the conversations if you reply? Then I can just join those two tables.

 

Thanks!

[sean04]

Hmm. I've run into a small issue. It seems that when I then reply to a message, that conversation then disappears. I think not having 'where to_user = $this->user_id' in the query causes this?