FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE coa (
    id INT(11) NOT NULL,
    parent INT(11) NULL DEFAULT NULL,
    account_name VARCHAR(255),  
    amount DECIMAL(15,2) NULL DEFAULT 0,
    is_group INT(11),
    PRIMARY KEY (`id`)
);

INSERT INTO coa VALUES
(1, NULL, 'root',          0.0,   1),
(2, 1,    'assets',        0.0,   1),
(3, 2,    'curr_assets',   0,     1),
(4, 3,    'cash',          10.0,  0),
(5, 3,    'bank',          20.0,  0),
(6, 2,    'fixed_assets',  0,     1),
(7, 6,    'buildings',     0,     1),
(8, 7,    'shopfloor',     30.0,  0),
(9, 7,    'offices',       40.0,  0),
(10, 2,   'stocks',        50.0,  0),
(11, 2,   'furniture',    60.0,  0),
(12, 1,   'liability',     0.0,   1),
(13, 12,  'payable',       70,    0)
;

with recursive
topdown as
(
  select 0 depth, id, parent from coa where parent is null
  union all
  # We do not just join, we left join, so that each leaf gets a virtual child (with a null id, but with a parent)
  select depth + 1, c.id, p.id from topdown p left join coa c on c.parent = p.id
  where p.id is not null
),
entrypoints as
(
  select * from topdown
  union all
  # Make sure we have a totally virtual node at each depth (with just a depth, but no parent and no id)
  select depth + 1, null, null from topdown group by depth
),
# Flat JSON contents (do it here instead of in the recursive downtop query, which will be dense).
contents as
(
  select depth, coa.id, coa.parent, json_object('id', t.id, 'account_name', account_name, 'is_group', is_group) j
  from topdown t join coa on coa.id = t.id 
),
downtop as
(
  select depth, id, parent, cast('' as varchar(1048576)) j from entrypoints where depth = (select max(depth) from entrypoints)
  union all
  # Do not use json_insert, which considers our previous iteration produced a string and will quote it entirely.
  select p.depth, p.id, p.parent, concat(substr(pc.j, 1, length(pc.j) - 1), ',"children":[',coalesce(c.j, ''),']}')
  from
  (
      select depth, parent, group_concat(j) j
      from downtop
      group by depth, parent
  ) c
  # Join entry points, either if we're directly referenced as a parent, or if we're a virtual node crafted to be introduced at this depth.
  join entrypoints p on p.depth = c.depth - 1 and (p.id = c.parent or (c.parent is null and p.id is null))
  left join contents pc on pc.id = p.id
)
select * from downtop where parent is null and id is not null;

depth	id	parent	j
0	1	NULL	{"id": 1, "account_name": "root", "is_group": 1,"children":[{"id": 2, "account_name": "assets", "is_group": 1,"children":[{"id": 3, "account_name": "curr_assets", "is_group": 1,"children":[{"id": 4, "account_name": "cash", "is_group": 0,"children":[]},{"id": 5, "account_name": "bank", "is_group": 0,"children":[]}]},{"id": 6, "account_name": "fixed_assets", "is_group": 1,"children":[{"id": 7, "account_name": "buildings", "is_group": 1,"children":[{"id": 8, "account_name": "shopfloor", "is_group": 0,"children":[]},{"id": 9, "account_name": "offices", "is_group": 0,"children":[]}]}]},{"id": 10, "account_name": "stocks", "is_group": 0,"children":[]},{"id": 11, "account_name": "furniture", "is_group": 0,"children":[]}]},{"id": 12, "account_name": "liability", "is_group": 1,"children":[{"id": 13, "account_name": "payable", "is_group": 0,"children":[]}]}]}