Home > Blockchain >  SQL output to display parent child relationships 4 levels deep
SQL output to display parent child relationships 4 levels deep

Time:08-26

I have a resource table that contains resource values and descriptions. The table also has a parent_id column that can match a res_id of another resource. This table can go up to 4 levels deep of children.

Sample resource table:

| res_id| kind_id| value         | description | parent_id|
|-------|--------|---------------|-------------|----------|
| 5678  | 4      | price_asfa    | fgdfhdf     | null     |
| 6789  | 2      | number_val    | djhhjhj     | 2345     |
| 9876  | 5      | test_val      | lutuyutyu   | 5678     |
| 8765  | 2      | other_val     | yuiyuiy     | 9876     |
| 26346 | 3      | edit_kind     | yfgjqwes    | null     |
| 1234  | 2      | option_kind   | dggdfghdhg  | 2345     |
| 2345  | 3      | fruit_price   | ghfgndfd    | 3456     |
| 3456  | 5      | console_price | fgdfgdsffhg | 5678     |

I also have a users table and a many-many user_resource table to create a relationship between the user_id and the res_id

Sample user table:

| user_id | username  | password | user_type_id |
|---------|-----------|----------|--------------|
| 100     | tim.smith | sdfsg    | 5            |
| 200     | jon.doe   | sdfsdh   | 4            |
| 300     | alex.din  | tyurte   | 5            |

Sample user_resource table

| user_id | res_id | date |
|---------|--------|------|
| 100     | 3456   | any  |
| 300     | 1234   | any  |
| 200     | 8765   | any  |
| 100     | 26346  | any  |

Im looking to get an SQL output where each row can see the username and userid as well as 4 columns (one column for each level resource description). not all resources will go 4 levels deep so some columns will have null values.

Im running into a situation when trying to join all the tables that im seeing duplicate resources moved up one level in the column rows. So im trying to nest the resource selects but its causing issues and im not able to create this table.

Expected output. (basically break down the relationships in the resource table into 4 columns as its max only 4 levels deep on parent/child/grandchild):

| user_id | username  | resource_group | resource1   | resource2     | resource3  |
|---------|-----------|----------------|-------------|---------------|------------|
| 100     | tim.smith | console_price  | price_asfa  | null          | null       |
| 100     | tim.smith | edit_kind      | null        | null          | null       |
| 200     | jon.doe   | other_val      | test_val    | price_asfa    | null       |
| 300     | alex.din  | option_kind    | fruit_price | console_price | price_asfa |
| 300     | alex.din  | edit_kind      | null        | null          | null       |

Ive made it one level down, but now if i try to nest a select join inside the other select join im getting invalid identifier errors as i guess the query cant find the nested values?

How can i keep this query going all the way down 4 levels?

SELECT u.userid,
       u.username,
       r.resource_group AS "RESOURCE GROUP",
       r.resource1
FROM   users_table u
       inner join user_resources_table ur
               ON u.userid = ur.userid
       left join (SELECT r1.resid,
                         r1.description  RESOURCE_GROUP,
                         r_r.description resource1
                  FROM   resources_table r1
                         left join resources_table r_r
                                ON r_r.parentid = r1.resid
                  WHERE  r1.parentid IS NULL) r
              ON ur.resid = r.resid
WHERE  u.active = 'Y'
       AND u.usertypeid = 5
       AND u.username = 'someName'
ORDER  BY u.username,
          r.resource_group,
          r.resource1; 

EDIT: update to Alex's response.

Currently now if I run the query as you've given alex i end up with something like this ():

| user_id | username  | resource_group | resource1     | resource2   | resource3     |
|---------|-----------|----------------|---------------|-------------|---------------|
| 100     | tim.smith | null           | null          | price_asafa | console_price |
| 100     | tim.smith | null           | null          | null        | edit_kind     |
| 200     | jon.doe   | null           | price_asfa    | test_val    | other_val     |
| 300     | alex.din  | price_asfa     | console_price | fruit_price | option_kind   |
| 300     | alex.din  | null           | null          | null        | edit_kind     |

When its supposed to be like this:

| user_id | username  | resource_group | resource1   | resource2     | resource3  |
|---------|-----------|----------------|-------------|---------------|------------|
| 100     | tim.smith | console_price  | price_asfa  | null          | null       |
| 100     | tim.smith | edit_kind      | null        | null          | null       |
| 200     | jon.doe   | other_val      | test_val    | price_asfa    | null       |
| 300     | alex.din  | option_kind    | fruit_price | console_price | price_asfa |
| 300     | alex.din  | edit_kind      | null        | null          | null       |

So i reversed the lvl like so:

for lvl in (3 as grp, 2 as lvl1, 1 as lvl2, 0 as lvl3)

And now it is aligning like below:

| user_id | username  | resource_group | resource1   | resource2     | resource3  |
|---------|-----------|----------------|-------------|---------------|------------|
| 100     | tim.smith | null           | null        | console_price | price_asfa |
| 100     | tim.smith | null           | null        | null          | edit_kind  |
| 200     | jon.doe   | null           | other_val   | test_val      | price_asfa |
| 300     | alex.din  | option_kind    | fruit_price | console_price | price_asfa |
| 300     | alex.din  | null           | null        | null          | edit_kind  |

Which works if all rows are not null. but any nulls end up by default to the left of the data. See the first row for example. The values should be in resource_group and resource1 as noted in the example table above, but they are aligned on the right of the table.

Is there a way to reverse this?

