MYSQL complex Union

I have the following MySQL query working so far:

SELECT CONCAT('program:', program_pk) AS global_id,
       program_name AS name,
       NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
       theme_name AS name,
       CONCAT('program:', program_pk) AS parent_global_id
FROM theme CROSS JOIN program
UNION ALL
SELECT DISTINCT
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS global_id,
       strand_name AS name,
       CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT CONCAT('strand_year:', strand_pk) AS global_id,
       strand.year AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strand
UNION ALL
SELECT CONCAT('strand_year:', strand_pk, ',unit:', unit_pk) AS global_id,
       CONCAT(unit.unit_code, ' ', unit.unit_name) AS name,
       CONCAT('strand_year:', strand_pk) AS parent_global_id
FROM strand LEFT JOIN unit ON strand.year = unit.year

db-fiddle

Table program

+------------+--------------+
| program_pk | program_name |
+------------+--------------+

Table theme

+----------+------------+
| theme_pk | theme_name |
+----------+------------+

Table strand

+-----------+-------------+----------+------+
| strand_pk | strand_name | theme_fk | year |
+-----------+-------------+----------+------+

Table unit

+---------+-----------+-----------+--------+------+----------+
| unit_pk | unit_code | unit_name | points | year | theme_fk |
+---------+-----------+-----------+--------+------+----------+

The relationship is:

program -> theme -> strand -> year -> unit

I now need to add to the query the table learning_event

Table learning_event

+-------------------+---------------------+---------+-----------+----------------+
    | learning_event_pk | learning_event_name | unit_fk | strand_fk | core_condition |
    +-------------------+---------------------+---------+-----------+----------------+

to branch learning events from the parent unit to give:

program -> theme -> strand -> year -> unit -> learning event

Note that only learning events related to the strand should show for a given strand and unit.

I have played around with this, but really unsure of how to get this to work with relating learning events to unit and strand.

UPDATE

In JSON format, what I have with the existing query is like:

{
    "name": "MD",
    "children": [{
        "name": "Professional",
        "children": [{
            "name": "Professional Behavours",
            "children": [{
                "name": "Year 1",
                "children": [{
                    "name": "IMED4443 Integrated Medical Sciences 1"
                }, {
                    "name": "IMED4444 Integrated Medical Sciences 2"
                }]
            }

The new output I'm looking for is like:

"name": "MD",
"children": [{
        "name": "Professional",
        "children": [{
                "name": "Professional Behavours",
                "children": [{
                        "name": "Year 1",
                        "children": [{
                                "name": "IMED4443 Integrated Medical Sciences 1"
                            }, {
                                "name": "IMED4444 Integrated Medical Sciences 2",
                                "children": [{
                                    "name": "Lecture - CVS"
                                }, {
                                    "name": "Lecture - Type 1 Diabetes"
                                }...

and the learning events should only show where this is a relationship with the unit AND the strand.

FYI, the relationships are handled with:

$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
    $data[$row->global_id] = $row;
}

$roots = array();
foreach ($data as $row) {   
    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);

from MYSQL complex Union