Как рекурсивно запрашивать древовидную структуру с несколькими таблицами?
Вот моя ER-диаграмма. Если я знаю user_id, как я могу получить пользовательские данные => проекты => доски => сообщения одного пользователя с помощью одного SQL-запроса?
Я узнал о рекурсивном CTE, но во всех примерах, которые я могу найти, все данные хранятся в одной таблице. У меня есть данные, разделенные на 4 таблицы. Есть ли способ получить все пользовательские данные здесь?
У меня нет никакого SQL, чтобы показать, что я пытался, потому что, честно говоря, я даже не знаю, с чего начать. Я думал просто добавить поле user_id в каждую таблицу, но это не похоже на правильное решение.
Обновлено: Это хороший способ справиться с избыточными данными из соединений?
У меня есть 3 идеи:
1-> Получить все данные с дубликатами. Нет повторяющихся данных в базе данных и один запрос, но я отправляю больше данных, чем мне нужно.
2-> Разделить запрос на четыре отдельных запроса для каждой таблицы. Нет повторяющихся данных, но затем я запускаю четыре отдельных запроса.
3-> Добавьте «user_id» в каждую таблицу и запросите каждую таблицу напрямую. Тогда у меня есть избыточные данные в моей базе данных.
4-> ?? Лучший вариант? Или более краткий запрос?
Вот моя идея разделить запросы (2)
-- user data
SELECT nickname, theme FROM users WHERE user_id = 'exampleid';
-- project data
SELECT
pr.project_id,
pr.time_created ,
pr.time_last_modified,
pr.title
FROM users u
INNER JOIN projects pr
ON u.user_id = pr.fk_projects_users
WHERE u.user_id = 'exampleid';
-- board data
SELECT
b.board_id,
b.fk_boards_projects,
b.title,
b.order_position,
b.color
FROM users u
INNER JOIN projects pr
ON u.user_id = pr.fk_projects_users
INNER JOIN boards b
ON pr.project_id = b.fk_boards_projects
WHERE u.user_id = 'exampleid';
-- post data
SELECT
po.post_id,
po.fk_posts_boards,
po.time_created,
po.title,
po.priority,
po.time_due,
po.body
FROM users u
INNER JOIN projects pr
ON u.user_id = pr.fk_projects_users
INNER JOIN boards b
ON pr.project_id = b.fk_boards_projects
INNER JOIN posts po
ON po.post_id = b.board_id
WHERE u.user_id = 'exampleid'
Ответы 1
Серии простых соединений будет достаточно.
Запросы, которые обращаются к нескольким таблицам (или к нескольким экземплярам одной и той же таблицы) одновременно, называются запросами на соединение. Они объединяют строки из одной таблицы со строками из второй таблицы с выражением, указывающим, какие строки должны быть объединены в пары.
create table users (user_id int);
create table projects(project_id int, fk_projects_users int);
create table boards (board_id int, fk_boards_projects int);
create table posts (post_id int, fk_posts_boards int);
insert into users values (1), (2), (3);
insert into projects values (11,1), (12,1), (13,2);
insert into boards values (101,11), (102,11), (103,13);
insert into posts values (1001,101),(1002,101), (1003,102),(1004,103);
select po.post_id
from users u
inner join projects pr
on u.user_id=pr.fk_projects_users
inner join boards b
on pr.project_id=b.fk_boards_projects
inner join posts po
on b.board_id=po.fk_posts_boards
where u.user_id=1;
-- post_id
-----------
-- 1001
-- 1002
-- 1003
--(3 rows)
Следующим логическим шагом было бы начать использовать агрегатные функции, которые позволяют собирать статистику по каждому пользователю.
select
u.user_id,
array_agg(po.post_id) as "array of all their post ids",
count(po.post_id) as "how many posts this user has",
max(po.post_id) as "latest post of this user (by id)"
from users u
inner join projects pr
on u.user_id=pr.fk_projects_users
inner join boards b
on pr.project_id=b.fk_boards_projects
inner join posts po
on b.board_id=po.fk_posts_boards
group by u.user_id;
-- user_id | array of all their post ids | how many posts this user has | latest post of this user (by id)
-----------+-----------------------------+------------------------------+----------------------------------
-- 2 | {1004} | 1 | 1004
-- 1 | {1001,1002,1003} | 3 | 1003
--(2 rows)
Это то, что мне нужно было знать! Но как теперь быть с дубликатами данных из объединений? (например, повторяющиеся пользовательские данные для каждого сообщения). По сути, мне нужно сбросить все данные во внешний интерфейс в виде объекта JSON. Я отредактировал свой пост своими идеями, что делать. Как вы думаете, должен ли я запрашивать все и иметь дело с дубликатами на серверной части или запрашивать каждую таблицу отдельно и получать именно те данные, которые мне нужны, но за 4 запроса?
Ваше редактирование добавляет еще один вопрос - лучше открыть отдельный и показать, какой именно запрос вы использовали, какую часть его результатов вы пытаетесь исключить, что вы пробовали до сих пор и почему это не работает. Без примеров я не уверен, что вы получаете и что именно вы считаете дублирующим и избыточным. Вы можете скопировать и изменить примеры DDL из моего ответа, просто добавив в них несколько столбцов и примеры данных, чтобы максимально точно отразить вашу настройку.
У меня есть новый вопрос, если вы хотите проверить его, я был бы признателен!
Одна из самых фундаментальных концепций реляционных баз данных заключается в том, что объединение двух таблиц дает ВСЕ комбинации всех строк в каждой таблице. Предложение ON, следующее за ним, и предложение WHERE под ним отфильтровывают нежелательные строки. Говоря
users u Inner Join projects p, вы запрашиваете ВСЕХ пользователей и ВСЕ проекты, независимо от того, работал над ними пользователь или нет. ДобавляяOn u.user_id=pr.fk_projects_users, вы выбрасываете всех пользователей, которые не работали над проектом, и оставляете только список пользователей и проектов, над которыми они работали.