Get the latest post for each topic
SQL Fiddle
http://sqlfiddle.com/#!2/1c5fc3/1
I am trying to create a simple messaging system but I am having problems with the desired results of the SQL queries. Here are the tables I have; I am trying to get INBOX data.
INBOX Define this problem:
This should be a threaded display in the incoming, i.e. google mail, but only to display the last message in that thread with the user who created the original thread and the last user who responded in the thread, if the last user is the same user who created the thread and there are no replies in beetween the message does not hang in the 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: after post sending
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
Suppose the 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)
Request:
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
THREADS
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 pull the INBOX data out of this situation, as I have been spinning for hours in circles and cannot get what I am doing wrong.
Thank.
source to share
Updated solution after accounting for post state explanations:
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 . Let me know it works for you.
source to share
I think this is the solution 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
source to share
Now with a description of what INBOX stands for, I suggest you rely heavily on the EXISTS clause in your request. Here's an example of what this might 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)
source to share