SQL Inbox запрос, как Google Inbox
Итак, у меня есть таблица сообщений существующего приложения:
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 Inbox. В частности, это означает, что сообщения группируются в разговоры. То есть Сообщение 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, Но то, как я сейчас это делаю, делает это неэффективным.
Вот пример (для INBOX) запроса (использующего 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();
Решение
Хорошо, теперь я собираюсь ответить на свой вопрос:
Здесь есть действительно полезный пост: Запрос родителей и детей в таблице самообращения, что дает правильное решение (ы).
Для этого конкретного случая 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
Другие решения
Других решений пока нет …