php - Group conversation by ID and join the most recent message -
i have 2 tables.
conversations
id | user_from | user_to | subject
messages
id | user_id | conversation_id | message
now want pull conversations particular user
. however, can't figure how this. current solution gives me oldest message each conversation.
db::table('conversations') ->where('user_from', '=', $this->id) ->orwhere('user_to', '=', $this->id) ->join('messages', 'conversations.id', '=', 'messages.conversation_id') ->groupby('conversations.id') ->get();
how can newest message each conversation?
in plain mysql can use additional self join messages ,according question guess id messages table set auto_increment have used max(id)
recent message id per conversation
select c.* conversations c join messages m on(m.conversation_id = c.id) join (select conversation_id,max(id) ic messages group conversation_id) mm on(m.conversation_id = mm.conversation_id , m.id = mm.id)
Comments
Post a Comment