| public static join ( array &$Data, array $Columns, array $Options = [] ) | ||
| $Data | array | |
| $Columns | array | The columns/table information for the join. Depending on the argument's index it will be interpreted differently. - numeric: This column will come be added to the resulting join. The value can be either a string or a two element array where the second element specifies an alias. - alias: The alias of the child table in the query. - child: The name of the child column. - column: The name of the column to put the joined data into. Can't be used with prefix. - parent: The name of the parent column. - table: The name of the child table in the join. - prefix: The name of the prefix to give the columns. Can't be used with column. |
| $Options | array | An array of extra options. - sql: A Gdn_SQLDriver with the child query. - type: The join type, either JOIN_INNER, JOIN_LEFT. This defaults to JOIN_LEFT. |
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]);
}
}
} /**
* Get a list of conversations for a user's inbox. This is an optimized version of ConversationModel::get().
*
* @param int $UserID The user looking at the conversations.
* @param int $Offset Number to skip.
* @param int $Limit Maximum to return.
*/
public function get2($UserID, $Offset = 0, $Limit = 0)
{
if ($Limit <= 0) {
$Limit = c('Conversations.Conversations.PerPage', 30);
}
// The self join is intentional in order to force the query to us an index-scan instead of a table-scan.
$Data = $this->SQL->select('c.*')->select('uc2.DateLastViewed')->select('uc2.CountReadMessages')->select('uc2.LastMessageID', '', 'UserLastMessageID')->from('UserConversation uc')->join('UserConversation uc2', 'uc.ConversationID = uc2.ConversationID and uc.UserID = uc2.UserID')->join('Conversation c', 'c.ConversationID = uc2.ConversationID')->where('uc.UserID', $UserID)->where('uc.Deleted', 0)->orderBy('uc.DateConversationUpdated', 'desc')->limit($Limit, $Offset)->get();
$Data->datasetType(DATASET_TYPE_ARRAY);
$Result =& $Data->result();
// Add some calculated fields.
foreach ($Result as &$Row) {
if ($Row['UserLastMessageID']) {
$Row['LastMessageID'] = $Row['UserLastMessageID'];
}
$Row['CountNewMessages'] = $Row['CountMessages'] - $Row['CountReadMessages'];
unset($Row['UserLastMessageID']);
}
// Join the participants.
$this->joinParticipants($Result);
// Join in the last message.
Gdn_DataSet::join($Result, array('table' => 'ConversationMessage', 'prefix' => 'Last', 'parent' => 'LastMessageID', 'child' => 'MessageID', 'InsertUserID', 'DateInserted', 'Body', 'Format'));
return $Data;
}
