I have a table where I store tasks for a Gantt chart. Each task has a start date, a duration and a percentage of completeness.
This is an extract of my table for a single project
| id | text | start_date | duration | progress | parent | sortorder | project |
|---|---|---|---|---|---|---|---|
| 1 | DT Next phase 1 | 2023-02-23 00:00:00 | 281 | 0 | 0 | 1 | 1 |
| 2 | PREPARE | 2023-02-23 00:00:00 | 19 | 0 | 1 | 2 | 1 |
| 3 | Prepare | 2023-02-23 00:00:00 | 19 | 0 | 2 | 3 | 1 |
| 4 | EXPLORE | 2023-03-23 00:00:00 | 122 | 0.9 | 1 | 4 | 1 |
| 5 | Bbp definition | 2023-03-23 00:00:00 | 113 | 1 | 4 | 5 | 1 |
| 6 | Bbp approval | 2023-08-01 00:00:00 | 5 | 0 | 4 | 6 | 1 |
| 7 | REALIZE | 2023-07-23 00:00:00 | 153 | 0.5 | 1 | 7 | 1 |
| 8 | System config | 2023-08-01 00:00:00 | 122 | 0.2 | 7 | 8 | 1 |
| 9 | Configure sys | 2023-08-01 00:00:00 | 145 | 0.4 | 7 | 9 | 1 |
| 10 | Unit test | 2023-07-23 00:00:00 | 123 | 0 | 7 | 10 | 1 |
| 11 | Custom dev | 2023-07-23 00:00:00 | 3 | 0 | 7 | 11 | 1 |
| 12 | Func spec | 2023-07-23 00:00:00 | 62 | 0 | 7 | 12 | 1 |
| 13 | Tech spec | 2023-07-23 00:00:00 | 73 | 0 | 7 | 13 | 1 |
| 14 | Unit test | 2023-07-23 00:00:00 | 92 | 0 | 7 | 14 | 1 |
| 15 | Data Migration | 2023-08-23 00:00:00 | 122 | 0 | 7 | 15 | 1 |
| 16 | Mock 1 | 2023-08-23 00:00:00 | 31 | 0 | 15 | 16 | 1 |
| 17 | Mock 2 | 2023-09-23 00:00:00 | 30 | 0 | 15 | 17 | 1 |
| 18 | Mock 3 | 2023-10-23 00:00:00 | 31 | 0 | 15 | 18 | 1 |
This table is used to display the GANTT graph and the user can update the progress of each activity. What I don’t have so far is to update the progress of the parents up to the top level and I am trying to build a trigger for that.
Assume that the user will set row 18 progress to 0.6 (60%). It means that the progress is of 310.6 days = 18.6 days. Row 18 parent is 15 so I need to do duration * progress of each row with parent = 15 and get the total: 310+300+310.6=18.6
Then I need to take duration on row 15 and do 18.6/122 = 0.15 that is the progress of row 15. So I need to update row 15 with progress = 0.15.
Then, row 15 parent is 7 so I have to do the same with all the rows with parent 7 and update row 7.
Row 7 parent is 1 and again the same math. Is it possible with one or more query in a Mysql Trigger?