Here is my ER diagram. If I know the user_id, how can I get a single user's user data=>projects=>boards=>posts using a single SQL query?

I have found out about recursive CTE, but all of the examples I can find have all of the data stored into a single table. I have my data split into 4 tables. Is there a way to get all user data here?

I don't have any SQL to show I tried, because honestly I don't even know where to begin. I thought of just adding a user_id field to every table, but it doesn't seem like the correct solution.

CodePudding user response：

A series of simple joins will be sufficient.

Queries that access multiple tables (or multiple instances of the same table) at one time are called join queries. They combine rows from one table with rows from a second table, with an expression specifying which rows are to be paired

Demo:

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)

Your next logical step would be to start using aggregate functions that let you collect stats per user.

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)