как написать sql select запрос с вложенными множествами?

Question

база данных: PostgreSQL 12.6

Есть Table1 с полями:

 id   text
-------------
  1 | text1
  2 | text2

Также есть Table2, с привязкой до Table1 (many to one)

 id    info    table1_id
-------------------------
  1 |  info1  |     1
  2 |  info2  |     1
  3 |   ...   |     1
  4 |   ...   |     1
  5 |   ...   |     2
 ...|   ...   |    ...

И Table3, с привязкой до Table2 (many to one)

 id    data    table2_id
-------------------------
  1 |  data1  |     1
  2 |  data2  |     1
  3 |   ...   |     1
  4 |   ...   |     1
  5 |   ...   |     2
 ...|   ...   |    ...

CREATE TABLE table1 (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
text VARCHAR(200)
);

CREATE TABLE table2 (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
info VARCHAR(200),
table1_id INTEGER NOT NULL REFERENCES Table1(id)
);

CREATE TABLE table3 (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
data VARCHAR(200),
table2_id INTEGER NOT NULL REFERENCES Table2(id)
);

INSERT INTO Table1 (text)
VALUES ('text1');

INSERT INTO Table1 (text)
VALUES ('text2');

INSERT INTO Table2 (info, table1_id)
VALUES ('info1', 1);

INSERT INTO Table2 (info, table1_id)
VALUES ('info2', 1);

INSERT INTO Table2 (info, table1_id)
VALUES ('info3', 1);

INSERT INTO Table2 (info, table1_id)
VALUES ('info4', 1);

INSERT INTO Table2 (info, table1_id)
VALUES ('info5', 2);

INSERT INTO Table3 (data, table2_id)
VALUES ('data1', 1);

INSERT INTO Table3 (data, table2_id)
VALUES ('data2', 1);

INSERT INTO Table3 (data, table2_id)
VALUES ('data3', 1);

INSERT INTO Table3 (data, table2_id)
VALUES ('data4', 1);

INSERT INTO Table3 (data, table2_id)
VALUES ('data5', 2);

Вопрос: каким образом получить множество table2, с limit 10, а также "подгрузить" множества table3 для каждого полученного элемента table2, с limit 5(для каждого)?

Я пробовал:

1) получать множество table2, с limit 10, а потом для каждого элемента этого множества создавал новый sql запрос на получение множества table3 с limit 5;

на псевдокоде:

String getTable2Sql =
"
SELECT 
    t1.id AS table1_id, t1.text AS table1_text,
    t2.id AS table2_id, t2.info AS table2_info
FROM Table1 AS t1
INNER JOIN Table2 t2 on t2.table1_id = t1.id
WHERE t1.id = ${ insert id here }
LIMIT 10
";

String getTable3Sql =
"
SELECT 
    t2.id AS table2_id, t2.info AS table2_text,
    t3.id AS table3_id, t3.data AS table3_data
FROM Table2 AS t2
INNER JOIN Table3 t3 on t3.table2_id = t2.id
WHERE t2.id = 1
LIMIT 5
"

FirstResultDto fresultDto = executeQuery(getTable2Sql);
Table2Result[] table2result = fresultDto.getTable2();

for each (Table2 table2 : table2result) {
  Table3ResultDto table3dto = executeQuery(getTable3Sql, table2.id)
}

это работает, но уходит слишком много запросов.

2) попытка (неудачная) одним запросом:

SELECT
    t1.id as table1_id, t1.text as table1_text,
    t2.id as table2_id, t2.info as table2_info,
    t3.id as table3_id, t3.data as table3_data
FROM Table1 as t1 
JOIN LATERAL (select * from Table2 as t2
             where t2.table1_id = t1.id limit 10) t2 on true
JOIN LATERAL (select * from Table3 as t3
             where t3.table2_id = t2.id limit 5) t3 on true
WHERE t1.id = 1

Answer 1

WITH
cte2 AS ( SELECT id, info, table1_id, 
                 ROW_NUMBER() OVER (PARTITION BY table1_id ORDER BY id) rn
          FROM Table2 ),
cte3 AS ( SELECT id, data, table2_id, 
                 ROW_NUMBER() OVER (PARTITION BY table2_id ORDER BY id) rn
          FROM Table3 )
SELECT Table1.id, Table1.text,
       cte2.id, cte2.info,
       cte3.id, cte3.data
FROM Table1
JOIN cte2 ON Table1.id = cte2.table1_id AND cte2.rn <= 10
JOIN cte3 ON cte2.id = cte3.table2_id AND cte3.rn <= 5

PS. Пример данных - откровенно дерьмовый (пардоньте за мой французский).

БЛОГ НА HUSL

как написать sql select запрос с вложенными множествами?

Ответы (1 шт):