无限级分类并按层级排序分页

存在问题：

怎么解决最下面表格橙色行的排序跟任何级别都可以orderby字段排序，或者在创建或修改项时能先计算排序？

-- 0的长度建议是字段id的长度
SELECT id, path, title, orderby, CONCAT('1', RIGHT( CONCAT(  '00000000000', IF( toppid, toppid, id ) ) , 11 ) , REPLACE( path,  ',',  '' ) , id,  '' ) AS neworderby
FROM  `categorys` 
WHERE 1 
ORDER BY IF( toppid, toppid, orderby ) ASC , IF( toppid, toppid, id ) DESC , CONCAT('1',  RIGHT( CONCAT(  '00000000000', IF( toppid, toppid, id ) ) , 11 ) , REPLACE( path,  ',',  '' ) , id,  '' ) 
LIMIT 0 , 30

-- 测试数据
DROP TABLE IF EXISTS `categorys`;
CREATE TABLE IF NOT EXISTS `categorys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '分类',
  `pid` int(11) NOT NULL DEFAULT '0' COMMENT '上级',
  `toppid` int(11) NOT NULL DEFAULT '0',
  `path` varchar(2048) NOT NULL COMMENT '路径',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `pyfirst` varchar(25) NOT NULL COMMENT '首字母',
  `pyall` varchar(255) NOT NULL COMMENT '全拼',
  `thumb` varchar(255) NOT NULL COMMENT '略缩图',
  `recmd` tinyint(1) NOT NULL DEFAULT '0' COMMENT '推荐',
  `orderby` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0隐藏1显示',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

INSERT INTO `categorys` (`id`, `type`, `pid`, `toppid`, `path`, `title`, `pyfirst`, `pyall`, `thumb`, `recmd`, `orderby`, `status`) VALUES
(1, 2, 0, 0, '', '美食', 'm s', 'mei shi', '', 0, 0, 0),
(2, 2, 1, 1, '1', '中餐', 'z c', 'zhong can', '', 0, 0, 0),
(3, 2, 2, 1, '1,2', '早餐', 'z c', 'zao can', '', 0, 0, 0),
(4, 2, 1, 1, '1', 'dfsd', 'd', 'dfsd', '', 0, 0, 0),
(5, 2, 4, 1, '1,4', 'fdsfsd', 'f', 'fdsfsd', '', 0, 0, 0),
(6, 2, 2, 1, '1,2', 'fsdfsd', 'f', 'fsdfsd', '', 0, 0, 0),
(7, 2, 1, 1, '1', 'fdsfsd', 'f', 'fdsfsd', '', 0, 1, 0),
(8, 2, 1, 1, '1', 'dfsdf', 'd', 'dfsdf', '', 0, 0, 0),
(9, 2, 7, 1, '1,7', 'fdsfs', 'f', 'fdsfs', '', 0, 0, 0),
(10, 2, 5, 1, '1,4,5', 'fsdf', 'f', 'fsdf', '', 0, 0, 0),
(11, 2, 4, 1, '1,4', 'fsdf', 'f', 'fsdf', '', 0, 0, 0),
(12, 2, 8, 1, '1,8', 'fsdfsd', 'f', 'fsdfsd', '', 0, 0, 0),
(13, 2, 1, 1, '1', '测试', 'c s', 'ce shi', '', 0, 0, 0),
(14, 2, 0, 0, '', '酒店', 'j d', 'jiu dian', '', 0, 2, 0),
(15, 2, 14, 14, '14', '休闲', 'x x', 'xiu xian', '', 0, 0, 0);

id	path路径	orderby只支持一级排序	neworderby
14		1	10000000001414
15	14	0	1000000000141415
1		2	1000000000011
13	1	0	100000000001113
2	1	0	10000000000112
3	1,2	0	100000000001123
6	1,2	0	100000000001126
4	1	0	10000000000114
11	1,4	0	1000000000011411
5	1,4	0	100000000001145
10	1,4,5	0	10000000000114510
7	1	1	10000000000117
9	1,7	0	100000000001179
8	1	0	10000000000118
12	1,8	0	1000000000011812

 

 

 

 

 

 

 

 

 

 

...