Database Design Hierarchy.md

Expected

ID  NAME      PARENT_ID  CHILDS                                                                               LEVEL
1   COMPANY   NULL       [{"id": 2, "name": "PROJECT", "level": 2}, {"id": 3, "name": "BRANCH", "level": 3}]    1
2   PROJECT   1          [{"id": 3, "name": "BRANCH", "level": 3}]                                              2
3   BRANCH    2          []                                                                                     3

MariaDB

Scheme

CREATE TABLE `organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `organization_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `organization` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

INSERT INTO `organization` (`id`, `name`, `parent_id`) VALUES
(1, 'COMPANY 1', NULL),
(2, 'PROJECT 1', 1),
(3, 'BRANCH 1', 2),
(4, 'COMPANY 2', NULL),
(5, 'PROJECT 2', 4),
(6, 'BRANCH 2', 5),
(7, 'BRANCH 3', 5);

Query

WITH RECURSIVE org_cte AS (
    SELECT 
        id, name, parent_id, 1 AS level
    FROM organization
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        o.id, o.name, o.parent_id, c.level + 1
    FROM organization o
    JOIN org_cte c ON o.parent_id = c.id
)

SELECT 
    o.id,
    o.name,
    o.parent_id,
    (
    	SELECT JSON_ARRAYAGG(JSON_OBJECT('id', child.id, 'name', child.name))
     	FROM organization child
     	WHERE child.parent_id IN (SELECT id FROM org_cte WHERE id = o.id OR parent_id = o.id)
    ) AS childs,
    o.level
FROM org_cte o
ORDER BY o.level, o.id;

PostgreSQL

Scheme

-- Table Definition
CREATE TABLE "public"."organization" (
    "id" int4 NOT NULL DEFAULT nextval('groups_id_seq'::regclass),
    "name" varchar,
    "parent_id" int4,
    CONSTRAINT "organization_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "public"."organization"("id"),
    PRIMARY KEY ("id")
);

-- Indices
CREATE UNIQUE INDEX groups_pkey ON public.organization USING btree (id);

INSERT INTO "public"."organization" ("id", "name", "parent_id") VALUES
(1, 'COMPANY 1', NULL),
(2, 'PROJECT 1', 1),
(3, 'BRANCH 1', 2),
(4, 'BRANCH 2', 2),
(5, 'BRANCH 3', 2),
(6, 'PROJECT 1', 1),
(7, 'COMPANY 2', NULL),
(8, 'BRANCH 1', 6),
(9, 'PROJECT 2', 7);

Query

WITH RECURSIVE org_cte AS (
    SELECT 
        id,
        name,
        parent_id,
        1 AS level
    FROM organization
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        o.id,
        o.name,
        o.parent_id,
        c.level + 1
    FROM organization o
    JOIN org_cte c ON o.parent_id = c.id
)

SELECT 
    o.id,
    o.name,
    o.parent_id,
    (SELECT json_agg(json_build_object('id', child.id, 'name', child.name))
     FROM organization child
     WHERE child.id IN (
         SELECT id FROM org_cte WHERE parent_id = o.id OR parent_id IN (SELECT id FROM org_cte WHERE parent_id = o.id)
     )
    ) AS childs,
    o.level
FROM org_cte o
ORDER BY o.level, o.id;