max / goingson
| 1 | -- Backfill recurrence_parent_id for existing recurring tasks. |
| 2 | -- Links completed recurring tasks to their earliest sibling (the chain root) |
| 3 | -- by matching on description + recurrence pattern + user_id. |
| 4 | -- |
| 5 | -- This is best-effort: tasks whose description was edited after spawning |
| 6 | -- won't match, but those are rare edge cases. |
| 7 | |
| 8 | UPDATE tasks |
| 9 | SET recurrence_parent_id = ( |
| 10 | SELECT MIN(t2.id) FROM tasks t2 |
| 11 | WHERE t2.user_id = tasks.user_id |
| 12 | AND t2.description = tasks.description |
| 13 | AND t2.recurrence = tasks.recurrence |
| 14 | AND t2.recurrence != 'None' |
| 15 | ) |
| 16 | WHERE recurrence != 'None' |
| 17 | AND recurrence_parent_id IS NULL; |
| 18 | |
| 19 | -- Don't set recurrence_parent_id to self (the root task itself). |
| 20 | UPDATE tasks |
| 21 | SET recurrence_parent_id = NULL |
| 22 | WHERE recurrence_parent_id = id; |
| 23 |