0

I have different tables and the goal is to obtain the approval workflow for every customer

Customers have different approval workflows, take a look at this:

In my table "entities" i have this

(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew').

It means that when the row was created the number 12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT

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');

Because Math Andrew is a CLIENT (also known as CUSTOMER) he 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:

(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);

Types of relations between entities:

(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');

This is the important part: when a client is linked to one approver, a relation is created inside relationships table.

In this case MathAndrew was linked to Approver #18 (ZATCH), THIS ROW WAS CREATED AFTER THE ASSIGNATION:

(787,459,'CHAIN1-MathAndrew',18)--

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

REPRESENTS THE RELATION CLIENT - APPROVER

CHAIN1-MathAndre is the

client 18 is the approver

Also, in this case APPROVER1 was linked to APPROVER2

(788,460,18,20)

Then, APPROVER2 was linked to APPROVER3

(789,463,20,21)

Finally, APPROVER3 was linked to APPROVER4

(790,467,21,26)

I WANT TO OBTAIN THE COMPLETE APPROVAL WORKFLOW CHAIN, I mean this: CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

I did this but i am not getting what i want:

WITH relationships_CTE as
select description_entity_1,description_entitiy_2
from relationships
where description_entitiy_1 like 'CHAIN1-MathAndrew'

UNION ALL

select description_entity_1,description_entitiy_2
from relationships
where relationships.description_entitiy_2 = relationships_CTE.description_entitiy_2

select * 
from relationships_CTE ma
left join relationships_CTE na

This is my SQL FIDDLE:

http://sqlfiddle.com/#!9/51bb39/4

Could you please help me?

Community
  • 1
  • 1
JustToKnow
  • 785
  • 6
  • 23

1 Answers1

2

So you have a couple of major issues with your demo, firstly that you are trying to use a CTE on a version of MySQL that doesn't support it (CTE support was introduced in MySQL version 8), and secondly you are trying to insert a string into a column in the relationships table (which should have been left as a reference to the entities table. Having corrected those issues, we can look at the CTE. There you have a syntax error because you have not enclosed your CTE query in (), and also you have failed to declare the CTE as recursive (since it refers to itself).

Now, based on your question, you want to get names out of the entities table to correspond to the values in the relationships table. So we start the CTE by finding the appropriate entities.id value for CHAIN1-MathAndrew, and then in the recursive part of the CTE we loop through all the entities that are related to that entity, grabbing the names as we go. This gives us this query:

WITH recursive relationships_CTE as (
    select e.id, e.description AS name
    from entities e
    where e.description like 'CHAIN1-MathAndrew'
    UNION ALL
    select r.description_entitiy_2, e.name
    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
)

If we now

select *
from relationships_CTE

we get

id  name
12  CHAIN1-MathAndrew
18  ZATCH
20  Ger
21  Mar
26  John

or we can use GROUP_CONCAT to string those names together:

select group_concat(name separator '-')
from relationships_CTE

Output:

CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Thank you very much for replying, Nick..I am checking this out! :) – JustToKnow Nov 18 '19 at 02:30
  • I have a question. I would like to obtain a single row but showing columns: CLIENT|APPROVER1|APPROVER2|APPROVER3|APPROVER4. How can i do that? – JustToKnow Nov 18 '19 at 02:36
  • @Student_new that's a more complex problem again. It would depend on how many approvers can be in the chain. If this works for you, I would accept and then ask a new question referring back to this one. If this isn't working, please let me know and we can debug... – Nick Nov 18 '19 at 02:57
  • @Student_new another option would be to take the concatenated output from the second query and split it in your application. That can often be simpler than attempting to build a pivot table in DBMS such as MySQL that don't support it natively. – Nick Nov 18 '19 at 02:59
  • yeah, you are right.I am gonna create a new question. Please, stay sharp! – JustToKnow Nov 18 '19 at 03:02
  • @Student_new I'll keep an eye out for it. Don't forget to say if there is a maximum number of approvers (and if so, what it is). Also if this is running in an application framework, be open to solutions there. – Nick Nov 18 '19 at 03:07
  • take a look (the new question) https://stackoverflow.com/questions/58907969/trying-to-obtain-the-accurate-information-cte-recursive – JustToKnow Nov 18 '19 at 04:24
  • Hey Nick, how are you. A guy replied to my question but i would like to see your point of you :) – JustToKnow Nov 18 '19 at 14:10
  • Nick, could you please take a look at this: https://stackoverflow.com/questions/59021115/tricky-cte-recursive-sql-editing-my-query – JustToKnow Nov 24 '19 at 18:42
  • I dont know how to modify it :( – JustToKnow Nov 24 '19 at 22:26