Listing last (N) message of each conversation

Question

I am creating a mobile application that need to synchronize with the server, and In order to do so, I need to get last (N) messages in each conversation. note that this query was worked but get only last message in each conversation.

SELECT users.user_id AS user_id,
       users.username,
       users.picture,
       users.last_seen,
       me.message,
       me.created_on
FROM messages me,
     users
WHERE (me.sender_id=1
       OR me.recipient_id=1)
  AND ((me.sender_id=user_id
        AND me.sender_id<>1)
       OR (me.recipient_id=user_id
           AND me.recipient_id<>1))
  AND NOT exists
    (SELECT 1
     FROM messages me2
     WHERE me2.id>me.id
       AND ((me.sender_id=me2.sender_id
             AND me.recipient_id=me2.recipient_id)
            OR (me.sender_id=me2.recipient_id
                AND me.recipient_id=me2.sender_id)))
ORDER BY me.created_on DESC

Yes, using this query will return only the latest message in each conversation — Moussawi7, Mar 27 '14 at 12:53
Why not get rid of the NOT EXISTS clause, and use LIMIT to ensure you only get 10 back? — Darius X., Mar 27 '14 at 13:14
@DariusX. In this case, the result is the latest N messages for the user and not latest N messages in each conversation for the user — Moussawi7, Mar 27 '14 at 13:17
Ah, I missed that. I wonder if these techniques could work: http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — Darius X., Mar 27 '14 at 13:22

StanislavL · Answer 1 · 2014-03-28T07:54:46.850

First we get all messages of all users ordered by user and date. We introduce artificial vars to number messages of user. When user id is the same we just increase message number. If it's different reset it to 0.

Thus subquery returns us

user_id, mess_n
1        0
1        1
1        2
2        0
2        1
2        2
3        0
3        1
3        2

Then in the query just leave messages with number <10 (first 10)

select *
from (
    select u.*, m.*, 
           @mess_n_for_user:=if(u.user_id!=@curr_user,0,@mess_n_for_user+1) as mess_n,
           @curr_user:=u.user_id
    from  (SELECT @mess_n_for_user:=0, @curr_user:=-1) sess_var,
         users u join messages m on (u.user_id=m.sender_id 
                                  or u.user_id=m.recipient_id)
    order by u.user_id, m.created_on DESC) all_messages_ordered
where all_messages_ordered.mess_n<10

Just add all filters conditions to the query

UPDATED

FROM the sqlfiddle

select * from (
select all_messages_ordered.*,
           @mess_n_for_user:=if(u_id!=@curr_user,0,@mess_n_for_user+1) as mess_n,
           @curr_user:=u_id
from (SELECT @mess_n_for_user:=0, @curr_user:=-1) sess_var,
     (
      select u.id as u_id, u.first_name, m.* 
      from  
         accounts u join messages m on (u.id=m.from or u.id=m.to)
                                  and (m.to=1 or m.from=1)
                                  and u.id<>1
    order by u.id, m.date_time DESC) all_messages_ordered) a
where mess_n<3

Thanks a lot your query worked great except that it does not return (N) entries of each conversation, in fact it return whole conversation — Moussawi7, Mar 27 '14 at 20:40
I can't test it becuase I don't have the tables with data. Try to play with the number in the last row. It works fine in a similar query I use. Or you can provide sqlfiddle where we can test the query. — StanislavL, Mar 28 '14 at 05:04
please find the sqlfiddle where you can got the database:http://sqlfiddle.com/#!2/49145/3 — Moussawi7, Mar 28 '14 at 07:35

score 0 · Answer 2 · edited May 23 '17 at 11:49

You need a recursive query to select conversations. Following the approach from this answer, substituting recipient_id / sender_id for col3 / col1 :

select id, recipient_id, @pv:=sender_id as 'recipient_id' from messages
join
(select @pv:=2)tmp
where recipient_id=@pv

I have not tested this but the principle should be correct. Of course you will need to expand this to fully solve your problem...but hope this helps.

Listing last (N) message of each conversation

2 Answers2