Tricky CTE - recursive sql (editing my query)

2020-05-02 07:56发布

问题:

I want to edit my query in order to get something a little more ticky.

The goal is to obtain the approval workflow for every customer, displaying that information in this way:

CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4

Previously, i had a table called entities

(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew')
(13, 'John Connor', 308, 'CHAIN2-JohnConnor')
(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);

DO NOTE:

12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT

13 was assigned to John Connor... 308 is the number that says that John Connor is a CLIENT

Because Math Andrew and John Connor are CLIENTS (also known as CUSTOMERS) they must be linked to one or more APPROVERS

A client could have 1 APPROVER, OR 2 APPROVERS OR 3 APPROVERS OR 4 APPROVERS, there exist different approvers inside entities table.

When i say that a client "could have" 1 or more APPROVERS i mean this:

CLIENT - APPROVER4 (this is a 1-1 relationship) PS: A CLIENT WILL ALWAYS BE RELATED TO the APPROVER4 IN SOME WAY OR ANOTHER

CLIENT - APPROVER1 - APPROVER4 (in this case there Will be 2 relations.. ONE: CLIENT-APPROVER1 and another APPROVER1-APPROVER4)

CLIENT - APPROVER1 - APPROVER2 - APPROVER4 (in this case there Will be 3 relations.. ONE: CLIENT-APPROVER1, APPROVER1- APPROVER2 AND APPROVER2 - APPROVER4)

AND SO ON... (hopefully you get the idea)

table type_entities

(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315, 'J6 APPROVER4'),
(316,'J7 APPROVER4');

table type_relation

(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(458,'CLIENT-APPROVER3'),
(459,'CLIENT-APPROVER1'),
(460,'APPROVER1-APPROVER2'),
(461,'APPROVER1-APPROVER3'),
(462,'J3 APPROVER1-APPROVER4'),
(463,'APPROVER2-APPROVER3'),
(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4'),
(471,'CLIENT-APPROVER2');

Types of relations:

CLIENT - APPROVER1 :

(459,'CLIENT-APPROVER1') 

CLIENT - APPROVER2 :

(471,'CLIENT-APPROVER2') 

CLIENT - APPROVER3 :

(461,'APPROVER1-APPROVER3')

CLIENT - APPROVER4:

(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(444,'J6 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4');

APPROVER 1 -APPROVER 2:

(460,'APPROVER1-APPROVER2')

APPROVER 2 - APPROVER 3:

(463,'APPROVER2-APPROVER3')

APPROVER 3 - APPROVER 4:

(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4');

THIS IS IMPORTANT: when a client is linked to one approver, a NEW RELATION is created inside relationships table.

Table relationships:

(787,459,12,18),
(788,460,18,20),
(789,463,20,21),
(790,467,21,26);

787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED

459 REPRESENTS THE RELATION: CLIENT - APPROVER

12 CHAIN1-MathAndre is the client

18 is the approver

Following the idea:

APPROVER1 was linked to APPROVER2:

(788,460,18,20)

APPROVER2 was linked to APPROVER3:

(789,463,20,21)

APPROVER3 was linked to APPROVER4:

(790,467,21,26) ​

So, i display this in screen:

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew    |   ZATCH   |   Ger     |    Mar    |    John  |
|CHAIN2-JohnConnor    |    MAX    |           |    Mario  |    Steven|
|CHAIN3-MarioShapiro  |    IVAN   |           |           |    John  |

This is my fiddle:

fiddle

This is my query:

WITH recursive relationships_CTE as (
  select e.id, e.description AS name, 1 col_id, 
    row_number() over (order by e.id) row_id
  from entities e
  where e.description like 'CHAIN%'
    UNION ALL
  select r.description_entitiy_2, e.name, col_id+ 1, row_id
  from relationships_CTE cte
  left join relationships r
    on r.description_entitiy_1 = cte.id
  join entities e 
    on r.description_entitiy_2 = e.id
)
select 
  max(case when col_id = 1 then name end) client,
  max(case when col_id = 2 then name end) approver1,
  max(case when col_id = 3 then name end) approver2,
  max(case when col_id = 4 then name end) approver3,
  max(case when col_id = 5 then name end) approver4
from relationships_CTE
group by row_id

NOW, THIS IS WHAT I WANT TO DO:

SUPPOSE that i have a new table called new_table and i modified table entities a little bit:

(12, 'Math Andrew', 308, 45)
(13, 'John Connor', 308, 46)
(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);

table new_table

(45,'Math Andrew', 'Chain1')
(45,'Math Andrew', 'Chain2')
(46, 'John Connor', 'Chain1')
(46, ''John Connor', 'Chain2')

and the table relationships would be like this:

(787,459,'45-Chain1',18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)

So, i want to join table entities with table new_table, get the relation 45-Chain1 and then, found 45-Chain1 within table relationships and get something like this (and do the same for all the different clients):

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|45-Chain1            |   ZATCH   |   Ger     |    Mar    |    John  |

I have been trying without success to solve this.

Could you please help me?

回答1:

I tried.
And normalized a bit extra, adding a chains table.

Here's my attempt :

create table entity_types 
(
 entity_type_id int primary key, 
 entity_type_name varchar(32) not null
);
create table relation_types 
(
 relation_type_id int primary key, 
 relation_type_name varchar(32) not null
);
create table chains 
(
 chain_id int primary key,
 chain_name varchar(30) not null
);
create table entities 
(
 entity_id int primary key,
 entity_name varchar(32) not null, 
 entity_type_id int not null,
 chain_id int,
 foreign key (entity_type_id)
         references entity_types(entity_type_id),
 foreign key (chain_id)
         references chains(chain_id)
);
create table relationships 
(
 relationship_id int primary key, 
 relation_type_id int not null,
 entity_id_1 int not null,
 entity_id_2 int not null,
 foreign key (relation_type_id)
         references relation_types(relation_type_id)
);
create table entity_chains 
(
 entity_id int not null,
 chain_id int not null,
 primary key (entity_id, chain_id),
 foreign key (chain_id)
         references chains(chain_id),
 foreign key (entity_id) 
         references entities(entity_id)
);
INSERT INTO entity_types
(entity_type_id, entity_type_name) VALUES
(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315,'J6 APPROVER4'),
(316,'J7 APPROVER4');
INSERT INTO relation_types
(relation_type_id, relation_type_name) VALUES
(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(448,'J4 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(450,'J10 CLIENT-APPROVER4'),
(451,'J3 CLIENT-APPROVER4'),
(452,'J8 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(454,'J6 CLIENT-APPROVER4'),
(455,'J7 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(458,'CLIENT-APPROVER3'),
(459,'CLIENT-APPROVER1'),
(460,'APPROVER1-APPROVER2'),
(461,'APPROVER1-APPROVER3'),
(462,'J3 APPROVER1-APPROVER4'),
(463,'APPROVER2-APPROVER3'),
(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4'),
(471,'CLIENT-APPROVER2');
insert into chains 
(chain_id, chain_name) values
(45,'Chain1'),
(46,'Chain2');
INSERT INTO entities 
(entity_id, entity_name, 
 entity_type_id, chain_id) VALUES
(12, 'Math Andrew', 308, 45),
(13, 'John Connor', 308, 46),
(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);
INSERT INTO relationships 
(relationship_id, relation_type_id,
 entity_id_1, entity_id_2) VALUES
(787,459,12,18),
(788,460,18,20),
(789,463,20,21),
(790,467,21,26);
insert into entity_chains 
(entity_id, chain_id) values
(12, 45),
(12, 46),
(13, 45),
(13, 46);
WITH RECURSIVE RCTE AS
(
  SELECT
   ent.chain_id,
   entch.entity_id as entity_id_0,
   0 as lvl,
   0 as entity_id_1,
   entch.entity_id as entity_id_2,
   0 as relation_type_id
  FROM entities ent
  JOIN entity_chains entch
    ON entch.chain_id = ent.chain_id

  UNION ALL

  SELECT 
  cte.chain_id,
  cte.entity_id_0,
  lvl+1,
  rel.entity_id_1,
  rel.entity_id_2,
  rel.relation_type_id
  FROM RCTE cte
  JOIN relationships rel
    ON rel.entity_id_1 = cte.entity_id_2
),
CTE AS
(
  SELECT
  rcte.*,
  chains.chain_name,
  ent0.entity_name as entity_name_0,
  -- reltype.relation_type_name,
  -- enttype2.entity_type_name as entity_type_name_2,
  -- ent1.entity_name as entity_name_1,
  ent2.entity_name as entity_name_2
  FROM RCTE rcte
  JOIN chains ON chains.chain_id = rcte.chain_id
  JOIN entities ent0 ON ent0.entity_id = rcte.entity_id_0
  JOIN entities ent2 ON ent2.entity_id = rcte.entity_id_2
  -- LEFT JOIN entity_types enttype2 ON enttype2.entity_type_id = ent2.entity_type_id
  -- LEFT JOIN relation_types reltype ON reltype.relation_type_id = rcte.relation_type_id
  -- LEFT JOIN entities ent1 ON ent1.entity_id = rcte.entity_id_1
)
/*
SELECT * FROM CTE WHERE lvl > 0
ORDER BY chain_name, entity_id_0, lvl;
*/
SELECT 
REPLACE(CONCAT(entity_name_0,'-',chain_name),' ','') as chain_client,
max(case when lvl=1 then entity_name_2 end) as approver1,
max(case when lvl=2 then entity_name_2 end) as approver2,
max(case when lvl=3 then entity_name_2 end) as approver3,
max(case when lvl=4 then entity_name_2 end) as approver4
FROM CTE cte
WHERE lvl > 0
GROUP BY chain_name, entity_name_0
ORDER BY chain_client;
chain_client      | approver1 | approver2 | approver3 | approver4
:---------------- | :-------- | :-------- | :-------- | :--------
MathAndrew-Chain1 | ZATCH     | Ger       | Mar       | John     
MathAndrew-Chain2 | ZATCH     | Ger       | Mar       | John     

db<>fiddle here