PHP、MySQL 和多重联接查询结果。有没有简单的方法可以将结果"convert"到层次结构中？

Question

假设我有这样的查询结果。最初，它们是绝对平坦的行(像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指挥家:nametbody> <<tr>111

id	name	instruments:id	instruments:type	instruments:manufacturers_id	players:id	players:name	演奏者:name	演奏者:id	指挥家:name	指挥家:name	指挥家:name	指挥家:name	指挥家:name	指挥家:name	指挥家:name	指挥家:name	1	1	哈利斯科爱乐乐团	1	小提琴	1	1	1	Aadu Vahtkumm	1	弦乐器	1	1	Aadu Vahtkumm	1
	1	哈利斯科爱乐乐团	1	小提琴	1	1	2	Leelo Luhvt	1	弦乐器	1	1	Aadu Vahtkumm	1
	1	哈利斯科爱乐乐团	1	小提琴	1	1	3	Valdur沃尔	1	弦乐器	1	1	Aadu Vahtkumm	1
	1	哈利斯科爱乐乐团	1	小提琴	1	1	34	第一去年	1	弦乐器	1	1	Aadu Vahtkumm	1

我建议不要使用这种方法，但是您可以扩展现有的逻辑来手动构建它-

$out = [];
while ($row = mysqli_fetch_assoc($result)) {
$out[$row['rowid']]['rowid'] = $row['rowid'];
$out[$row['rowid']]['id'] = $row['id'];
$out[$row['rowid']]['name'] = $row['name'];
$out[$row['rowid']]['hasMany']['conductors'][$row['conductors:id']]['id'] = $row['conductors:id'];
$out[$row['rowid']]['hasMany']['conductors'][$row['conductors:id']]['name'] = $row['conductors:name'];
$out[$row['rowid']]['hasMany']['conductors'][$row['conductors:id']]['orchestraId'] = $row['conductors:orchestraId'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['id'] = $row['instruments:id'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['type'] = $row['instruments:type'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['manufacturers_id']['value'] = $row['instruments:manufacturers_id'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['manufacturers_id']['parentKey'] = 'id';
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['manufacturers_id']['table'] = 'manufacturers';
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['manufacturers_id']['data']['id'] = $row['manufacturers:id'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['manufacturers_id']['data']['name'] = $row['manufacturers:name'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['belongsTo']['orchestraId']['value'] = $row['id'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['hasMany']['players'][$row['players:id']]['id'] = $row['players:id'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['hasMany']['players'][$row['players:id']]['name'] = $row['players:name'];
$out[$row['rowid']]['hasMany']['instruments'][$row['instruments:id']]['hasMany']['players'][$row['players:id']]['belongsTo']['instrument_id']['value'] = $row['instruments:id'];
}

如果你想沿着这条路走下去，我建议你读一些关于对象关系映射器(orm)的书。