I want to display my query results in a tree form in HTML and CSS. After some searching the internet, I came across a very good library: https://dumptyd.github.io/treeflex/
I got this data using CTE in My database. Here is the data:
WITH RECURSIVE category_path (id, parent_id, lvl, name, path) AS
(
SELECT id, parent_id ,0 lvl, name, name as path
FROM account_roll_up
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, (cp.lvl + 1), c.name, CONCAT(cp.path, ' > ', c.name)
FROM category_path AS cp JOIN account_roll_up AS c
ON cp.id = c.parent_id
)
SELECT id,parent_id, lvl, name AS name FROM category_path
ORDER BY path;
+------+-----------+------+--------------------------------------+
| id | parent_id | lvl | name |
+------+-----------+------+--------------------------------------+
| 23 | NULL | 0 | Laporan Keuangan |
| 25 | 23 | 1 | Laporan Laba Rugi |
| 12 | 25 | 2 | Laba (Rugi) Sebelum Pajak |
| 16 | 12 | 3 | Laba (Rugi) Usaha |
| 13 | 16 | 4 | Laba (Rugi) Kotor |
| 15 | 13 | 5 | Total Biaya Langsung |
| 14 | 13 | 5 | Total Jumlah Pendapatan |
| 17 | 16 | 4 | Total Biaya Administrasi & Umum |
| 18 | 17 | 5 | Total Biaya Administrasi |
| 19 | 17 | 5 | Total Biaya Umum |
| 20 | 12 | 3 | Total Pendapatan & Biaya Lain - Lain |
| 22 | 20 | 4 | Total Biaya Lain-Lain |
| 21 | 20 | 4 | Total Pendapatan Lain - Lain |
| 24 | 23 | 1 | Laporan Neraca |
| 1 | 24 | 2 | Total Aktiva |
| 6 | 1 | 3 | Total Aktiva Lainnya |
| 2 | 1 | 3 | Total Aktiva Lancar |
| 4 | 1 | 3 | Total Aktiva Tetap |
| 5 | 1 | 3 | Total Aktiva Tidak Berwujud |
| 3 | 1 | 3 | Total Investasi Jangka Panjang |
| 7 | 24 | 2 | Total Hutang & Modal |
| 8 | 7 | 3 | Total Hutang |
| 10 | 8 | 4 | Total Hutang Jangka Panjang |
| 9 | 8 | 4 | Total Hutang Jangka Pendek |
| 11 | 7 | 3 | Total Modal |
+------+-----------+------+--------------------------------------+
25 rows in set (0.01 sec)
As an array in php :
[
0 => [
'id' => '23'
'lvl' => '0'
'name' => 'Laporan Keuangan'
]
1 => [
'id' => '25'
'lvl' => '1'
'name' => 'Laporan Laba Rugi'
]
2 => [
'id' => '12'
'lvl' => '2'
'name' => 'Laba (Rugi) Sebelum Pajak'
] ....
to 25,
How to build this flat array into those treeflex? Any help it so appreciated.
So, I change my mind to restructure those array by copy the original one.
Now, the problem, how to generate this tree into treeflex,
[
0 => [
'id' => '23'
'parent_id' => null
'lvl' => '0'
'name' => 'Laporan Keuangan'
'children' => [
0 => [
'id' => '25'
'parent_id' => '23'
'lvl' => '1'
'name' => 'Laporan Laba Rugi'
'children' => [
0 => [
'id' => '12'
'parent_id' => '25'
'lvl' => '2'
'name' => 'Laba (Rugi) Sebelum Pajak'
'children' => [
0 => [
'id' => '16'
'parent_id' => '12'
'lvl' => '3'
'name' => 'Laba (Rugi) Usaha'
'children' => [
0 => [
'id' => '13'
'parent_id' => '16'
'lvl' => '4'
'name' => 'Laba (Rugi) Kotor'
'children' => [...]
]
1 => [
'id' => '17'
'parent_id' => '16'
'lvl' => '4'
'name' => 'Total Biaya Administrasi & Umum'
'children' => [...]
]
]
]
1 => [
'id' => '20'
'parent_id' => '12'
'lvl' => '3'
'name' => 'Total Pendapatan & Biaya Lain - Lain'
'children' => [
0 => [
'id' => '22'
'parent_id' => '20'
'lvl' => '4'
'name' => 'Total Biaya Lain-Lain'
]
1 => [
'id' => '21'
'parent_id' => '20'
'lvl' => '4'
'name' => 'Total Pendapatan Lain - Lain'
]
]
]
]
]
]
]
1 => [
'id' => '24'
'parent_id' => '23'
'lvl' => '1'
'name' => 'Laporan Neraca'
'children' => [
0 => [
'id' => '1'
'parent_id' => '24'
'lvl' => '2'
'name' => 'Total Aktiva'
'children' => [
0 => [
'id' => '6'
'parent_id' => '1'
'lvl' => '3'
'name' => 'Total Aktiva Lainnya'
]
1 => [
'id' => '2'
'parent_id' => '1'
'lvl' => '3'
'name' => 'Total Aktiva Lancar'
]
2 => [
'id' => '4'
'parent_id' => '1'
'lvl' => '3'
'name' => 'Total Aktiva Tetap'
]
3 => [
'id' => '5'
'parent_id' => '1'
'lvl' => '3'
'name' => 'Total Aktiva Tidak Berwujud'
]
4 => [
'id' => '3'
'parent_id' => '1'
'lvl' => '3'
'name' => 'Total Investasi Jangka Panjang'
]
]
]
1 => [
'id' => '7'
'parent_id' => '24'
'lvl' => '2'
'name' => 'Total Hutang & Modal'
'children' => [
0 => [
'id' => '8'
'parent_id' => '7'
'lvl' => '3'
'name' => 'Total Hutang'
'children' => [
0 => [
'id' => '10'
'parent_id' => '8'
'lvl' => '4'
'name' => 'Total Hutang Jangka Panjang'
]
1 => [
'id' => '9'
'parent_id' => '8'
'lvl' => '4'
'name' => 'Total Hutang Jangka Pendek'
]
]
]
1 => [
'id' => '11'
'parent_id' => '7'
'lvl' => '3'
'name' => 'Total Modal'
]
]
]
]
]
]
]
]
