SQL ORDER BY two fields with priority, rather than one after the other

For a message board system. There are general posts, and direct replies to those posts.

SELECT * FROM msgs ORDER BY msg_id ASC, msg_replyto ASC

will not give me the desired results, because it lists all the general posts, THEN all the replies. I am not sure how to word what I am asking so all I can do is give you mockup table and data.

Here is the example table structure and rows:

 msg_id | msg_text                               | msg_replyto
----------------------------------------------------------------
   1    | This is a post ID #1                   | NULL
   2    | This is a post ID #2                   | NULL
   3    | This is a direct reply to post ID #1   | 1
   4    | This is a post ID #4                   | NULL
   5    | This is a post ID #5                   | NULL
   6    | This is a direct reply to post ID #4   | 4
   7    | This is a post ID #7                   | NULL
   8    | This is a direct reply to post ID #3,  | 3
        | which is a direct reply to post ID #1. |

The correct order of how I am trying to have the rows returned would be as follows:

 msg_id | msg_replyto
-----------------------
   1    | NULL
   3    | 1
   8    | 3
   2    | NULL
   4    | NULL
   6    | 4
   5    | NULL
   7    | NULL

Where, at first msg_id has the priority – UNLESS the field msg_replyto in a row is equal to the other row’s msg_id, and then msg_replyto has the secondary priority. I am sorry if this doesn’t make sense, I have no other way to explain it. I tried searching for this but didn’t even know how to word it.

This is the actual SQL to create the example.

CREATE TABLE IF NOT EXISTS `msgs` (
  `msg_id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_text` varchar(200) NOT NULL,
  `msg_replyto` int(11) DEFAULT NULL,
  PRIMARY KEY (`msg_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `msgs` (`msg_id`, `msg_text`, `msg_replyto`) VALUES
(1, 'This is a post ID #1', NULL),
(2, 'This is a post ID #2', NULL),
(3, 'This is a direct reply to post ID #1', 1),
(4, 'This is a post ID #4', NULL),
(5, 'This is a post ID #5', NULL),
(6, 'This is a direct reply to post ID #4', 4),
(7, 'This is a post ID #7 ', NULL),
(8, 'This is a direct reply to post ID #3, which is a direct reply to post ID #1', 3);
COMMIT;

Answer

There is no easy way to solve this as a general problem. You have a graph walking problem.

But there is a way. You can construct a path to each method as a string and then order by that string:

with recursive cte as (
      select msg_id, cast(lpad(msg_id, 5, '0') as char(10000)) as path
      from msgs
      where msg_replyto is null
      union all
      select m.msg_id, concat_ws('->', path, lpad(m.msg_id, 5, '0')) 
      from cte join
           msgs m
           on m.msg_replyto = cte.msg_id
     )
select m.*
from msgs m left join
     cte
     on m.msg_id = cte.msg_id
order by cte.path;

Here is a db<>fiddle.

Leave a Reply

Your email address will not be published. Required fields are marked *