0

I have a menu table in PostgreSQL 13 like this:

-- Drop table

-- DROP TABLE public.menu_resource;

CREATE TABLE public.menu_resource (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" varchar NOT NULL,
    res_type int4 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    remark varchar NULL,
    "path" varchar NOT NULL,
    parent_id int4 NOT NULL,
    component varchar NULL,
    sort int4 NOT NULL,
    name_zh varchar NOT NULL,
    tree_id_path varchar NULL,
    CONSTRAINT auth_resource_id_seq PRIMARY KEY (id)
);

and the menu data look like this with a tree structure that marked by parent_id and id:

INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL);

now I want to generate the full path of the parent-child id, for example, the tree_id_path for job may look like this: 5-1-13, the interview may look like 5-1-13-14. what should I do to make it work like this?

1
  • You need to recursively traverse your id/parent relationship. If you start with the root(s) and then recursively assing the parents path + parent to each node. Note that you may end up with anomalies since you have information both about the parent and the ancestors Commented Apr 23, 2022 at 11:30

1 Answer 1

1

Here is one example, you probably need to adjust it slightly:

create table tmp (id int, parent_id int, tree_path_id text);

insert into tmp (id, parent_id, tree_path_id)
with recursive t (id, parent_id, tree_path_id) as (
    select id, parent_id, '' as tree_path_id 
    from public.menu_resource 
    where parent_id = 0
    
    union all
    
    select mr.id, mr.parent_id, t.tree_path_id || '-' || mr.parent_id::text
    from public.menu_resource mr
    join t
        on mr.parent_id = t.id
) 
select id, parent_id, tree_path_id from t;

update public.menu_resource x
    set tree_id_path = (select tree_path_id from tmp where id = x.id);

select id, parent_id, tree_id_path from public.menu_resource;

id  parent_id   tree_id_path
13          1   -5-1
14         13   -5-1-13
15          2   -5-2
3           1   -5-1
5           0   
4           3   -5-1-3
1           5   -5
2           5   -5
6           2   -5-2
7           3   -5-1-3
8           2   -5-2
9           5   -5
10          1   -5-1
11         10   -5-1-10
12         10   -5-1-10

Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.