mysql - 获取每个线程的最后一条消息

Question

SQL fiddle

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

我正在尝试创建简单的消息传递系统，但我无法从 SQL 查询中获得所需的结果。这是我的 table ；我正在尝试获取收件箱数据..

这个问题的收件箱定义:

这应该在收件箱中进行线程显示，即。谷歌邮件，但仅显示该线程中的最后一条消息，其中包含最初创建该线程的用户和最后一个在该线程中回复的用户，如果最后一个用户是创建该线程的同一用户并且在两者之间没有回复邮件不在收件箱中。

表格:

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

我的逻辑是:发送消息后

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

假设状态可以是

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)

查询:

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

样本数据

线程

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

您将如何从这种情况中提取 INBOX 数据，因为我在原地转了好几个小时并且不太明白我做错了什么。

谢谢。

Answer 1

考虑到消息状态的解释后更新的解决方案:

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 是 here .让我知道这是否适合您。