public static function join(&$Data, $Columns, $Options = array())
{
$Options = array_change_key_case($Options);
$Sql = Gdn::sql();
//GetValue('sql', $Options, Gdn::SQL());
$ResultColumns = array();
// Grab the columns.
foreach ($Columns as $Index => $Name) {
if (is_numeric($Index)) {
// This is a column being selected.
if (is_array($Name)) {
$Column = $Name[0];
$ColumnAlias = $Name[1];
} else {
$Column = $Name;
$ColumnAlias = '';
}
if (($Pos = strpos($Column, '.')) !== false) {
$Sql->select($Column, '', $ColumnAlias);
$Column = substr($Column, $Pos + 1);
} else {
$Sql->select(isset($TableAlias) ? $TableAlias . '.' . $Column : $Column, '', $ColumnAlias);
}
if ($ColumnAlias) {
$ResultColumns[] = $ColumnAlias;
} else {
$ResultColumns[] = $Column;
}
} else {
switch (strtolower($Index)) {
case 'alias':
$TableAlias = $Name;
break;
case 'child':
$ChildColumn = $Name;
break;
case 'column':
$JoinColumn = $Name;
break;
case 'parent':
$ParentColumn = $Name;
break;
case 'prefix':
$ColumnPrefix = $Name;
break;
case 'table':
$Table = $Name;
break;
case 'type':
// The type shouldn't be here, but handle it.
$Options['Type'] = $Name;
break;
default:
throw new Exception("Gdn_DataSet::Join(): Unknown column option '{$Index}'.");
}
}
}
if (!isset($TableAlias)) {
if (isset($Table)) {
$TableAlias = 'c';
} else {
$TableAlias = 'c';
}
}
if (!isset($ParentColumn)) {
if (isset($ChildColumn)) {
$ParentColumn = $ChildColumn;
} elseif (isset($Table)) {
$ParentColumn = $Table . 'ID';
} else {
throw Exception("Gdn_DataSet::Join(): Missing 'parent' argument'.");
}
}
// Figure out some options if they weren't specified.
if (!isset($ChildColumn)) {
if (isset($ParentColumn)) {
$ChildColumn = $ParentColumn;
} elseif (isset($Table)) {
$ChildColumn = $Table . 'ID';
} else {
throw Exception("Gdn_DataSet::Join(): Missing 'child' argument'.");
}
}
if (!isset($ColumnPrefix) && !isset($JoinColumn)) {
$ColumnPrefix = stringEndsWith($ParentColumn, 'ID', true, true);
}
$JoinType = strtolower(val('Type', $Options, self::JOIN_LEFT));
// Start augmenting the sql for the join.
if (isset($Table)) {
$Sql->from("{$Table} {$TableAlias}");
}
$Sql->select("{$TableAlias}.{$ChildColumn}");
// Get the IDs to generate an in clause with.
$IDs = array();
foreach ($Data as $Row) {
$Value = val($ParentColumn, $Row);
if ($Value) {
$IDs[$Value] = true;
}
}
$IDs = array_keys($IDs);
$Sql->whereIn($ChildColumn, $IDs);
$ChildData = $Sql->get()->resultArray();
$ChildData = self::index($ChildData, $ChildColumn, array('unique' => GetValue('unique', $Options, isset($ColumnPrefix))));
$NotFound = array();
// Join the data in.
foreach ($Data as $Index => &$Row) {
$ParentID = val($ParentColumn, $Row);
if (isset($ChildData[$ParentID])) {
$ChildRow = $ChildData[$ParentID];
if (isset($ColumnPrefix)) {
// Add the data to the columns.
foreach ($ChildRow as $Name => $Value) {
setValue($ColumnPrefix . $Name, $Row, $Value);
}
} else {
// Add the result data.
setValue($JoinColumn, $Row, $ChildRow);
}
} else {
if ($JoinType == self::JOIN_LEFT) {
if (isset($ColumnPrefix)) {
foreach ($ResultColumns as $Name) {
setValue($ColumnPrefix . $Name, $Row, null);
}
} else {
setValue($JoinColumn, $Row, array());
}
} else {
$NotFound[] = $Index;
}
}
}
// Remove inner join rows.
if ($JoinType == self::JOIN_INNER) {
foreach ($NotFound as $Index) {
unset($Data[$Index]);
}
}
}