私信系统查询

发布于 2024-11-14 22:59:41 字数 463 浏览 0 评论 0原文

我正在使用 mysql 制作私人消息系统。创建此表：

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

表 user_has_messages 存储消息历史记录，因此每 1 条消息有 2 行（针对“发送者”用户和“接收者”用户）。每条消息 2 行，因为即使接收者删除了消息，发件人也应该看到他的消息。所以我需要获取具体用户的所有对话框列表，其中包含最后一条消息。如果你看一下这张图片应该更容易理解：解释

问题是我无法为此任务构建正确的查询。也许数据库设计不好？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酷遇一生 2024-11-21 22:59:41

看起来这个查询就是我需要的：

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

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

回复收藏 0 原文

︶￣淡然 2024-11-21 22:59:41

我相信数据库设计可能是错误的，因为如果收件人删除他的消息（通过删除 user_has_messages 行），那么发件人将无法再看到他们将其发送给谁 - 信息丢失。

如果一条消息总是有一个发送者和一个接收者，那么我会有这样的表：

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

即使采用这种简化的设计，满足您的要求的 SQL 也有点复杂：（

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);

并且假设 (sender_id,recipient_id,created) 是唯一键）。

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).

回复收藏 0 原文

~没有更多了~