如何从用户所在的组中选择最后一条消息

Question

I'm making a private chat system, and I want to make section "last messages from your conversations"

I really don't know how to do it. Im stuck at selecting messages

BASE ONE - members
id | conversationid | userid

BASE TWO - conversations
conversationid | name

BASE THREE - messages
id | conversationid | uid | message

I expect that: user is in conversations with id's: 1, 4, 5,9, 15 and there are selected messages from them - ordered by id

Answer 1

First you need to know what are the conversation for your user:

 SELECT conversation_id
 FROM members
 WHERE user_id = @user_id

Now you need to find what is the last message id for each conversation

 SELECT conversation_id, MAX(id) as last_id
 FROM members
 JOIN messages
   ON members.conversation_id = messages.conversation_id
 WHERE user_id = @user_id
 GROUP BY conversation_id

Now you need to find the message associated with each id:

SELECT l.conversation_id, l.last_id, m.message
FROM (
     SELECT conversation_id, MAX(id) as last_id
     FROM members
     JOIN messages
       ON members.conversation_id = messages.conversation_id
     WHERE user_id = @user_id
     GROUP BY conversation_id
    ) l
JOIN messages m
  ON l.last_id = m.id