generate nested tree path in hierarchical data in mysql

dd

-- ----------------------------
-- Table structure for acos
-- ----------------------------
DROP TABLE IF EXISTS `acos`;
CREATE TABLE `acos`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `parent_id` int(11) NULL DEFAULT NULL,
  `model` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `foreign_key` int(11) NULL DEFAULT NULL,
  `alias` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `lft` int(11) NULL DEFAULT NULL,
  `rght` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `lft`(`lft`, `rght`) USING BTREE,
  INDEX `alias`(`alias`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 135 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of acos
-- ----------------------------
INSERT INTO `acos` VALUES (1, '', NULL, NULL, NULL, 'controllers', 1, 268);
INSERT INTO `acos` VALUES (2, '', 1, NULL, NULL, 'Blog', 2, 13);
INSERT INTO `acos` VALUES (3, '', 2, NULL, NULL, 'index', 3, 4);
INSERT INTO `acos` VALUES (4, '', 2, NULL, NULL, 'view', 5, 6);
INSERT INTO `acos` VALUES (5, '', 2, NULL, NULL, 'add', 7, 8);
INSERT INTO `acos` VALUES (6, '', 2, NULL, NULL, 'edit', 9, 10);
INSERT INTO `acos` VALUES (7, '', 2, NULL, NULL, 'delete', 11, 12);
INSERT INTO `acos` VALUES (8, '', 1, NULL, NULL, 'Comments', 14, 31);
INSERT INTO `acos` VALUES (9, '', 8, NULL, NULL, 'index', 15, 16);
INSERT INTO `acos` VALUES (10, '', 8, NULL, NULL, 'view', 17, 18);
INSERT INTO `acos` VALUES (11, '', 8, NULL, NULL, 'add', 19, 20);
INSERT INTO `acos` VALUES (12, '', 8, NULL, NULL, 'edit', 21, 22);
INSERT INTO `acos` VALUES (13, '', 8, NULL, NULL, 'delete', 23, 24);
INSERT INTO `acos` VALUES (14, '', 8, NULL, NULL, 'action1', 25, 26);
INSERT INTO `acos` VALUES (15, '', 8, NULL, NULL, 'action2', 27, 28);
INSERT INTO `acos` VALUES (16, '', 8, NULL, NULL, 'actionBBB', 29, 30);
INSERT INTO `acos` VALUES (17, '', 1, NULL, NULL, 'Docters', 32, 49);
INSERT INTO `acos` VALUES (18, '', 17, NULL, NULL, 'index', 33, 34);
INSERT INTO `acos` VALUES (19, '', 17, NULL, NULL, 'view', 35, 36);
INSERT INTO `acos` VALUES (20, '', 17, NULL, NULL, 'add', 37, 38);
INSERT INTO `acos` VALUES (21, '', 17, NULL, NULL, 'edit', 39, 40);
INSERT INTO `acos` VALUES (22, '', 17, NULL, NULL, 'delete', 41, 42);
INSERT INTO `acos` VALUES (23, '', 17, NULL, NULL, 'action1', 43, 44);
INSERT INTO `acos` VALUES (24, '', 17, NULL, NULL, 'action2', 45, 46);
INSERT INTO `acos` VALUES (25, '', 17, NULL, NULL, 'actionBBB', 47, 48);
INSERT INTO `acos` VALUES (26, '', 1, NULL, NULL, 'Error', 50, 51);
INSERT INTO `acos` VALUES (27, '', 1, NULL, NULL, 'Pages', 52, 61);
INSERT INTO `acos` VALUES (28, '', 27, NULL, NULL, 'display', 53, 54);
INSERT INTO `acos` VALUES (29, '', 27, NULL, NULL, 'action1', 55, 56);
INSERT INTO `acos` VALUES (30, '', 27, NULL, NULL, 'action2', 57, 58);
INSERT INTO `acos` VALUES (31, '', 27, NULL, NULL, 'actionBBB', 59, 60);
INSERT INTO `acos` VALUES (32, '', 1, NULL, NULL, 'Products', 62, 73);
INSERT INTO `acos` VALUES (33, '', 32, NULL, NULL, 'index', 63, 64);
INSERT INTO `acos` VALUES (34, '', 32, NULL, NULL, 'view', 65, 66);
INSERT INTO `acos` VALUES (35, '', 32, NULL, NULL, 'add', 67, 68);
INSERT INTO `acos` VALUES (36, '', 32, NULL, NULL, 'edit', 69, 70);
INSERT INTO `acos` VALUES (37, '', 32, NULL, NULL, 'delete', 71, 72);
INSERT INTO `acos` VALUES (38, '', 1, NULL, NULL, 'Test', 74, 85);
INSERT INTO `acos` VALUES (39, '', 38, NULL, NULL, 'index', 75, 76);
INSERT INTO `acos` VALUES (40, '', 38, NULL, NULL, 'view', 77, 78);
INSERT INTO `acos` VALUES (41, '', 38, NULL, NULL, 'add', 79, 80);
INSERT INTO `acos` VALUES (42, '', 38, NULL, NULL, 'edit', 81, 82);
INSERT INTO `acos` VALUES (43, '', 38, NULL, NULL, 'delete', 83, 84);
INSERT INTO `acos` VALUES (44, '', 1, NULL, NULL, 'Admin', 86, 133);
INSERT INTO `acos` VALUES (45, '', 44, NULL, NULL, 'Comments', 87, 98);
INSERT INTO `acos` VALUES (46, '', 45, NULL, NULL, 'index', 88, 89);
INSERT INTO `acos` VALUES (47, '', 45, NULL, NULL, 'view', 90, 91);
INSERT INTO `acos` VALUES (48, '', 45, NULL, NULL, 'add', 92, 93);
INSERT INTO `acos` VALUES (49, '', 45, NULL, NULL, 'edit', 94, 95);
INSERT INTO `acos` VALUES (50, '', 45, NULL, NULL, 'delete', 96, 97);
INSERT INTO `acos` VALUES (51, '', 44, NULL, NULL, 'Docters', 99, 110);
INSERT INTO `acos` VALUES (52, '', 51, NULL, NULL, 'index', 100, 101);
INSERT INTO `acos` VALUES (53, '', 51, NULL, NULL, 'view', 102, 103);
INSERT INTO `acos` VALUES (54, '', 51, NULL, NULL, 'add', 104, 105);
INSERT INTO `acos` VALUES (55, '', 51, NULL, NULL, 'edit', 106, 107);
INSERT INTO `acos` VALUES (56, '', 51, NULL, NULL, 'delete', 108, 109);
INSERT INTO `acos` VALUES (57, '', 44, NULL, NULL, 'Pages', 111, 120);
INSERT INTO `acos` VALUES (58, '', 57, NULL, NULL, 'display', 112, 113);
INSERT INTO `acos` VALUES (59, '', 57, NULL, NULL, 'action1', 114, 115);
INSERT INTO `acos` VALUES (60, '', 57, NULL, NULL, 'action2', 116, 117);
INSERT INTO `acos` VALUES (61, '', 57, NULL, NULL, 'actionBBB', 118, 119);
INSERT INTO `acos` VALUES (62, '', 44, NULL, NULL, 'Products', 121, 132);
INSERT INTO `acos` VALUES (63, '', 62, NULL, NULL, 'index', 122, 123);
INSERT INTO `acos` VALUES (64, '', 62, NULL, NULL, 'view', 124, 125);
INSERT INTO `acos` VALUES (65, '', 62, NULL, NULL, 'add', 126, 127);
INSERT INTO `acos` VALUES (66, '', 62, NULL, NULL, 'edit', 128, 129);
INSERT INTO `acos` VALUES (67, '', 62, NULL, NULL, 'delete', 130, 131);
INSERT INTO `acos` VALUES (68, '', 1, NULL, NULL, 'Maneger', 134, 159);
INSERT INTO `acos` VALUES (69, '', 68, NULL, NULL, 'Comments', 135, 146);
INSERT INTO `acos` VALUES (70, '', 69, NULL, NULL, 'index', 136, 137);
INSERT INTO `acos` VALUES (71, '', 69, NULL, NULL, 'view', 138, 139);
INSERT INTO `acos` VALUES (72, '', 69, NULL, NULL, 'add', 140, 141);
INSERT INTO `acos` VALUES (73, '', 69, NULL, NULL, 'edit', 142, 143);
INSERT INTO `acos` VALUES (74, '', 69, NULL, NULL, 'delete', 144, 145);
INSERT INTO `acos` VALUES (75, '', 68, NULL, NULL, 'Docters', 147, 158);
INSERT INTO `acos` VALUES (76, '', 75, NULL, NULL, 'index', 148, 149);
INSERT INTO `acos` VALUES (77, '', 75, NULL, NULL, 'view', 150, 151);
INSERT INTO `acos` VALUES (78, '', 75, NULL, NULL, 'add', 152, 153);
INSERT INTO `acos` VALUES (79, '', 75, NULL, NULL, 'edit', 154, 155);
INSERT INTO `acos` VALUES (80, '', 75, NULL, NULL, 'delete', 156, 157);
INSERT INTO `acos` VALUES (81, '', 1, NULL, NULL, 'Acl', 160, 161);
INSERT INTO `acos` VALUES (82, '', 1, NULL, NULL, 'AclMan', 162, 237);
INSERT INTO `acos` VALUES (83, '', 82, NULL, NULL, 'Actions', 163, 174);
INSERT INTO `acos` VALUES (84, '', 83, NULL, NULL, 'index', 164, 165);
INSERT INTO `acos` VALUES (85, '', 83, NULL, NULL, 'view', 166, 167);
INSERT INTO `acos` VALUES (86, '', 83, NULL, NULL, 'add', 168, 169);
INSERT INTO `acos` VALUES (87, '', 83, NULL, NULL, 'edit', 170, 171);
INSERT INTO `acos` VALUES (88, '', 83, NULL, NULL, 'delete', 172, 173);
INSERT INTO `acos` VALUES (89, '', 82, NULL, NULL, 'Manegers', 175, 178);
INSERT INTO `acos` VALUES (90, '', 89, NULL, NULL, 'index', 176, 177);
INSERT INTO `acos` VALUES (91, '', 82, NULL, NULL, 'Tests', 179, 184);
INSERT INTO `acos` VALUES (92, '', 91, NULL, NULL, 'index', 180, 181);
INSERT INTO `acos` VALUES (93, '', 91, NULL, NULL, 'getAllAcosNode', 182, 183);
INSERT INTO `acos` VALUES (94, '', 82, NULL, NULL, 'Admin', 185, 210);
INSERT INTO `acos` VALUES (95, '', 94, NULL, NULL, 'Actions', 186, 197);
INSERT INTO `acos` VALUES (96, '', 95, NULL, NULL, 'index', 187, 188);
INSERT INTO `acos` VALUES (97, '', 95, NULL, NULL, 'view', 189, 190);
INSERT INTO `acos` VALUES (98, '', 95, NULL, NULL, 'add', 191, 192);
INSERT INTO `acos` VALUES (99, '', 95, NULL, NULL, 'edit', 193, 194);
INSERT INTO `acos` VALUES (100, '', 95, NULL, NULL, 'delete', 195, 196);
INSERT INTO `acos` VALUES (101, '', 94, NULL, NULL, 'Pages', 198, 209);
INSERT INTO `acos` VALUES (102, '', 101, NULL, NULL, 'index', 199, 200);
INSERT INTO `acos` VALUES (103, '', 101, NULL, NULL, 'view', 201, 202);
INSERT INTO `acos` VALUES (104, '', 101, NULL, NULL, 'add', 203, 204);
INSERT INTO `acos` VALUES (105, '', 101, NULL, NULL, 'edit', 205, 206);
INSERT INTO `acos` VALUES (106, '', 101, NULL, NULL, 'delete', 207, 208);
INSERT INTO `acos` VALUES (107, '', 82, NULL, NULL, 'Maneger', 211, 236);
INSERT INTO `acos` VALUES (108, '', 107, NULL, NULL, 'Actions', 212, 223);
INSERT INTO `acos` VALUES (109, '', 108, NULL, NULL, 'index', 213, 214);
INSERT INTO `acos` VALUES (110, '', 108, NULL, NULL, 'view', 215, 216);
INSERT INTO `acos` VALUES (111, '', 108, NULL, NULL, 'add', 217, 218);
INSERT INTO `acos` VALUES (112, '', 108, NULL, NULL, 'edit', 219, 220);
INSERT INTO `acos` VALUES (113, '', 108, NULL, NULL, 'delete', 221, 222);
INSERT INTO `acos` VALUES (114, '', 107, NULL, NULL, 'Pages', 224, 235);
INSERT INTO `acos` VALUES (115, '', 114, NULL, NULL, 'index', 225, 226);
INSERT INTO `acos` VALUES (116, '', 114, NULL, NULL, 'view', 227, 228);
INSERT INTO `acos` VALUES (117, '', 114, NULL, NULL, 'add', 229, 230);
INSERT INTO `acos` VALUES (118, '', 114, NULL, NULL, 'edit', 231, 232);
INSERT INTO `acos` VALUES (119, '', 114, NULL, NULL, 'delete', 233, 234);
INSERT INTO `acos` VALUES (120, '', 1, NULL, NULL, 'DebugKit', 238, 267);
INSERT INTO `acos` VALUES (121, '', 120, NULL, NULL, 'Composer', 239, 242);
INSERT INTO `acos` VALUES (122, '', 121, NULL, NULL, 'checkDependencies', 240, 241);
INSERT INTO `acos` VALUES (123, '', 120, NULL, NULL, 'MailPreview', 243, 250);
INSERT INTO `acos` VALUES (124, '', 123, NULL, NULL, 'index', 244, 245);
INSERT INTO `acos` VALUES (125, '', 123, NULL, NULL, 'sent', 246, 247);
INSERT INTO `acos` VALUES (126, '', 123, NULL, NULL, 'email', 248, 249);
INSERT INTO `acos` VALUES (127, '', 120, NULL, NULL, 'Panels', 251, 256);
INSERT INTO `acos` VALUES (128, '', 127, NULL, NULL, 'index', 252, 253);
INSERT INTO `acos` VALUES (129, '', 127, NULL, NULL, 'view', 254, 255);
INSERT INTO `acos` VALUES (130, '', 120, NULL, NULL, 'Requests', 257, 260);
INSERT INTO `acos` VALUES (131, '', 130, NULL, NULL, 'view', 258, 259);
INSERT INTO `acos` VALUES (132, '', 120, NULL, NULL, 'Toolbar', 261, 264);
INSERT INTO `acos` VALUES (133, '', 132, NULL, NULL, 'clearCache', 262, 263);
INSERT INTO `acos` VALUES (134, '', 120, NULL, NULL, 'Admin', 265, 266);

SET FOREIGN_KEY_CHECKS = 1;

اگر جدولی داریم که ساختار داده‌های آن بصورت درختی است و مثل جدول بالا باشد و احیانا اگر بخواهیم که یک هم چنین چیزی نیاز داشته باشیم:

حالا باید چه کار کنیم؟! از این کوئری می‌توانیم استفاده کنیم:)
‍‍‍```
SELECT
node.*,
( GROUP_CONCAT( parent.alias ORDER BY parent.lft SEPARATOR '/' ) ) AS path
FROM
acos AS node,
acos AS parent
WHERE
node.lft BETWEEN parent.lft
AND parent.rght
GROUP BY
node.alias,
node.parent_id
ORDER BY
node.lft;