Implement adding a second parent to an element using SQL
When the element already has an existing parent, we can add a new parent using just 1 INSERT … SELECT query. We only need to remove the path of the existing parent and add the new parent's path to get new rows.
Example: A.add_parent(Y)
| Element | Path | Ordering |
|---|---|---|
| X | [] | 0 |
| Y | [] | 0 |
| A | X | 0 |
| A |
|
0 |
| B | X A | 0 |
| B |
|
0 |
| C | X A | 1 |
| C |
|
1 |
@startmindmap
+ A
++ B
++ C
-- X
-- Y
@endmindmapA, B, C all already had paths that end in X. We strip off X and everything before X from each path, and add all of the new parent's paths to it to create new paths.
We should try to implement this INSERT…SELECT after the initial SELECT query that looks for the child and the parent's existing paths, as a shortcut: if the element already has a path, then we can use this SQL query. Otherwise, we will for now fall back to the existing Python code.
This is a first step towards implementing ElementPath edition in SQL. Most of the work will be on learning the correct array manipulation functions, optimizing the various required joins, and possibly adding missing test cases (a parent with multiple paths on a child with multiple paths for example), all of which will be a bit lighter for subsequent issues.