当前位置: 动力学知识库 > 问答 > 编程问答 >

ltree - postgresql nested select with map

问题描述:

I have the following structure:

CREATE TABLE folder (

id integer primary key,

name varchar(255)

);

CREATE TABLE folder_path (

folder_id integer references folder(id),

path ltree

);

INSERT INTO folder (id,name) VALUES (1,'f1');

INSERT INTO folder (id,name) VALUES (2,'f2');

INSERT INTO folder_path (folder_id, path) VALUES (1,'1');

INSERT INTO folder_path (folder_id, path) VALUES (2,'1.2');

INSERT INTO folder_path (folder_id, path) VALUES (2,'2');

And needs to select all folders with their paths, like this:

 [

{ id: 1,

name: 'f1',

paths: [

[{id: 1, name: 'f1'}]

]

}

{ id: 2,

name: 'f2',

paths: [

[{id: 1, name: 'f1'}, {id: 2, name: 'f2'}],

[{id: 2, name: 'f2'}]

]

]

How can I do this? Thanks.

sqlfiddle: http://sqlfiddle.com/#!15/6b974/12

分享给朋友:
您可能感兴趣的文章:
随机阅读: