查询私人消息系统

Question

I'm making private messaging system using mysql. Created this tables:

1) users (id, name)
2) messages(id, text, created)
3) user_has_messages(id, user_id, message_id, is_sender)

Table user_has_messages stores messaging history, so there are 2 rows(for "sender" user and for "receiver" user.) per 1 message. 2 rows per message because sender should see his message even if receiver deleted it. So i need to fetch list of all dialogs for concrete user with last message in it. It should be easier to understend if you take a look a this pic: Explanation

The problem is that i cannot construct a proper query for this task. Maybe bad db design?

Answer 1

Looks like this query is what i need:

SELECT * FROM users_has_messages uhm1 
WHERE uhm1.message_id=(
       SELECT message_id FROM users_has_messages uhm2 
       WHERE (uhm1.receiver_id=uhm2.receiver_id AND uhm1.sender_id=uhm2.sender_id) 
       OR uhm1.receiver_id=uhm2.sender_id ORDER BY message_id DESC limit 1) 
AND user_id=1