系统

Question

我正在开发一个信息收件箱系统。用户创建针对另一个用户的消息线程。它的工作方式如下：

1. 收到的消息线程将显示在收件箱中。
2. 您创建的消息线程也会出现在收件箱中。
3. 您删除的消息线程仅从您自己的收件箱中删除。

我的message_thread表中有以下字段：

• id
• from_user_id
• to_user_id
• 主题
• 已删除
• updated_at

我使用以下查询成功地实现了1和2：

SELECT * FROM message_thread mt
WHERE ((mt.from_user_id = 1 OR mt.to_user_id = 1) AND mt.deleted = 0)
ORDER BY mt.updated_at DESC

我正在努力想出一种实现3的方法。

我假设需要引入新的列(from_user_deleted和to_user_deleted)。是否可以通过扩展查询来检索正确的结果集，或者是否需要在服务器端脚本(当前使用PHP)中进行检索？

编辑:如果用户A删除一个线程，然后用户B向该线程发送一个回复，它应该重新出现在用户A的收件箱中。

Answer 1

我通过添加一个新表来解决这个问题：

message_thread_status

• message_thread_id (FK)
• user_id (FK)
• 删除(默认为0)

创建message_thread时，它在message_thread_status表中创建两个条目(消息线程中的每个用户一个)。

然后，我只需要在message_thread_status表中找到当前用户的记录，并添加一个条件来检查deleted的值。

新查询：

SELECT
     * FROM message_thread mt

LEFT OUTER JOIN
     message_thread_status mts ON mts.message_thread_id = mt.id

WHERE
    ((mt.from_user_id = 1 OR mt.to_user_id = 1)
    AND (mts.user_id = 1 AND mts.deleted = 0))

ORDER BY
    mt.updated_at DESC

Answer 2

用通俗的英语：

其中(消息是给您的或(消息来自您，而消息未被删除))