Hello, everybody!
I want to ask something that has already been asked several times but there is still no clear solution. My initial query gives me the set of events, each of these have child_id...
See more...
Hello, everybody!
I want to ask something that has already been asked several times but there is still no clear solution. My initial query gives me the set of events, each of these have child_id and parent_id fields. Sample data looks like this:
child_id | parent_id
********************
null | A1
null | B1
A1 | A2
B1 | B2
A2 | C1
B2 | C1
C1 | C2
C2 | D1
C2 | E1
So, the elements on the bottom of the hierarchy has their child_id = null . The depth of parent-child relationships is not known in advance. I wonder, how can I restore the these events into the hierarchy, so if I set a specific event my search would return to me only this event and all events which are parent events? For example:
If I search child_id=B2 I need to get two events for child_id=B2 (root) and child_id=B1 (1 child) as results
If I search child_id=C1 I need to get five events for child_id=C1 (root) and child_id=A2 , child_id=B2 , child_id=A1 , child_id=B1 (4 childs) as results, etc.
In any words, I need to get chains from the initial data:
child_id | chain
****************
A1 | A1
A2 | A2 -> A1
B1 | B1
B2 | B2 -> B1
C1 | C1 -> A2 -> A1
C1 | C1 -> B2 -> B1
C2 | C2 -> C1 -> A2 -> A1
C2 | C2 -> C1 -> B2 -> B1
D1 | D1 -> C2 -> C1 -> A2 -> A1
D1 | D1 -> C2 -> C1 -> B2 -> B1
E1 | E1 -> C2 -> C1 -> A2 -> A1
E1 | E1 -> C2 -> C1 -> B2 -> B1
I tried to achieve this with transaction and map but no luck at the moment. Looks like I need a kind of recursion. Is it maybe possible to implement a recursion by search macro, pointing to itself?