假设我有这样的查询结果，最初它们是绝对平坦的行（就像MySQL查询结果总是这样）--我只是设法返回它们，以便当前的“主记录”是唯一的，其余的是相关数据的数组。
但是如何用php把它们全部“转换”成层次结构呢？有没有好的库等等？
如您所见，相关数据的字段有别名tablename：fieldname，这有助于区分源表。主数据有原始字段名，因此很容易按主记录pk值对数据进行分组。
好了，这是初步转换后的数据，下面是最终目标。
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
)
)
)
)最后的预期目标是这样的。一些解释：管弦乐队可以有许多乐器和指挥。乐器可以有许多演奏者。但每种乐器也有（“属于”）一个制造商。
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
)
)
)
)
)
)
)
)
)正如@nnichols所建议的
1.示例结果为表视图（我猜它会太大）
| 行标识符|标识符|姓名|仪器：ID|乐器：类型|仪器：制造商标识|乐器：管弦乐队ID|玩家：id|玩家：名称|播放器：乐器标识|制造商：名称|制造商：id|导体：ID|导体：名称|指挥：管弦乐队ID|
| - -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|
| 一个|一个|哈利斯科爱乐乐团|一个|小提琴|一个|一个|一个|阿杜·瓦特库姆|一个|斯特拉迪瓦里|一个|一个|阿杜·瓦赫库姆|一个|
| 一个|一个|哈利斯科爱乐乐团|一个|小提琴|一个|一个|2个|利洛·卢赫特|一个|斯特拉迪瓦里|一个|一个|阿杜·瓦特库姆|一个|
| 一个|一个|哈利斯科爱乐乐团|一个|小提琴|一个|一个|三个|瓦尔杜尔·沃尔|一个|斯特拉迪瓦里|一个|一个|阿杜·瓦特库姆|一个|
| 一个|一个|哈利斯科爱乐乐团|一个|小提琴|一个|一个|三十四|名末|一个|斯特拉迪瓦里|一个|一个|阿杜·瓦特库姆|一个|
这是初始分组的代码
$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);这里还有在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"
}
}
}
}
1条答案
按热度按时间4urapxun1#
我建议不要使用这种方法，但是您可以扩展现有的逻辑来手动构建它-
如果你想沿着这条路走下去，我建议你读一些关于对象关系Map器（ORM）的书。