Get the last message for each thread

Question

SQL Fiddle

http://sqlfiddle.com/#!2/1c5fc3/1

I am trying to create simple messaging system, but I am having trouble with the desired results from the SQL queries. Here are the tables I have; I am trying to get INBOX data..

INBOX Definiton for this problem:

This should be threaded display in inbox, ie. google mail, but only to show the last message in that thread with the user who originaly created the thread and the last user who replied in the thread, if the last user is the same user that created the thread and there are no replies in beetween the message doesnt belog in inbox.

TABLES:

THREAD
id_thread
id_last_message
id_user_inital
id_user_last

THREAD_USERS
id
id_thread
id_user

THREAD_MESSAGES
id_thread_messages
id_user_sender
id_thread
datetime
subject
body

MESSAGE_STATUS
id_messsage_status
id_thread_messages
id_user
status
datetime

My logic is: once a message has been sent

THREAD 
id_thread  id_last_message  id_user_inital  id_user_last
1          1                1               1

THREAD_USERS 
id   id_thread   id_user
1    1           1
2    1           2

THEREAD_MESSAGES
id_thread_messages   id_user_sender   id_thread   datetime           subject   body
1                    1                1           07.09.2014 16:02   'title'   'text message'

MESSAGE_STATUS
id_message_status   id_thread_messages   id_user   status   datetime
1                   1                    1         4        07.09.2014 16:02
2                   1                    2         1        07.09.2014 16:02

Lets say status can be

0 = deleted (do not show at all)
1 = new (show only to user that is on the receiving end)
2 = read (this status will be shown to all users in the thread)
3 = replied (show only to user that makes this action)
4 = sent (show only to user that makes this action)

Query :

SELECT * 
   FROM thread
      JOIN thread_users
         ON thread.id_thread = thread_users.id_thread
      JOIN thread_messages 
         ON thread.id_thread = thread_messages.id_thread
         JOIN message_status 
            ON thread_messages.id_thread_messages = message_status.id_thread_messages
   WHERE 
          thread_users.id_user = 2
      AND message_status.status != 0
      AND message_status.status != 4
      AND thread.id_user_last != message_status.id_user

sample data

THREAD

id_thread   id_last_message   id_user_inital  id_user_last
1           4                 1               2
2           2                 3               3
3           3                 4               4

THREAD_USERS

id   id_thread   id_user
1    1           1
2    1           2
3    2           3
4    2           2
5    3           4
6    3           2

THEREAD_MESSAGES

id_thread_messages   id_user_sender   id_thread   datetime          subject     body
1                    1                1           07.09.2014 16:02  'title'     'text message'
2                    3                2           07.09.2014 16:05  'hey two'   'foo'
3                    4                2           07.09.2014 16:07  'hey two'   'bar' 
4                    2                1           07.09.2014 16:10  'title'     'replay on 1st'

MESSAGE_STATUS

id_message_status  id_thread_messages  id_user   status   datetime
1                  1                   1         4        07.09.2014 16:02 
2                  1                   2         1        07.09.2014 16:02
3                  2                   3         4        07.09.2014 16:05 
4                  2                   2         1        07.09.2014 16:05  
5                  3                   4         4        07.09.2014 16:07
6                  3                   2         1        07.09.2014 16:07  
7                  4                   2         4        07.09.2014 16:10 
8                  4                   1         1        07.09.2014 16:10

How would you extract INBOX data from this situation, as I am spinning in circles for hours and can't quite get what I am doing wrong.

Thank you.

Answer 1

Updated solution after taking into account explanations for message status:

SELECT DISTINCT t.*, tm.* , ms.*
FROM thread t 
   -- tm should be last message
   INNER JOIN thread_messages tm ON t.id_thread = tm.id_thread
      INNER JOIN message_status ms ON (ms.id_thread_messages = tm.id_thread_messages)AND
                                      (ms.id_user=2)AND
                                      (ms.status!=0)
   -- try to find message after tm, and then in WHERE filter only those cases where there is no message after tm
   LEFT JOIN thread_messages tm_next 
        INNER JOIN message_status ms_next ON (ms_next.id_thread_messages = tm_next.id_thread_messages)AND
                                             (ms_next.id_user=2)AND
                                             (ms_next.status!=0)
      ON (t.id_thread = tm_next.id_thread)and
         (tm_next.datetime>tm.datetime)

   LEFT JOIN thread_messages tm_other 
        INNER JOIN message_status ms_other ON (ms_other.id_thread_messages = tm_other.id_thread_messages)AND
                                             (ms_other.id_user=2)AND
                                             (ms_other.status!=0)
      ON (t.id_thread = tm_other.id_thread)and
         (tm_other.id_thread_messages!=tm.id_thread_messages)and
         (tm_other.id_user_sender!=2)

WHERE 
  -- ensure tm is last message in thread
  (tm_next.id_thread is null)and
  (
       -- there is a non deleted message from another user in current thread
       (tm_other.id_thread_messages is not null)or

       -- last message is not from current user
       (tm.id_user_sender!=2)
  )

SqlFiddle is here. Let me know is this working for you.

Answer 2

Now with the description of what INBOX means I suggest you rely heavily on EXISTS clause in your query. Here is just an example of how it can look like:

SELECT * 
FROM thread t INNER JOIN 
  thread_messages tm ON t.id_thread = tm.id_thread
WHERE
 EXISTS ( -- User is in the thread.
   SELECT * FROM thread_users tu 
   WHERE t.id_thread = tu.id_thread AND tu.id_user = 2
 )
 AND NOT EXISTS ( -- Exclude earlier messages for thread.
   SELECT * FROM thread_messages  WHERE 
   tm.id_thread = id_thread AND datetime > tm.datetime
   AND EXISTS (-- Exclude deleted messages here
     SELECT * FROM message_status 
     WHERE thread_messages.id_thread_messages = id_thread_messages
       AND status != 0
       AND status != 4
   )
 )
 AND EXISTS ( -- Include messages that were not deleted or send to self.
    SELECT * FROM message_status 
    WHERE tm.id_thread_messages = id_thread_messages
     AND status != 0
     AND status != 4
     AND t.id_user_last != id_user -- not sure what is this for
 )
 AND EXISTS ( -- Include threads with messages from several users
   SELECT * FROM thread_messages 
   WHERE tm.id_thread = id_thread 
     AND tm.id_user_sender != t.id_user_inital)

http://sqlfiddle.com/#!2/1c5fc3/39

Answer 3

i think this is the solution that you are looking for

SELECT * FROM thread
JOIN  thread_users ON thread.id_thread = thread_users.id_thread
JOIN thread_messages ON thread.id_thread = thread_messages.id_thread
JOIN message_status ON thread_messages.id_thread_messages = message_status.id_thread_messages
WHERE thread_users.id_user = 2
AND thread_users.id_user = message_status.id_user
AND message_status.status != 0
AND message_status.status != 4
AND thread.id_user_last != message_status.id_user