I have a nested model category tree in array format as follows ....
$data = [
[
'categoryId' => '08adf337-a577-4038-86a6-a5cd16676dff',
'name' => 'ELECTRONICS',
'parentId' => 0,
'lft' => 1,
'rgt' => 20,
'level' => 0,
],
[
'categoryId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
'name' => 'TELEVISIONS',
'parentId' => '08adf337-a577-4038-86a6-a5cd16676dff',
'lft' => 2,
'rgt' => 9,
'level' => 1,
],
[
'categoryId' => '3fb68583-b9f3-4996-86fa-897c825767e4',
'name' => 'TUBE',
'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
'lft' => 3,
'rgt' => 4,
'level' => 2,
],
[
'categoryId' => 'bca4d77f-9f6f-41da-ad66-e747edcb9482',
'name' => 'LCD',
'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
'lft' => 5,
'rgt' => 6,
'level' => 2,
],
[
'categoryId' => '9726c8ff-7664-4494-bb2f-3b365e7a6743',
'name' => 'PLASMA',
'parentId' => '20150966-bbc5-438f-b3eb-1ff0bbc71b8b',
'lft' => 7,
'rgt' => 8,
'level' => 2,
],
[
'categoryId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
'name' => 'PORTABLE ELECTRONICS',
'parentId' => '08adf337-a577-4038-86a6-a5cd16676dff',
'lft' => 10,
'rgt' => 19,
'level' => 1,
],
[
'categoryId' => '062921a4-e9af-48f6-adba-d623ca2de4ae',
'name' => 'MP3 PLAYERS',
'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
'lft' => 11,
'rgt' => 14,
'level' => 2,
],
[
'categoryId' => '3a80ae89-5eba-4407-819c-944e1d360ed9',
'name' => 'FLASH',
'parentId' => '062921a4-e9af-48f6-adba-d623ca2de4ae',
'lft' => 12,
'rgt' => 13,
'level' => 3,
],
[
'categoryId' => '0a03f267-8646-4460-bce7-dc6993e337a3',
'name' => 'CD PLAYERS',
'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
'lft' => 15,
'rgt' => 16,
'level' => 2,
],
[
'categoryId' => 'fc9a25d0-a67a-4753-aed8-c148c9c95573',
'name' => '2 WAY RADIOS',
'parentId' => '576a2266-9f11-4430-b81f-2ac2a536ad9a',
'lft' => 17,
'rgt' => 18,
'level' => 2,
],
];
.. and using PHP, I need to convert the format I obtained from the mysql output above into the following format:
The response that i need to:
$result = array();
$result['items'] = [
[
id => 08adf337-a577-4038-86a6-a5cd16676dff,
title => 'ELECTRONICS',
children => [
[ id: 2, title: 'Child #1' ],
[ id: 3, title: 'Child #2' ],
[
id: 4,
title: 'Child #3',
children: [
[ id: 5, title: 'Grandchild #1' ],
[ id: 6, title: 'Grandchild #2' ],
],
],
],
]
];
For better understanding, I added my mysql table below visually.
Here is my mysql table:
------------------------
MySQL categories table
------------------------
categoryId parentId name lft rgt level
-------------------------------------------------------------------------------------------------------------------
08adf337-a577-4038-86a6-a5cd16676dff 0 ELECTRONICS 1 20 0
20150966-bbc5-438f-b3eb-1ff0bbc71b8b 08adf337-a577-4038-86a6-a5cd16676dff TELEVISIONS 2 9 1
3fb68583-b9f3-4996-86fa-897c825767e4 20150966-bbc5-438f-b3eb-1ff0bbc71b8b TUBE 3 4 2
bca4d77f-9f6f-41da-ad66-e747edcb9482 20150966-bbc5-438f-b3eb-1ff0bbc71b8b LCD 5 6 2
9726c8ff-7664-4494-bb2f-3b365e7a6743 20150966-bbc5-438f-b3eb-1ff0bbc71b8b PLASMA 7 8 2
576a2266-9f11-4430-b81f-2ac2a536ad9a 08adf337-a577-4038-86a6-a5cd16676dff PORTABLE ELECTRONICS 10 19 1
062921a4-e9af-48f6-adba-d623ca2de4ae 576a2266-9f11-4430-b81f-2ac2a536ad9a MP3 PLAYERS 11 14 2
3a80ae89-5eba-4407-819c-944e1d360ed9 062921a4-e9af-48f6-adba-d623ca2de4ae FLASH 12 13 3
0a03f267-8646-4460-bce7-dc6993e337a3 576a2266-9f11-4430-b81f-2ac2a536ad9a CD PLAYERS 15 16 2
fc9a25d0-a67a-4753-aed8-c148c9c95573 576a2266-9f11-4430-b81f-2ac2a536ad9a 2 WAY RADIOS 17 18 2
I've included the full output of the sql code below if you need to add it to your database.
Here is the full sql code:
------------------------
MySQL categories.sql
------------------------
SET NAMES utf8mb4;
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
`categoryId` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`parentId` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`lft` int NOT NULL,
`rgt` int NOT NULL,
`level` int NULL DEFAULT NULL,
PRIMARY KEY (`categoryId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `categories` VALUES ('062921a4-e9af-48f6-adba-d623ca2de4ae', '576a2266-9f11-4430-b81f-2ac2a536ad9a', 'MP3 PLAYERS', 11, 14, 2);
INSERT INTO `categories` VALUES ('08adf337-a577-4038-86a6-a5cd16676dff', '0', 'ELECTRONICS', 1, 20, 0);
INSERT INTO `categories` VALUES ('0a03f267-8646-4460-bce7-dc6993e337a3', '576a2266-9f11-4430-b81f-2ac2a536ad9a', 'CD PLAYERS', 15, 16, 2);
INSERT INTO `categories` VALUES ('20150966-bbc5-438f-b3eb-1ff0bbc71b8b', '08adf337-a577-4038-86a6-a5cd16676dff', 'TELEVISIONS', 2, 9, 1);
INSERT INTO `categories` VALUES ('3a80ae89-5eba-4407-819c-944e1d360ed9', '062921a4-e9af-48f6-adba-d623ca2de4ae', 'FLASH', 12, 13, 3);
INSERT INTO `categories` VALUES ('3fb68583-b9f3-4996-86fa-897c825767e4', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'TUBE', 3, 4, 2);
INSERT INTO `categories` VALUES ('576a2266-9f11-4430-b81f-2ac2a536ad9a', '08adf337-a577-4038-86a6-a5cd16676dff', 'PORTABLE ELECTRONICS', 10, 19, 1);
INSERT INTO `categories` VALUES ('9726c8ff-7664-4494-bb2f-3b365e7a6743', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'PLASMA', 7, 8, 2);
INSERT INTO `categories` VALUES ('bca4d77f-9f6f-41da-ad66-e747edcb9482', '20150966-bbc5-438f-b3eb-1ff0bbc71b8b', 'LCD', 5, 6, 2);
INSERT INTO `categories` VALUES ('fc9a25d0-a67a-4753-aed8-c148c9c95573', '576a2266-9f11-4430-b81f-2ac2a536ad9a', '2 WAY RADIOS', 17, 18, 2);
After running the sql code you may need to get the entire category tree:
The sql code which is fetch all tree.
SELECT c.name, c.parentId, c.lft, c.rgt, c.level
FROM
categories c
WHERE
c.lft BETWEEN c.lft AND c.rgt
GROUP BY
c.name
ORDER BY
c.lft;
Sample output of above the code:
+-----------------------+
| name |
+-----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+-----------------------+
Reference: https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
You can just map the original response you already have from SQL to the format you want. You could do it with a recursive function like below.
function buildTree($items, $parentId = 0) {
$branch = [];
foreach ($items as $item) {
if ($item['parentId'] == $parentId) {
$children = buildTree($items, $item['categoryId']);
$node = [
'id' => $item['categoryId'],
'title' => $item['name']
];
if (!empty($children)) {
$node['children'] = $children;
}
$branch[] = $node;
}
}
return $branch;
}