I have multiple tables all with a similar basic structure:
biopsy_p0
id | biopsy_id | introduced
biopsy_p1
id | biopsy_p0_id | introduced
biopsy_p2
id | biopsy_p1_id | introduced
My goal is to get a treeview of the dependencies bewteen
biopsy_p0.id->biopsy_p1.biopsy_p0_id->biopsy_p2.biopsy_p1_id
I tried to do it only with SQL but as you can see from my question I am not very experienced in it. Everything I could find so far are references about about hierarchical trees. But those use always only one table with internal references.
--- Update: I now got it working with PHP it is really not a nice solution and i hoped I could make it with SQL so that would be a bit better to extend:
PHP code:
$database = DatabaseFactory::getFactory()->getConnection();
// Get all p0 element asociated with the biopsy
$sql = "SELECT *
FROM biopsy_p0
WHERE biopsy_id = :id";
$query = $database->prepare($sql);
$query->execute(array(':id' => $id));
$p0 = $query->fetchAll();
// Get all p1 elements
$sql="SELECT *
FROM biopsy_p0 as p0
RIGHT JOIN biopsy_p1 as p1
ON p0.id=p1.biopsy_p0_id
WHERE biopsy_id = :id;";
$query = $database->prepare($sql);
$query->execute(array(':id' => $id));
$p1 = $query->fetchAll();
for ($i=0; $i < count($p0); $i++)
{
$p1Array = new ArrayObject();
foreach ($p1 as $key => $value)
{
if ($value->biopsy_p0_id == $p0[$i]->id)
{
$p1Array->append($value);
}
$p0[$i]->p1 = $p1Array;
}
unset($p1Array);
}
if ($p0 != NULL){
return $p0;
}
return FALSE;
Result: This is exactly what i need but the PHP is messy and it complexity increases with each child level I would like to check.
details: Array
(
[0] => stdClass Object
(
[id] => 1
[biopsy_id] => 226
[introduced] => 2014-12-31
[p1] => ArrayObject Object
(
[storage:ArrayObject:private] => Array
(
[0] => stdClass Object
(
[id] => 1
[biopsy_id] => 226
[introduced] => 2015-03-18
[biopsy_p0_id] => 1
)
[1] => stdClass Object
(
[id] => 3
[biopsy_id] => 226
[introduced] => 2015-03-17
[biopsy_p0_id] => 1
)
[2] => stdClass Object
(
[id] => 4
[biopsy_id] => 226
[introduced] => 2015-03-18
[biopsy_p0_id] => 1
)
)
)
)
[1] => stdClass Object
(
[id] => 2
[biopsy_id] => 226
[introduced] => 2014-12-31
[p1] => ArrayObject Object
(
[storage:ArrayObject:private] => Array
(
[0] => stdClass Object
(
[id] => 2
[biopsy_id] => 226
[introduced] => 2015-03-31
[biopsy_p0_id] => 2
)
[1] => stdClass Object
(
[id] => 6
[biopsy_id] => 226
[introduced] => 2015-03-01
[biopsy_p0_id] => 2
)
)
)
)
[2] => stdClass Object
(
[id] => 3
[biopsy_id] => 226
[introduced] => 2014-12-31
[p1] => ArrayObject Object
(
[storage:ArrayObject:private] => Array
(
[0] => stdClass Object
(
[id] => 5
[biopsy_id] => 226
[introduced] => 2015-03-11
[biopsy_p0_id] => 3
)
)
)
)
)
SQL Data:
CREATE TABLE IF NOT EXISTS `biopsy` (
`id` int(11) unsigned NOT NULL,
`creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;
INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');
CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL,
`biopsy_id` int(11) unsigned NOT NULL,
`introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14'),
(5, 255, '2015-03-10'),
(6, 255, '2015-03-12');
CREATE TABLE IF NOT EXISTS `biopsy_p1` (
`id` int(11) unsigned NOT NULL,
`biopsy_p0_id` int(11) unsigned NOT NULL,
`introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`) VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');
Maybe it would be better to have one hierarchical relation table that references via ID to another table that stores the actual data... so that I have only two tables involved and would be more flexible if new elements are added...
