1

So I have a message table of en existing application:

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;

I am trying to write something similar to Google Inbox. Specifically, it means that messages get grouped to conversations. I.e. Message A (sent two weeks ago) and Messages D + Z (sent yesterday) need to be shown as a group.

Currently, I am polling the DB twice, once for all the messages that have no answers to them (i.e. where hasConversation = NULL), the second time to fetch all the conversation items.

Then in PHP I am associating messages to the year/month they were sent in, or if they belong to a conversation associate them to the last reply year/month.

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)

I hope you get the idea.

How would I do this in on SQL query? The tricky part comes in when you think about "paging" or infinite scrolling. For that, to work I would need to set a LIMIT and an OFFSET. But the way I am doing it currently makes this inefficient.

Here is an example (for the INBOX) of a query (using Yii2) that fetches the data:

$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();
Muhammad Omer Aslam
  • 22,976
  • 9
  • 42
  • 68
axtho
  • 15
  • 6
  • 2
    to get better replies you should first select the answers from your previous questions, i see that you do not have the habit of selecting the answer when someone provides you , and that is like disrespecting others who helped or tried to help youout – Muhammad Omer Aslam Feb 09 '18 at 07:55

1 Answers1

0

Ok, I am going answer my own question now:

There is a really helpful post here: Query parents and children in self-referencing table, which gives the proper solution(s).

For this Yii2 specific case the query would then break down to the following:

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

EDIT: you can also see another, little less complex, solution here http://sqlfiddle.com/#!9/1808d7/1

axtho
  • 15
  • 6