Creating A Messages List In Sql

I'm trying to create a messages list, like Facebook. Showing only last the message from a users conversation (history) when I send show mine , when I get answer show answered mess

Solution 1:

I think your query is producing the “right” results, as if you'd like to see the last message in some of the conversations, you should really group by the conversation_id. I don't see this field you in schema though.

If you do WHERE sender_id = 3 GROUP BY receiver_id, then it is correct, that query returns you messages 1 and 7, 'cos those messages had been sent to different people, thus in your design they're different conversations.

If you want to see only the very last message sent by you in general, just remove GROUP BY in the second part of your UNION. Otherwise, consider re-designing your schema.

---

EDIT:

Try this query:

SELECT m.message_id, u.username, m.subject, m.message,
       m.status, UNIX_TIMESTAMP(m.date) as `date`
  FROM users u
  LEFT JOIN messages m ON m.sender_id = u.id
 WHERE m.message_id IN (
    SELECT max(message_id) 
      FROM messages
     WHERE receiver_id = 3OR sender_id = 3GROUPBY least(sender_id,receiver_id), 
              greatest(sender_id,receiver_id)
    );

Some notes:

1. UNION is not needed anymore;
2. This approach will treat all e-mails between 2 parties as a single conversation, which is not always true. You might want to re-design this approach;
3. It is a bad style to use reserved words (like date) for columns' names or aliases, try to avoid this. Or use backticks if you do use them.