Let's say I have such query results. Originally they are absolutely flat rows (like MySQL query results always are) - I just managed to return them so that the current "main record" is unique and the rest is an array of related data.
But how to "convert" them all into hierarchical structure with php? Any good library etc?
As you see, fields of related data have alias names tablename:fieldname which helps to distinguish the tables of origin. The main data had original field names so it was easy to group the data by main records pk value.
OK, here is the data after the initial conversion. The final goal follows.
Array
(
[1] => Array
(
[rowid] => 1
[id] => 1
[name] => Jalisco Philharmonic
[related] => Array
(
[0] => Array
(
[instruments:id] => 1
[instruments:type] => violin
[instruments:manufacturers_id] => 1
[instruments:orchestraId] => 1
[players:id] => 1
[players:name] => Aadu Vahtkumm
[players:instruments_id] => 1
[manufacturers:name] => Stradivari
[manufacturers:id] => 1
[conductors:id] => 1
[conductors:name] => Aadu Vahtkumm
[conductors:orchestraId] => 1
)
[1] => Array
(
[instruments:id] => 1
[instruments:type] => violin
[instruments:manufacturers_id] => 1
[instruments:orchestraId] => 1
[players:id] => 2
[players:name] => Leelo Luhvt
[players:instruments_id] => 1
[manufacturers:id] => 1
[manufacturers:name] => Stradivari
[conductors:id] => 1
[conductors:name] => Aadu Vahtkumm
[conductors:orchestraId] => 1
)
[2] => Array
(
[instruments:id] => 1
[instruments:type] => violin
[instruments:manufacturers_id] => 1
[instruments:orchestraId] => 1
[players:id] => 3
[players:name] => Valdur Voll
[players:instruments_id] => 1
[manufacturers:id] => 1
[manufacturers:name] => Stradivari
[conductors:id] => 1
[conductors:name] => Aadu Vahtkumm
[conductors:orchestraId] => 1
)
[3] => Array
(
[instruments:id] => 1
[instruments:type] => violin
[instruments:manufacturers_id] => 1
[instruments:orchestraId] => 1
[players:id] => 34
[players:name] => First Last
[players:instruments_id] => 1
[manufacturers:id] => 1
[manufacturers:name] => Stradivari
[conductors:id] => 1
[conductors:name] => Aadu Vahtkumm
[conductors:orchestraId] => 1
)
)
)
)
And the final expected goal looks like this. Some explanations: orchestra can have here many instruments and conductors. Instrument can have many players. But each instrument also have ("belongs to") a manufacturer.
Array
(
[1] => Array
(
[rowid] => 1
[id] => 1
[name] => Jalisco Philharmonic
[hasMany] => Array
(
[conductors] => Array
(
[id] => 1
[name] => Aadu Vahtkumm
[orchestraId] => 1
)
[instruments] => Array
[1] => Array
(
[id] => 1
[type] => violin
[belongsTo] => Array
(
[manufacturers_id] => Array
(
[value] => 1,
[parentKey] => id,
[table] => manufacturers,
[data] => Array
(
[id] => 1
[name] => Stradivari
)
),
[orchestraId] => Array
(
[value] => 1
)
),
[hasMany] => Array
(
[players] => Array
(
[1] => Array
(
[id] => 1
[name] => Aadu Vahtkumm
[belongsTo] => Array
(
[instruments_id] => Array
(
[value] => 1
)
)
)
[2] => Array
(
[id] => 2
[name] => Leelo Luhvt
[belongsTo] => Array
(
[instruments_id] => Array
(
[value] => 1
)
)
)
[3] => Array
(
[id] => 3
[name] => Valdur Voll
[belongsTo] => Array
(
[instruments_id] => Array
(
[value] => 1
)
)
)
[34] => Array
(
[id] => 34
[name] => First Last
[belongsTo] => Array
(
[instruments_id] => Array
(
[value] => 1
)
)
)
)
)
)
)
)
)
As @nnichols suggested
- example result as table view (I guess it will be too large)
| rowid | id | name | instruments:id | instruments:type | instruments:manufacturers_id | instruments:orchestraId | players:id | players:name | players:instruments_id | manufacturers:name | manufacturers:id | conductors:id | conductors:name | conductors:orchestraId |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Jalisco Philharmonic | 1 | violin | 1 | 1 | 1 | Aadu Vahtkumm | 1 | Stradivari | 1 | 1 | Aadu Vahtkumm | 1 |
| 1 | 1 | Jalisco Philharmonic | 1 | violin | 1 | 1 | 2 | Leelo Luhvt | 1 | Stradivari | 1 | 1 | Aadu Vahtkumm | 1 |
| 1 | 1 | Jalisco Philharmonic | 1 | violin | 1 | 1 | 3 | Valdur Voll | 1 | Stradivari | 1 | 1 | Aadu Vahtkumm | 1 |
| 1 | 1 | Jalisco Philharmonic | 1 | violin | 1 | 1 | 34 | First Last | 1 | Stradivari | 1 | 1 | Aadu Vahtkumm | 1 |
And here is the code which made its initial grouping
$dataRows = [];
$mainRow = [];
while ($row = mysqli_fetch_assoc($result)) {
$rowid = $row['rowid'];
while (!strpos(key($row), ':')) {
$mainRow[key($row)] = $row[key($row)];
unset($row[key($row)]);
}
$mainRow['related'][] = $row;
$dataRows[$rowid] = $mainRow;
}
echo json_encode($dataRows);
Here also tables and relations defined in json
{
"instruments": {
"pk": "id",
"data": {},
"belongsTo": {
"orchestraId": {
"parentKey": "id",
"label": "name",
"table": "orchestras"
},
"manufacturers_id": {
"parentKey": "id",
"label": "name",
"table": "manufacturers"
}
}
},
"manufacturers": {
"pk": "id",
"label": "name",
"data": {}
},
"orchestras": {
"pk": "id",
"data": {}
},
"conductors": {
"pk": "id",
"data": {},
"belongsTo": {
"orchestraId": {
"parentKey": "id",
"label": "name",
"table": "orchestras"
}
}
},
"players": {
"pk": "id",
"data": {},
"belongsTo": {
"instruments_id": {
"parentKey": "id",
"label": "type",
"table": "instruments"
}
}
}
}
