查询私人消息系统

问题描述：

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?

我正在使用mysql制作私人消息系统。创建了这个表： p>

  1）用户（id，name）
2）消息（id，text，created）
3）user_has_messages（id，user_id，message_id，is_sender）  
  code>  pre> 
 
 表user_has_messages存储邮件历史记录，因此每1封邮件有2行（“发件人”用户和“收件人”用户。）。 每条消息2行，因为即使接收方删除了它，发送方也应该看到他的消息。  
因此我需要获取具有最后一条消息的具体用户的所有对话框的列表。 如果你看一下这张照片应该更容易下面：解释  a>  p> 
 
 
问题是我无法为此任务构建正确的查询。 也许糟糕的数据库设计？ p> 
  div>

答

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

答

I believe the database design may be wrong, because if the recipient deletes his message (by deleting the user_has_messages row) then the sender can no longer see who they sent it to - information is lost.

If a message always has one sender and one recipient, then I would have the tables like:

1) users (id, name)
2) messages(id, text, created, sender_id, recipient_id,
            deleted_by_sender, deleted_by_recipient)

Even with this simplified design the SQL for your requirement is a bit complicated:

select m.recipient_id, m.text
from messages m
where m.sender_id = ?
and m.created = (select max(created)
                 from messages m2
                 where m2.sender_id = m.sender_id
                 and m2.recipient_id = m.recipient_id
                 and m2.deleted_by_sender = 0
                 and m2.deleted_by_recipient = 0);

(and that assumes that (sender_id, recipient_id, created) is a unique key).

查询私人消息系统

相关推荐