Get the last message for each thread

Question

SQL Fiddle

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.

Please post SQL query(s) your tried explaining what exactly did not work — Bulat, Sep 07 '14 at 14:28
i have added the query i used.. what do you mean by sample data? — Malinois, Sep 07 '14 at 14:40
What values you have in tables and what do you expect as a result. — Bulat, Sep 07 '14 at 14:51
as far as i see it user 2 should have 2 messages in the inbox .. i am getting 3 — Malinois, Sep 07 '14 at 15:01
can you add expected output and what you get, it is difficult to guess. Alternatively you can set up sqlfiddle.com, it will be easier for everyone to help you. — Bulat, Sep 07 '14 at 15:13
added the sql fiddle, and thank you for your help so far, as you cn see i am getting 3 results, but there should be two in the user 2 inbox... — Malinois, Sep 07 '14 at 15:40
what is `id_last_message` column in the `thread` table? Shouldn't it contain value 3 for thread 2? — Davor, Sep 07 '14 at 22:29
its the id of the last message that is related to that thread (last message in that thread) — Malinois, Sep 07 '14 at 22:30
Then it looks like your test data is not ok - shouldn't it contain value 3 for thread 2? — Davor, Sep 07 '14 at 22:33
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Bulat, Sep 08 '14 at 06:39
Why have you chosen not to provide the desired result? Seems odd to me. — Strawberry, Sep 08 '14 at 14:11
I have stated in the comments befeore , I get 3 resutls when there should be 2. Also check on Davors answer an comments, that should be the solution — Malinois, Sep 08 '14 at 16:27

score 1 · Answer 1 · answered Sep 07 '14 at 15:53

1

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

answered Sep 07 '14 at 15:53

Prasad Khode

6,602
11
44
59

this seems to work but when there are only two messages in 1 thread it gives 0 results.. sqlfiddle.com/#!2/a823a/1 the last message from the thread should be shown to user in inbox, even if his messag is the last in the thread and he is not the creator of the thread – Malinois Sep 07 '14 at 17:10

Davor · Accepted Answer · 2014-09-08T13:08:45.397

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.

Regarding message stauts, if the user deletes the message that message shouldnt be shown to him . Actually user doesnt delete the message from the thread, he just sets the status of that message for him, other users involced in that thread can still se that message in the thread. — Malinois, Sep 08 '14 at 05:36
i believe this is the solution, http://sqlfiddle.com/#!2/855309/8 I will test it later on today on different data sets to confirm Thank you for your help — Malinois, Sep 08 '14 at 10:19
If user can delete message indicated by thread.id_last_message, than you should first find actual last message (start at the end and find first not deleted message) and then base logic to show/hide thread on that and other not deleted messages. — Davor, Sep 08 '14 at 12:45
I will test it later on today and get back to you... seems to work based on the fiddle test data — Malinois, Sep 09 '14 at 08:11
I am glad it worked for you! Be sure to test performance on real data, nested joins can take a lot of resources. — Davor, Sep 16 '14 at 19:03

Bulat · Answer 3 · 2014-09-08T08:15:48.187

0

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

edited Sep 08 '14 at 08:15

answered Sep 07 '14 at 16:01

Bulat

6,869
1
29
52

this seems to work but when there are only two messages in 1 thread it gives 0 results.. sqlfiddle.com/#!2/a823a/1 the last message from the thread should be shown to user in inbox, even if his messag is the last in the thread and he is not the creator of the thread – Malinois Sep 07 '14 at 17:19
I think you need to add the definition of the INBOX in the question, as it is difficult to know what you have in mind. – Bulat Sep 07 '14 at 17:29
ok, let me try to expain this further i want a 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 – Malinois Sep 07 '14 at 17:33
I have updated my answer. It is not complete, but this should get you closer to your INBOX results. – Bulat Sep 07 '14 at 21:11
this seems to work with the different data sets.. can you clarify the part that says "You will need to exclude deleted messages here" Thank you – Malinois Sep 07 '14 at 21:53
Current `EXISTS` clause for earlier messages will exclude based on all messages, even if one was deleted. This means that if you have deleted message as the last in the thread query will not work. – Bulat Sep 07 '14 at 23:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60807/discussion-between-bulat-and-malinois). – Bulat Sep 08 '14 at 08:17

Get the last message for each thread

3 Answers3