I have decided to follow http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
So now I am looking for some help with the code.
I am using their data for my testing, So, I visualized the tree being like so:
array('value' => 'Richard Shakespeare',
array('value' => 'Henry',
array('value' => 'Joan'),
array('value' => 'Margaret'),
array('value' => 'William',
array('value' => 'Susana',
array('value' => 'Elizabeth Hall',
array('value' => 'John Bernard'))),
array('value' => 'Hamnet'),
array('value' => 'Judith',
array('value' => 'Shakespeare Quiney'),
array('value' => 'Richard Quiney'),
array('value' => 'Thomas Quiney'))),
array('value' => 'Gilbert'),
array('value' => 'Joan',
array('value' => 'William Hart'),
array('value' => 'Mary Hart'),
array('value' => 'Thomas Hart'),
array('value' => 'Micheal Hart')),
array('value' => 'Anne'),
array('value' => 'Richard'),
array('value' => 'Edmond')),
array('value' => 'John'));
So if we want to insert that into the database we want to end up with
Array
(
[0] => Array
(
[value] => Richard Shakespeare
[left] => 1
[right] => 46
)
[1] => Array
(
[value] => Henry
[left] => 2
[right] => 43
)
[2] => Array
(
[value] => Joan
[left] => 3
[right] => 4
)
[3] => Array
(
[value] => Margaret
[left] => 5
[right] => 6
)
[4] => Array
(
[value] => William
[left] => 7
[right] => 24
)
[5] => Array
(
[value] => Susana
[left] => 8
[right] => 13
)
[6] => Array
(
[value] => Elizabeth Hall
[left] => 9
[right] => 12
)
[7] => Array
(
[value] => John Bernard
[left] => 10
[right] => 11
)
[8] => Array
(
[value] => Hamnet
[left] => 14
[right] => 15
)
[9] => Array
(
[value] => Judith
[left] => 16
[right] => 23
)
[10] => Array
(
[value] => Shakespeare Quiney
[left] => 17
[right] => 18
)
[11] => Array
(
[value] => Richard Quiney
[left] => 19
[right] => 20
)
[12] => Array
(
[value] => Thomas Quiney
[left] => 21
[right] => 22
)
[13] => Array
(
[value] => Gilbert
[left] => 25
[right] => 26
)
[14] => Array
(
[value] => Joan
[left] => 27
[right] => 36
)
[15] => Array
(
[value] => William Hart
[left] => 28
[right] => 29
)
[16] => Array
(
[value] => Mary Hart
[left] => 30
[right] => 31
)
[17] => Array
(
[value] => Thomas Hart
[left] => 32
[right] => 33
)
[18] => Array
(
[value] => Micheal Hart
[left] => 34
[right] => 35
)
[19] => Array
(
[value] => Anne
[left] => 37
[right] => 38
)
[20] => Array
(
[value] => Richard
[left] => 39
[right] => 40
)
[21] => Array
(
[value] => Edmond
[left] => 41
[right] => 42
)
[22] => Array
(
[value] => John
[left] => 44
[right] => 45
)
)
So the issue comes to mind of, How best to do this?
My solution was:
$container = array();
function children($item){
$children = 0;
foreach($item as $node)
if(is_array($node))
$children += children($node)+1;
return $children;
}
function calculate($item, &$container, $data = array(0,0)){
//althought this one is actually of no use, it could be useful as it contains a count
$data[0]++; //$left
$right = ($data[0]+(children($item)*2))+1;
//store the values in the passed container
$container[] = array(
'value' => $item['value'],
'left' => $data[0],
'right' => $right,
);
//continue looping
$level = $data[1]++;
foreach($item as &$node)
if(is_array($node))
$data = calculate($node, $container, $data);
$data[1] = $level;
$data[0]++;
return $data;
}
calculate($tree, $container);
How efficient it is I do not know.
But now onto the queries.
To select all descendants of a node we can use
SELECT child.value AS 'Descendants of William', COUNT(*) AS `Level`
FROM tester AS parent
JOIN tester AS child ON child.`left` BETWEEN parent.`left` AND parent.`right`
WHERE parent.`left` > 7 AND parent.`right` < 24
GROUP BY child.value ORDER BY `level`;
To select all descendants of a node, to a specific depth we can use
Note that we are selecting Descendants of William to a depth of 2
Williams left: 7, Williams right: 24, Levels: 2
SELECT child.value AS 'Descendants of William', COUNT(*) AS `Level`
FROM tester AS parent
JOIN tester AS child ON child.`left` BETWEEN parent.`left` AND parent.`right`
WHERE parent.`left` > 7 AND parent.`right` < 24
GROUP BY child.value HAVING `level` <= 2 ORDER BY `level`;
So that's easy enough.
But now I want to know a few things,
Note that in the actual database as well as left/right all rows have a unique id, and a "parent" column containing their inviteers id, or null if not invited
- Lets say I want to insert
Davidas a child ofJudith, How do I do that? - Lets say I want to get
Mary Hart'sParent, and the Parents Parent (array('Henery', 'Joan', 'Mary Hart')), How do I do that? - Lets say I want to delete
William HartfromJoanHow so I do that?