<?php
/**
* Normalise a flat dot notation array.
*
* @param array $rows The flat array
* @param string $dot The character that separates the layers.
*
* @return array
*/
function unflatten(array $rows, string $dot = "."): array
{
# The last row's main table columns
$last_main_table = [];
# A collection of all the joined tables belonging to a given row of the main table
$joined_tables = [];
# For each row in the result set
foreach($rows as $id => $row){
# Contains this row's main table columns
$main_table = [];
# Contains this row's joined table columns
$joined_table = [];
# Foreach column and value
foreach($row as $col => $val){
/**
* Whether a column name contains a period or not
* is the determining factor of whether the column
* belongs to the main table (no period) or a joined
* table (periods).
*/
if(!strpos($col, $dot)){
//if the column belongs to the main table
$main_table[$col] = $val;
} else {
//If the column belongs to a joined table
# Break open the column name
$col = explode($dot,$col);
# Extract out the joined table, and the column names (that may include joined children)
$joined_table[array_shift($col)][$id][implode($dot,$col)] = $val;
}
}
# If this is the first row, or if this row's main columns are the same as the last row
if(!$last_main_table || ($main_table == $last_main_table)){
# Update the last main table variable
$last_main_table = $main_table;
# merge all rows of columns belonging to joined tables
$joined_tables = array_merge_recursive($joined_tables ?:[], $joined_table ?:[]);
# Go to the next row
continue;
}
# At this point, the main columns are different from the previous row's main columns
# Add the last row's main columns, merged with all of it's joined table rows to the normalised array
$normalised[] = array_merge($last_main_table, $joined_tables);
# Update the last main table variable
$last_main_table = $main_table;
# Reset the joined tables
$joined_tables = [];
# Add this row's joined table columns
$joined_tables = array_merge_recursive($joined_tables ?:[], $joined_table ?:[]);
}
# Capture the last row
$normalised[] = array_merge($last_main_table, $joined_tables);
# Go deeper
foreach($normalised as $id => $row){
//For each row that is now normalised
foreach($row as $key => $val){
//For each column value
if(is_array($val)){
//if any of the values are arrays, make sure that array is also normalised
$normalised[$id][$key] = unflatten($val, $dot);
}
}
}
return $normalised;
}
$results[] = [
'id' => 'A1',
'address.id' => '11f456A1',
'address.coordinates.lat' => '12.345',
'address.coordinates.lng' => '67.89',
'address.coordinates.geo.accurate' => true,
];
$results[] = [
'id' => 'A1',
'address.id' => '22f456A1',
'address.coordinates.lat' => '12.345',
'address.coordinates.lng' => '67.89',
'address.coordinates.geo.accurate' => true,
];
$results[] = [
'id' => 'A2',
'address.id' => '44f456A2',
'address.coordinates.lat' => '11.345',
'address.coordinates.lng' => '67.89',
'address.coordinates.geo.accurate' => true,
];
$results[] = [
'id' => 'A2',
'address.id' => '44f456A2',
'address.coordinates.lat' => '22.345',
'address.coordinates.lng' => '67.89',
'address.coordinates.geo.accurate' => true,
];
$normalised = unflatten($results);
var_export($normalised);