php - Messages table in MySQL, getting last message for each thread -

i trying last message each conversation, problem not using conversations table.

i using 1 messages table, table columns looks this:

id  from_id     to_id   text    isread  isseen  created_at  updated_at  

right able retrieve conversations this:

$messages = message::select('*')->                 from(db::raw("(select * `messages` `to_id` = ".auth::id()." order `created_at` desc) sub"))->                 groupby('from_id')->orderby('created_at', 'desc')->                 paginate(7); 

the downside not retrieving last message each conversation, retrieving last message received.

how can retrieve last message of each conversation?

example retrieve user63 conversations , last message each conversation:

id  from_id     to_id   text    isread  isseen  created_at  updated_at  23  224         63             0         0    2015-03-28 22:23:54     2015-03-28 22:23:54 20  63          225     b        0        0     2015-03-28 22:23:06     2015-03-28 22:23:06 16  225         63      hi       0        1     2015-03-28 22:21:32     2015-03-28 22:21:32 

i thought of idea, not sure though:

$messages = message::select('*')->             from(db::raw(" ( (select *, to_id theuser `messages` `to_id` = ".auth::id()." order `created_at` desc) union (select *, from_id theuser `messages` `from_id` = ".auth::id()." order `created_at` desc) group theuser ) sub"))->             groupby('from_id')->orderby('created_at', 'desc')->             paginate(7); 

this must works too: difference in approach self join little max() function this:

select from_id, max(created_at) last messages group from_id 

so:

$pagedmessages = message::select('*')->   from(     db::raw("         messages m          join (             select from_id, max(created_at) lm messages group from_id         ) lm          on lm.from_id = m.from_id , lm.created_at = m.created_at      "))-> paginate(7);