CodePudding user response:

You can use recursive subquery factoring to generate the levels:

with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
  select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
  from users_table ut
  join user_resources_table urt on urt.user_id = ut.user_id
  join resources_table rt on rt.res_id = urt.res_id
  union all
  select r.user_id, r.username, r.root_res_id, r.lvl   1, rt.res_id, rt.parent_id, rt.value
  from rcte r
  join resources_table rt on rt.res_id = r.parent_id
)
select * from rcte
USER_ID USERNAME ROOT_RES_ID LVL RES_ID PARENT_ID VALUE DESCRIPTION
100 tim.smith 3456 1 3456 5678 console_price fgdfgdsffhg
100 tim.smith 3456 2 5678 null price_asfa fgdfhdf
100 tim.smith 26346 1 26346 null edit_kind yfgjqwes
200 jon.doe 8765 1 8765 9876 other_val yuiyuiy
200 jon.doe 8765 2 9876 5678 test_val lutuyutyu
200 jon.doe 8765 3 5678 null price_asfa fgdfhdf
300 alex.din 1234 1 1234 2345 option_kind dggdfghdhg
300 alex.din 1234 2 2345 3456 fruit_price ghfgndfd
300 alex.din 1234 3 3456 5678 console_price fgdfgdsffhg
300 alex.din 1234 4 5678 null price_asfa fgdfhdf

And then pivot using the levels to get the format you want, renaming the pivoted columns to whatever you want:

with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
  select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
  from users_table ut
  join user_resources_table urt on urt.user_id = ut.user_id
  join resources_table rt on rt.res_id = urt.res_id
  union all
  select r.user_id, r.username, r.root_res_id, r.lvl   1, rt.res_id, rt.parent_id, rt.value
  from rcte r
  join resources_table rt on rt.res_id = r.parent_id
)
select user_id, username,
  grp_value as resource_group,
  lvl1_value as resource_1,
  lvl2_value as resource_2,
  lvl3_value as resource_3
from (
  select user_id, username, root_res_id, lvl, res_id, value
  from rcte
)
pivot (
  max(res_id) as res_id, max(value) as value 
  for lvl in (0 as grp, 1 as lvl1, 2 as lvl2, 3 as lvl3)
)
order by user_id, root_res_id
USER_ID USERNAME RESOURCE_GROUP RESOURCE_1 RESOURCE_2 RESOURCE_3
100 tim.smith console_price price_asfa null null
100 tim.smith edit_kind null null null
200 jon.doe other_val test_val price_asfa null
300 alex.din option_kind fruit_price console_price price_asfa

db<>fiddle


If you want to reverse the order so that the resource at the root of the hierarchy - that is, the one with a null parent - as the resource group, then you can reverse the levels in the pivot subquery (not in the pivot clause itself):

with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
  select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
  from users_table ut
  join user_resources_table urt on urt.user_id = ut.user_id
  join resources_table rt on rt.res_id = urt.res_id
  union all
  select r.user_id, r.username, r.root_res_id, r.lvl   1, rt.res_id, rt.parent_id, rt.value
  from rcte r
  join resources_table rt on rt.res_id = r.parent_id
)
select user_id, username,
  grp_value as resource_group,
  lvl1_value as resource_1,
  lvl2_value as resource_2,
  lvl3_value as resource_3
from (
  select user_id, username, root_res_id, res_id, value,
    row_number() over (partition by user_id, root_res_id order by lvl desc) - 1 as lvl
  from rcte
)
pivot (
  max(res_id) as res_id, max(value) as value 
  for lvl in (0 as grp, 1 as lvl1, 2 as lvl2, 3 as lvl3)
)
order by user_id, root_res_id
USER_ID USERNAME RESOURCE_GROUP RESOURCE_1 RESOURCE_2 RESOURCE_3
100 tim.smith price_asfa console_price null null
100 tim.smith edit_kind null null null
200 jon.doe price_asfa test_val other_val null
300 alex.din price_asfa console_price fruit_price option_kind

db<>fiddle

But that isn't what you showed in the question.

CodePudding user response:

Almost exactly the same as my answer to your (now deleted) previous question, just reversing the hierarchy and then re-reversing the depths:

select u.user_id,
       u.username,
       r.*
FROM   users_table u
       INNER JOIN user_resources_table ur
       on u.user_id  = ur.user_id
       CROSS JOIN LATERAL (
         SELECT *
         FROM   (
           SELECT value,
                  MAX(LEVEL) OVER ()   1 - LEVEL AS depth
           FROM   resources_table r
           WHERE  level <= 4
           START WITH ur.res_id = r.res_id
           CONNECT BY res_id = PRIOR parent_id
         )
         PIVOT (
           MAX(value)
           FOR depth IN (
             1 AS resource_group,
             2 AS resource1,
             3 AS resource2,
             4 AS resource3
           )
         )
       ) r
--WHERE  u.active = 'Y'
--AND    u.user_type_id = 5
--AND    u.username = 'someName'
ORDER BY
       u.username,
       r.resource_group,
       r.resource1,
       r.resource2,
       r.resource3;

Which, for the sample data (thanks @AlexPoole), outputs:

USER_ID USERNAME RESOURCE_GROUP RESOURCE1 RESOURCE2 RESOURCE3
300 alex.din price_asfa console_price fruit_price option_kind
200 jon.doe price_asfa test_val other_val null
100 tim.smith edit_kind null null null
100 tim.smith price_asfa console_price null null

db<>fiddle here

  • Related