Skip to content

Instantly share code, notes, and snippets.

@josser

josser/query.sql

Created Feb 11, 2016
Embed
What would you like to do?
with tbl2_view as (
with tbl3_view as (
with tbl4_view as (
select * from tbl4
)
select tbl3.*, json_agg(tbl4_view) as tbl4 from tbl3
inner join tbl4_view on tbl4_view.tbl3_id = tbl3.id
group by tbl3.id
)
select tbl2.*, json_agg(tbl3_view) as tbl3 from tbl2
inner join tbl3_view on tbl3_view.tbl2_id = tbl2.id
group by tbl2.id
)
select tbl1.*, json_agg(tbl2_view) as tbl2 from tbl1
inner join tbl2_view on tbl2_view.tbl1_id = tbl1.id
group by tbl1.id;
CREATE TABLE tbl1 (
id serial,
name text
);
CREATE TABLE tbl2 (
id serial,
name text,
tbl1_id integer
);
CREATE TABLE tbl3 (
id serial,
name text,
tbl2_id integer
);
CREATE TABLE tbl4 (
id serial,
name text,
tbl3_id integer
);
INSERT INTO tbl1 VALUES (1, 'tbl1_foo');
SELECT pg_catalog.setval('tbl1_id_seq', 1, true);
INSERT INTO tbl2 VALUES (1, 'tbl2_foo', 1);
INSERT INTO tbl2 VALUES (2, 'tbl2_bar', 1);
SELECT pg_catalog.setval('tbl2_id_seq', 2, true);
INSERT INTO tbl3 VALUES (1, 'tbl3_foo', 1);
INSERT INTO tbl3 VALUES (2, 'tbl3_bar', 2);
SELECT pg_catalog.setval('tbl3_id_seq', 2, true);
INSERT INTO tbl4 VALUES (1, 'tbl4_foo', 1);
SELECT pg_catalog.setval('tbl4_id_seq', 1, true);
ALTER TABLE ONLY tbl1
ADD CONSTRAINT tbl1_pkey PRIMARY KEY (id);
ALTER TABLE ONLY tbl2
ADD CONSTRAINT tbl2_pkey PRIMARY KEY (id);
ALTER TABLE ONLY tbl3
ADD CONSTRAINT tbl3_pkey PRIMARY KEY (id);
ALTER TABLE ONLY tbl4
ADD CONSTRAINT tbl4_pkey PRIMARY KEY (id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment