SQL收件箱查询，例如Google收件箱

Question

所以我有一个en现有应用程序的消息表：

CREATE TABLE `message` (
  `id` int(11) NOT NULL,
  `fromUserId` int(11) DEFAULT NULL,
  `fromDeleted` tinyint(1) DEFAULT NULL,
  `fromArchived` tinyint(1) DEFAULT NULL,
  `toUserId` int(11) DEFAULT NULL,
  `toDeleted` tinyint(1) DEFAULT NULL,
  `toArchived` tinyint(1) DEFAULT NULL,
  `message` mediumtext COLLATE utf8mb4_unicode_ci,
  `sentTime` datetime DEFAULT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `relatesTo` int(11) DEFAULT NULL,
  `subject` mediumtext COLLATE utf8mb4_unicode_ci,
  `viewed` tinyint(1) DEFAULT NULL,
  `hasConversation` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我正在尝试写类似于Google收件箱的东西。具体来说，这意味着消息将其分组到对话中。IE。消息A(两周前发送(和消息D Z(昨天发送(需要显示为一个组。

目前，我对所有没有答案的消息(即where hasConversation = NULL(进行了两次投票，第二次获取所有对话项目。

然后，在PHP中，我将消息与他们发送的年度/月相关联，或者如果它们属于对话将它们与最后一个答复相关联。

Message Z "Re:Re: Hello" (sent 2018-02-08 15:00)
-- Message D "Re: Hello" (sent 2018-02-03 10:00)
-- Message A "Hallo" (sent 2018-02-01 19:30)

我希望你明白这个主意。

我将如何在SQL查询中执行此操作？当您考虑"分页"或无限滚动时，棘手的部分就出现了。为此，要工作，我需要设置LIMIT和一个OFFSET。但是我目前这样做的方式使这种效率低下。

这是查询(使用yii2(的一个示例(用于收件箱(，该示例获取数据：

$messages = Message::find()
    ->with(['toUser', 'fromUser'])
    ->andWhere([
        'toUserId' => Access::userId(),
        'relatesTo' => null,
        'hasConversation' => null,
        'toArchived' => null,
        'toDeleted' => null,
    ])
    ->andWhere(['not', ['sentTime' => null]])
    ->orderBy(['sentTime' => SORT_DESC])
    ->all();
$conversations = Message::find()
    ->with(['toUser', 'fromUser'])
    ->andWhere(['OR',
        ['toUserId' => Access::userId(), 'toDeleted' => null, 'toArchived' => null],
        ['fromUserId' => Access::userId(), 'fromDeleted' => null, 'fromArchived' => null],
    ])
    ->andWhere(['OR',
        ['IS NOT', 'relatesTo', null],
        ['IS NOT', 'hasConversation', null],
    ])
    ->orderBy(['relatesTo' => SORT_DESC, 'sentTime' => SORT_DESC])
    ->all();

Answer 1

好吧，我现在要回答自己的问题：

这里有一个非常有用的帖子：在自我引用表中查询父母和孩子，这给出了适当的解决方案。

对于此YII2特定情况，查询将分解为以下内容：

$exp = new Expression('(CASE WHEN relatesTo IS NULL THEN id ELSE relatesTo END), sentTime DESC');
$messages = Message::find()
            ->with(['toUser', 'fromUser'])
            ->orderBy($exp)
            ->all();

编辑：您还可以在此处查看另一个不那么复杂的解决方案，http：//sqlfiddle.com/#！9/1808D7/1