PHP JDatabaseQuery::join方法代码示例

 /**
  * Adds Count Items for Category Manager.
  *
  * @param   JDatabaseQuery  $query  The query object of com_categories
  *
  * @return  JDatabaseQuery
  *
  * @since   3.4
  */
 public static function countItems($query)
 {
     // Join articles to categories and count published items
     $query->select('COUNT(DISTINCT cp.id) AS count_published');
     $query->join('LEFT', '#__newsfeeds AS cp ON cp.catid = a.id AND cp.published = 1');
     // Count unpublished items
     $query->select('COUNT(DISTINCT cu.id) AS count_unpublished');
     $query->join('LEFT', '#__newsfeeds AS cu ON cu.catid = a.id AND cu.published = 0');
     // Count archived items
     $query->select('COUNT(DISTINCT ca.id) AS count_archived');
     $query->join('LEFT', '#__newsfeeds AS ca ON ca.catid = a.id AND ca.published = 2');
     // Count trashed items
     $query->select('COUNT(DISTINCT ct.id) AS count_trashed');
     $query->join('LEFT', '#__newsfeeds AS ct ON ct.catid = a.id AND ct.published = -2');
     return $query;
 }

 /**
  * Tests the JDatabaseQuery::join method.
  *
  * @return  void
  *
  * @covers  JDatabaseQuery::join
  * @since   11.3
  */
 public function testJoin()
 {
     $this->assertThat($this->_instance->join('INNER', 'foo ON foo.id = bar.id'), $this->identicalTo($this->_instance), 'Tests chaining.');
     $this->assertThat(trim($this->_instance->join[0]), $this->equalTo('INNER JOIN foo ON foo.id = bar.id'), 'Tests that first join renders correctly.');
     $this->_instance->join('OUTER', 'goo ON goo.id = car.id');
     $this->assertThat(trim($this->_instance->join[1]), $this->equalTo('OUTER JOIN goo ON goo.id = car.id'), 'Tests that second join renders correctly.');
 }

 /**
  * @param array $filters
  * @param array $sort_filters
  */
 public function process(array $filters, array $sort_filters = array(), $showUnpublished = false)
 {
     $this->showUnpublished = $showUnpublished;
     $this->setAccessWhere();
     $this->setDisplayedWhere();
     foreach ($filters as $filtertype => $fitlerdata) {
         if (!method_exists($this, $filtertype)) {
             //throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype));
         } else {
             $this->{$filtertype}($fitlerdata);
         }
     }
     foreach ($sort_filters as $sort_filtertype => $sort_fitlerdata) {
         $sort_function = 'sort_' . $sort_filtertype;
         if (!method_exists($this, 'sort_' . $sort_filtertype)) {
             //throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype));
         } else {
             $this->{$sort_function}($sort_fitlerdata);
         }
     }
     if (!empty($this->access_where)) {
         $access_where = sprintf('(%s)', implode(' AND ', $this->access_where));
         $article_where_parts[] = $access_where;
         $filter_where_parts[] = $access_where;
     }
     if (!empty($this->displayed_where)) {
         $displayed_where = sprintf('(%s)', implode(' AND ', $this->displayed_where));
         $article_where_parts[] = $displayed_where;
         $filter_where_parts[] = $displayed_where;
     }
     if (!empty($this->article_where)) {
         $article_where_parts[] = implode(' AND ', $this->article_where);
         $this->query->where(sprintf('(%s)', implode(' AND ', $article_where_parts)), 'OR');
     }
     if (!empty($this->filter_where)) {
         $filter_where_parts[] = implode(' AND ', $this->filter_where);
         $this->query->where(sprintf('(%s)', implode(' AND ', $filter_where_parts)), 'OR');
     }
     if (empty($this->article_where) && empty($this->filter_where)) {
         $this->query->where('0=1');
     }
     if ($this->manualSort) {
         $this->query->join('LEFT OUTER', sprintf('(select rsi.provider_id, rsi.order from #__roksprocket_items as rsi where module_id = %d) rsi on a.id = rsi.provider_id', $this->moduleId));
         array_unshift($this->sort_order, 'rsi.order');
         if ($this->manualAppend == 'after') {
             array_unshift($this->sort_order, 'IF(ISNULL(rsi.order),1,0)');
         }
     }
     foreach ($this->sort_order as $sort) {
         $this->query->order($sort);
     }
 }

 /**
  * add filter to query
  *
  * @param   JDatabaseQuery  $query  query object
  *
  * @return JDatabaseQuery
  */
 public function addFilter(JDatabaseQuery $query)
 {
     if (!$this->filterField) {
         return $query;
     }
     $db = JFactory::getDbo();
     //since joomla 3.0 filter_value can be '' too not only filterNullValue
     if (isset($this->filter_value) && strlen($this->filter_value) > 0 && $this->filter_value != $this->filterNullValue) {
         $query->join('INNER', '#__rwf_fields AS rwff ON rwff.id = c.field_id');
         $query->where("rwff.form_id = " . $db->escape($this->filter_value, true));
     }
     return $query;
 }

	static function &getModules()
	{
		static $modules;
		
		if (!is_null($modules))
			return $modules;

		$mainframe = JFactory::getApplication();

		$user =& JFactory::getUser();
		$db	=& JFactory::getDBO();
		$aid = $user->get('aid', 0);

		$groups	= implode(',', $user->getAuthorisedViewLevels());
		$query = new JDatabaseQuery;
		$query->select('id, title, module, position, content, showtitle, params, mm.menuid');
		$query->from('#__modules AS m');
		$query->join('LEFT','#__modules_menu AS mm ON mm.moduleid = m.id');
		$query->where('m.published = 1');

		if (!$user->authorise('core.admin',1)) {
			$query->where('m.access IN (' . $groups . ')');
		}

		$query->where('m.client_id = ' . (int)$mainframe->getClientId());
		$query->order('position, ordering'); 

		$db->setQuery($query);
		$modules = $db->loadObjectList('id');

		if ($db->getErrorNum()) 
		{
			$modules = array();
		}

		foreach ($modules as $key => $mod)
		{
			$module =& $modules[$key];
			$file = $module->module;
			$custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
			$module->user = $custom;
			$module->name = $custom ? $module->title : substr( $file, 4 );
			$module->style	= null;
			$module->position = strtolower($module->position);
		}
			
		return $modules;
	}

 /**
  * Load published modules
  *
  * @return	array
  */
 protected static function &_load()
 {
     static $clean;
     if (isset($clean)) {
         return $clean;
     }
     $Itemid = JRequest::getInt('Itemid');
     $app = JFactory::getApplication();
     $user = JFactory::getUser();
     $groups = implode(',', $user->getAuthorisedViewLevels());
     $lang = JFactory::getLanguage()->getTag();
     $clientId = (int) $app->getClientId();
     $cache = JFactory::getCache('com_modules', '');
     $cacheid = md5(serialize(array($Itemid, $groups, $clientId, $lang)));
     if (!($clean = $cache->get($cacheid))) {
         $db = JFactory::getDbo();
         $query = new JDatabaseQuery();
         $query->select('id, title, module, position, content, showtitle, params, mm.menuid');
         $query->from('#__modules AS m');
         $query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id');
         $query->where('m.published = 1');
         $date = JFactory::getDate();
         $now = $date->toMySQL();
         $nullDate = $db->getNullDate();
         $query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')');
         $query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')');
         $query->where('m.access IN (' . $groups . ')');
         $query->where('m.client_id = ' . $clientId);
         $query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)');
         // Filter by language
         if ($app->isSite() && $app->getLanguageFilter()) {
             $query->where('m.language IN (' . $db->Quote($lang) . ',' . $db->Quote('*') . ')');
         }
         $query->order('position, ordering');
         // Set the query
         $db->setQuery($query);
         $modules = $db->loadObjectList();
         $clean = array();
         if ($db->getErrorNum()) {
             JError::raiseWarning(500, JText::sprintf('JLIB_APPLICATION_ERROR_MODULE_LOAD', $db->getErrorMsg()));
             return $clean;
         }
         // Apply negative selections and eliminate duplicates
         $negId = $Itemid ? -(int) $Itemid : false;
         $dupes = array();
         for ($i = 0, $n = count($modules); $i < $n; $i++) {
             $module =& $modules[$i];
             // The module is excluded if there is an explicit prohibition, or if
             // the Itemid is missing or zero and the module is in exclude mode.
             $negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0;
             if (isset($dupes[$module->id])) {
                 // If this item has been excluded, keep the duplicate flag set,
                 // but remove any item from the cleaned array.
                 if ($negHit) {
                     unset($clean[$module->id]);
                 }
                 continue;
             }
             $dupes[$module->id] = true;
             // Only accept modules without explicit exclusions.
             if (!$negHit) {
                 //determine if this is a custom module
                 $file = $module->module;
                 $custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
                 $module->user = $custom;
                 // Custom module name is given by the title field, otherwise strip off "com_"
                 $module->name = $custom ? $module->title : substr($file, 4);
                 $module->style = null;
                 $module->position = strtolower($module->position);
                 $clean[$module->id] = $module;
             }
         }
         unset($dupes);
         // Return to simple indexing that matches the query order.
         $clean = array_values($clean);
         $cache->store($clean, $cacheid);
     }
     return $clean;
 }

 protected function modifyQuery(JDatabaseQuery &$query)
 {
     $query->where("NOT deleted");
     if (isset($this->leader)) {
         if ($this->leader instanceof Leader) {
             $query->where("leaderid = " . $this->leader->id);
         } else {
             if (is_int($this->leader)) {
                 $query->where("leaderid = " . $this->leader);
             } else {
                 if (ctype_digit($this->leader)) {
                     $query->where("leaderid = " . (int) $this->leader);
                 }
             }
         }
     }
     if (isset($this->walkProgramme)) {
         if ($this->walkProgramme instanceof WalkProgramme) {
             $wpID = $this->walkProgramme->id;
         } else {
             $wpID = (int) $this->walkProgramme;
         }
         $query->join('INNER', 'walkprogrammewalklinks ON WalkProgrammeWalkID = walkprogrammewalks.SequenceID');
         $query->where("walkprogrammewalklinks.ProgrammeID = " . $wpID);
     }
     if (isset($this->startTimeMax)) {
         $query->where("meettime <= '" . strftime("%H:%M", $this->startTimeMax) . "'");
     }
 }

 /**
  * Get the part of the sql query that creates the joins
  * used when building the table's data
  *
  * @param   bool|JDatabaseQuery  $query  JQuery object or false
  *
  * @return  JDatabaseQuery|string  string or join query - join sql
  */
 public function buildQueryJoin($query = false)
 {
     $db = FabrikWorker::getDbo();
     $ref = $query ? '1' : '0';
     if (isset($this->joinsSQL[$ref])) {
         return $this->joinsSQL[$ref];
     }
     $statements = array();
     $table = $this->getTable();
     $selectedTables[] = $table->db_table_name;
     $return = array();
     $joins = $this->get('includeCddInJoin', true) === false ? $this->getJoinsNoCdd() : $this->getJoins();
     foreach ($joins as $join) {
         // Used to bypass user joins if the table connect isnt the Joomla connection
         if ((int) $join->canUse === 0) {
             continue;
         }
         if ($join->join_type == '') {
             $join->join_type = 'LEFT';
         }
         $sql = JString::strtoupper($join->join_type) . ' JOIN ' . $db->qn($join->table_join);
         $k = FabrikString::safeColName($join->keytable . '.' . $join->table_key);
         // Check we only get the field name
         $join->table_join_key = explode('.', $join->table_join_key);
         $join->table_join_key = array_pop($join->table_join_key);
         if ($join->table_join_alias == '') {
             $on = FabrikString::safeColName($join->table_join . '.' . $join->table_join_key);
             $sql .= ' ON ' . $on . ' = ' . $k;
         } else {
             $on = FabrikString::safeColName($join->table_join_alias . '.' . $join->table_join_key);
             $sql .= ' AS ' . FabrikString::safeColName($join->table_join_alias) . ' ON ' . $on . ' = ' . $k . "\n";
         }
         /*
          * @FIXME - need to work out where the COLLATE needs to go. Was at the end of builQuery, but that creates
          * an invalid query if we had any grouping.  As it only applies to joined tables, tried putting it here,
          * but still get a query error.  Needs to be fixed, but I have to commit to get some other changes done.
          */
         /*
         $params = $this->getParams();
         
         if ($params->get('force_collate', '') !== '')
         {
         	$sql .= ' COLLATE ' . $params->get('force_collate', '') . ' ';
         }
         */
         /* Try to order join statements to ensure that you are selecting from tables that have
          * already been included (either via a previous join statement or the table select statement)
          */
         if (in_array($join->keytable, $selectedTables)) {
             $return[] = $sql;
             $selectedTables[] = $join->table_join;
         } else {
             // Didn't find anything so defer it till later
             /* $statements[$join->keytable] = $sql;
              * $$$rob - sometimes the keytable is the same for 2 deferred joins
              * in this case the first join is incorrectly overwritten in the $statements array
              * keying on join->id should solve this
              */
             $statements[$join->id] = array($join->keytable, $sql);
         }
         // Go through the deferred join statements and see if their table has now been selected
         foreach ($statements as $joinId => $ar) {
             $t = $ar[0];
             $s = $ar[1];
             if (in_array($t, $selectedTables)) {
                 if (!in_array($s, $return)) {
                     // $$$rob test to avoid duplicate join queries
                     $return[] = $s;
                     unset($statements[$t]);
                 }
             }
         }
     }
     // $$$rob test for bug #376
     foreach ($statements as $joinId => $ar) {
         $s = $ar[1];
         if (!in_array($s, $return)) {
             $return[] = $s;
         }
     }
     if ($query !== false) {
         foreach ($return as $r) {
             $words = explode(' ', trim($r));
             $type = array_shift($words);
             $statement = str_replace('JOIN', '', implode(' ', $words));
             $query->join($type, $statement);
         }
         return $query;
     } else {
         $return = implode(' ', $return);
         $this->joinsSQL[$ref] = $return;
     }
//.........这里部分代码省略.........

 function getModule($id = 0, $name = '')
 {
     $Itemid = $this->Itemid;
     $app = JFactory::getApplication();
     $user = JFactory::getUser();
     $groups = implode(',', $user->authorisedLevels());
     $db = JFactory::getDbo();
     $query = new JDatabaseQuery();
     $query->select('id, title, module, position, content, showtitle, params, mm.menuid');
     $query->from('#__modules AS m');
     $query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id');
     $query->where('m.published = 1');
     $query->where('m.id = ' . $id);
     $date = JFactory::getDate();
     $now = $date->toSql();
     $nullDate = $db->getNullDate();
     $query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')');
     $query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')');
     $clientid = (int) $app->getClientId();
     if (!$user->authorise('core.admin', 1)) {
         $query->where('m.access IN (' . $groups . ')');
     }
     $query->where('m.client_id = ' . $clientid);
     if (isset($Itemid)) {
         $query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)');
     }
     $query->order('position, ordering');
     // Filter by language
     if ($app->isSite() && $app->getLanguageFilter()) {
         $query->where('m.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . ')');
     }
     // Set the query
     $db->setQuery($query);
     $cache = JFactory::getCache('com_modules', 'callback');
     $cacheid = md5(serialize(array($Itemid, $groups, $clientid, JFactory::getLanguage()->getTag(), $id)));
     $module = $cache->get(array($db, 'loadObject'), null, $cacheid, false);
     if (!$module) {
         return null;
     }
     $negId = $Itemid ? -(int) $Itemid : false;
     // The module is excluded if there is an explicit prohibition, or if
     // the Itemid is missing or zero and the module is in exclude mode.
     $negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0;
     // Only accept modules without explicit exclusions.
     if (!$negHit) {
         //determine if this is a custom module
         $file = $module->module;
         $custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
         $module->user = $custom;
         // Custom module name is given by the title field, otherwise strip off "com_"
         $module->name = $custom ? $module->title : substr($file, 4);
         $module->style = null;
         $module->position = strtolower($module->position);
         $clean[$module->id] = $module;
     }
     return $module;
 }

 /**
  * registerQueryTables
  *
  * @param DatabaseQuery $query
  *
  * @return  DatabaseQuery
  */
 public function registerQueryTables(DatabaseQuery $query)
 {
     foreach ($this->tables as $alias => $table) {
         if ($table['join'] == 'FROM') {
             $query->from($query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias));
         } else {
             $query->join($table['join'], $query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias) . ' ' . $table['condition']);
         }
     }
     return $query;
 }

 /**
  * Apply currently set filters to the database query
  * @param JDatabaseQuery $query
  */
 protected function applyFilters(JDatabaseQuery &$query)
 {
     if ($this->useEventsTable) {
         $query->from("events");
         $query->leftJoin($this->table . " USING (" . $this->idField . ")");
     } else {
         $query->from($this->table);
     }
     $query->where(array($this->startDateField . " >= '" . Event::timeToDate($this->startDate) . "'", $this->startDateField . " <= '" . Event::timeToDate($this->endDate) . "'"));
     if (!$this->showUnpublished) {
         $query->where($this->readyToPublishField);
     }
     // Filter by attendees
     if (!empty($this->filterAttendedBy)) {
         $query->join("INNER", "eventattendance ON eventattendance.eventtype=" . $this->eventTypeConst . " AND eventattendance.eventid=" . $this->table . "." . $this->idField);
         $query->where("eventattendance.user IN (" . implode(",", $this->filterAttendedBy) . ")");
     }
     // This allows subclasses to modify the query. Normally they'll add extra WHERE clauses.
     $this->modifyQuery($query);
 }