5

I have a private messages table on my site and for a while now I have had an inbox and sentbox separate. I want to combine the inbox/sentbox only showing the latest message either to or from a specific user.

TLDR: I want to show the latest message grouped by sent to or from each user.

Example of table

|  id | fromuser | fromid | touser | toid | message    |  timestamp            |
--------------------------------------------------------------------------------
|  1  | user1    |  1     | user2  |  2   | Hello..    |  2015-01-01 00:00:00  |
|  2  | user1    |  1     | user3  |  3   | okay...    |  2015-01-02 00:00:00  |
|  3  | user3    |  3     | user1  |  1   | not....    |  2015-01-03 00:00:00  |
|  4  | user2    |  2     | user3  |  3   | New....    |  2015-01-04 00:00:00  |
|  5  | user2    |  2     | user1  |  1   | With.....  |  2015-01-05 00:00:00  |
--------------------------------------------------------------------------------

Result i'm looking for when in user 1

|  id | fromuser | fromid | touser | toid | message    |  timestamp            |
--------------------------------------------------------------------------------
|  3  | user3    |  3     | user1  |  1   | not....    |  2015-01-03 00:00:00  |
|  5  | user2    |  2     | user1  |  1   | With.....  |  2015-01-05 00:00:00  |
--------------------------------------------------------------------------------

Using the code below I can get it to show one message to or from each user but it shows the oldest message not the newest.

mysql_query("SELECT m1.*, users.id AS userid, users.username 
    FROM pm AS m1, pm AS m2, users 
    WHERE ((m1.fromuser='".$_SESSION['userName']."' AND users.id=m1.toid) 
    OR (m1.touser='".$_SESSION['userName']."' AND users.id=m1.fromid)) 
    AND m2.id=m1.id ORDER BY timestamp DESC");
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
David Wright
  • 53
  • 3
  • Why am i not seeing any joins between your tables? how about joining the tables and using limit function? – Krish Apr 22 '15 at 14:14
  • possible duplicate of [How to get the latest record in each group using GROUP BY?](http://stackoverflow.com/questions/10999522/how-to-get-the-latest-record-in-each-group-using-group-by) – Sean Apr 22 '15 at 14:35
  • had a look at that link and that will return all messages, not just messages to or from user logged in. – David Wright Apr 22 '15 at 14:37
  • then modify that query and add your `...='".$_SESSION['userName']."'` – Sean Apr 22 '15 at 14:56

1 Answers1

5

Try this out:

select * from messages m where not exists ( select 1 from messages mm where (mm.fromuser = m.fromuser or mm.fromuser = m.touser) AND (mm.touser = m.touser or mm.touser = m.fromuser) and mm.timestamp > m.timestamp ) and m.fromuser = 'user1' or m.touser = 'user1';

demo here.

It would probably be better, going forward, to flag conversations between two users as a particular conversation, then every message belongs to a conversation, and thus it becomes far easier to find conversations the user is involved in, and info about messages related to the conversation. Anyway.

Try this one instead. Ugh.

select m.* 
  from messages m 
     left join messages m2 
       on ((m.fromuser = m2.fromuser and m.touser = m2.touser) 
           or (m.fromuser = m2.touser and m.touser = m2.fromuser)) 
         and m.timestamp < m2.timestamp 
  where (m.fromuser = 'user1' or m.touser = 'user1') 
  and m2.id is null;

It's likely to be superior to the not exists version, even if i do manage to fix that one.

this fiddle actually works

pala_
  • 8,901
  • 1
  • 15
  • 32