mysql - 对话属于多个用户，但用户 A 删除而用户 B 不删除。我们如何防止它被退回？

Question

我正在我们的应用程序中构建一个聊天功能。基本的聊天工作，我们有一个查询来获取属于用户的对话，获取对话，消息等。

现在，我们想要添加一个功能，对话的参与者(对话可以有多个参与者)可以在他们的一端删除聊天，但是这个 不会删除 服务器中的对话。相反，我们会将对话标记为从该用户的 X 点删除。在这种情况下，当删除对话的参与者再次在我们的 API 中请求对话时，他将看不到删除之前的消息。

为了清楚地理解这个概念，WhatsApp、Telegram 或当今大多数聊天应用程序的工作方式都是一样的。当用户 A 和 B 进行交互时，如果用户 B 选择从他的手机中删除对话，用户 A 仍然会看到整个对话。如果用户 B(或 A)在对话中再次发短信，用户 B 只会看到新的短信。

我不完全确定这对他们来说是如何工作的，但似乎对我们有用的结构如下:

CREATE TABLE `conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `starter_id` bigint(20) unsigned NOT NULL,
  `last_message_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversations_starter_id_index` (`starter_id`),
  KEY `conversations_last_message_id_index` (`last_message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversation_participants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `participant_id` bigint(20) unsigned NOT NULL,
  `deleted_from_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_participants_conversation_id_index` (`conversation_id`),
  KEY `conversation_participants_participant_id_index` (`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversation_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `sender_id` bigint(20) unsigned NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_messages_conversation_id_index` (`conversation_id`),
  KEY `conversation_messages_sender_id_index` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

正如您在 conversation_participants 中看到的那样我们添加了一个 deleted_from_id .此 deleted_from_id当用户 B 向服务器发送删除 session 的请求时，将得到更新。它将发送他看到的conversation_id 和最新的conversation_message_id 并相应地更新。

我们使用 Laravel 作为我们的框架，使用 Eloquent 轻松生成具有关系的查询。我们有一个端点为用户请求最新的 25 个对话，然后我们对它们进行分页。这是为此类查询生成的查询:

select
  `conversations`.*,
  `conversation_participants`.`participant_id` as `pivot_participant_id`,
  `conversation_participants`.`conversation_id` as `pivot_conversation_id`,
  `conversation_participants`.`created_at` as `pivot_created_at`,
  `conversation_participants`.`updated_at` as `pivot_updated_at`
from
  `conversations`
  inner join `conversation_participants` on `conversations`.`id` = `conversation_participants`.`conversation_id`
where
  `conversation_participants`.`participant_id` = 1
  and exists (
    select
      *
    from
      `conversation_messages`
    where
      `conversations`.`id` = `conversation_messages`.`conversation_id`
  )
order by
  `id` desc

上面的查询非常简单，它返回特定用户的对话。使用 Laravel 的 Conversation::with('messages')... ，它允许我们轻松过滤包含消息的对话(我们不希望返回空的对话)。

问题是我们试图过滤更多并阻止用户在其手机上删除的对话显示在该查询中。我们还没有找到办法。

我们的第一个猜测是简单地添加调整 exists()限制 conversation_messages.id ， 喜欢:

select
  `conversations`.*,
  `conversation_participants`.`participant_id` as `pivot_participant_id`,
  `conversation_participants`.`conversation_id` as `pivot_conversation_id`,
  `conversation_participants`.`deleted_from_id` as `pivot_deleted_from_id`,
  `conversation_participants`.`created_at` as `pivot_created_at`,
  `conversation_participants`.`updated_at` as `pivot_updated_at`
from
  `conversations`
  inner join `conversation_participants` on `conversations`.`id` = `conversation_participants`.`conversation_id`
where
  `conversation_participants`.`participant_id` = 1
  and exists (
    select
      *
    from
      `conversation_messages`
    where
      `conversations`.`id` = `conversation_messages`.`conversation_id`
      and `id` > conversation_participants.deleted_from_id
  )
order by
  `id` desc

这将“有效”，但是如果用户删除了一条消息，并且比方说，有消息的对话早于 and身份证 > conversation_participants.deleted_from_id ，不会返回其他 session 。这是错误的，因为它会阻止显示任何其他对话，即使它们有消息并且属于参与者。

我们还尝试了一种不同的方法，在 exists() 中使用了一些连接。尝试阻止“已删除的对话”显示在列表中:

select
  `conversations`.*,
  `conversation_participants`.`participant_id` as `pivot_participant_id`,
  `conversation_participants`.`conversation_id` as `pivot_conversation_id`,
  `conversation_participants`.`deleted_from_id` as `pivot_deleted_from_id`,
  `conversation_participants`.`created_at` as `pivot_created_at`,
  `conversation_participants`.`updated_at` as `pivot_updated_at`
from
  `conversations`
  inner join `conversation_participants` on `conversations`.`id` = `conversation_participants`.`conversation_id`
where
  `conversation_participants`.`participant_id` = 1
  and exists (
    select
      `conversation_messages`.*
    from
      `conversation_messages`
    join
      `conversations` on `conversations`.`id` = `conversation_messages`.`conversation_id`
    join
      `conversation_participants` on `conversations`.`id` = `conversation_participants`.`conversation_id`
    where
      `conversations`.`id` = `conversation_messages`.`conversation_id`
      and `conversation_participants`.`participant_id` = 1
      and `conversation_messages`.`id` > `conversation_participants`.`deleted_from_id`
  )
order by
  `id` desc

但不幸的是，这也不起作用。

为了让测试更方便，我在这里设置了一个 DB Fiddle: https://www.db-fiddle.com/f/q6S3GfZNCbvYbtvRwXJxN7/0

这个 fiddle 有多个用户、多个对话和多个消息。如您所见，如果您立即运行查询，它将返回属于给定参与者的 28 个对话。

也就是说，如果您注意到在 session_participants 表中，有一行参与者_id=1，在 session_id=82 上，删除消息是 82: (55,28,1,82,'2020-01-31 10:01:08','2020-01-31 10:01:08'), ( fiddle 中的第 166 行)。消息82是conversation_id=28中的最后一条消息，因此应该 没有出现在查询中，因为它没有消息。

在我们努力寻找解决方案的过程中，我们还认为可能有行 conversations.last_message_id将有助于防止对话出现......但我们也不确定这一点，因为我们找不到解决方案。我决定将它留在 SQL 中，以防它有助于找到解决方案。

我怎样才能得到想要的结果？我错过了什么？

提前致谢

Answer 1

这就是我要做的。

对话包含消息
消息包含参与者。

我不会保留在您的案例中看到的“Conversation_Participants”。
相反，我会保留“Message_Participants”。

这将是我的表结构

CREATE TABLE `conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `starter_id` bigint(20) unsigned NOT NULL,
  `last_message_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversations_starter_id_index` (`starter_id`),
  KEY `conversations_last_message_id_index` (`last_message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `conversation_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `sender_id` bigint(20) unsigned NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_messages_conversation_id_index` (`conversation_id`),
  KEY `conversation_messages_sender_id_index` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `message_participants` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `message_id` bigint(20) unsigned NOT NULL,
      `participant_id` bigint(20) unsigned NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      )

我省略了外键的创建，您可以根据需要添加。

现在，每当消息到达具有“n”个参与者的对话时，将在 message_participants 表中生成“n”个条目。

因此，当参与者从他的聊天中删除一条消息或一组消息时，您可以删除“message_participants”表中与该消息和参与者有关的相应条目。

这样，参与者可以按任何顺序从任何聊天中删除任何消息。在您的逻辑中，您提到了“last_message_id”。这将限制用户不能访问超过特定 id 的消息，但按照我的逻辑，他可以保留 2014 年的几条消息，然后删除之后直到 2018 年的所有消息，然后从 2018 年开始保留 2 个月的聊天记录并删除其余消息。
我希望你明白这一点并希望它有所帮助。