I have a MySQL query that I use with PHP to build hierarchical JSON for a d3.js tree/dendrogram.

Fiddle to view schema and existing queries.

I am now considering how to add additional data relationships Dbetween program_outcome dataOand unit dataUand development_level data, with multiple pairs between them Many relationships.

There are only three types ofD, as shown in the figure below.

Concept map I need:

EachUis related to only oneDof eachO(only oneObranch is shown for clarity).

So U needs to be the descendant ofDand the grandson ofO. The sameUmay have the same or different type ofDfor otherObranches.

As you can see in the fiddle, the relationship betweenOandUis currently implemented via the lookup/relationship tableprogram_outcome_unit_lookup.

Also, it is possible to change the lookup tables so there are two lookup tables instead of theprogram_outcome_unit_lookuptable, which might work:

O->U

D->U

Any ideas how to achieve this?

The PHP after the query (not in the database fiddle...) is as follows, but may not be relevant to the solution, this is essentially a database issue.

$result = $connection->query($query); $data = array(); while ($row = $result->fetch_object()) { $data[$row->global_id] = $row; } $roots = array(); foreach ($data as $row) { $row->type = end(explode(",",(implode(array_slice(explode ( ':', $row->global_id), -2, 1))))); if ($row->parent_global_id === null) { $roots[]= $row; } else { $data[$row->parent_global_id]->children[] = $row; } unset($row->parent_global_id); unset($row->global_id); } $json = json_encode($roots); $json = trim($json, '[]'); $fp = fopen('data.json', 'w'); fwrite($fp, $json); fclose($fp);

renew

View the expanded concept diagram with two branches:

CREATE TABLE `program_outcome_unit_lookup` ( `program_outcome_unit_lookup_pk` int(6) NOT NULL AUTO_INCREMENT, `program_outcome_fk` int(2) NOT NULL, `devdata_fk` int(2) NOT NULL, `unit_fk` int(2) NOT NULL, PRIMARY KEY (`program_outcome_unit_lookup_pk`), UNIQUE KEY (`program_outcome_fk`, `unit_fk`) );