join() public static method

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.
 /**
  * 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;
 }
Example #2
0
 /**
  *
  *
  * @param $Data
  * @param string $Field
  * @param array $Columns
  * @throws
  * @throws Exception
  */
 public static function joinAddons(&$Data, $Field = 'AddonID', $Columns = array('Name'))
 {
     $Columns = array_merge(array('table' => 'Addon', 'column' => 'Addon'), $Columns);
     Gdn_DataSet::join($Data, $Columns, array('unique' => true));
 }