PHP JDatabaseQuery::select方法代码示例

 /**
  * 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;
 }

 /**
  * Get a list of logged users.
  *
  * @param	JObject	The module parameters.
  * @return	mixed	An array of articles, or false on error.
  */
 public static function getList($params)
 {
     // Initialise variables
     $db = JFactory::getDbo();
     $user = JFactory::getUser();
     $query = new JDatabaseQuery();
     $query->select('s.time, s.client_id, u.id, u.name, u.username');
     $query->from('#__session AS s');
     $query->leftJoin('#__users AS u ON s.userid = u.id');
     $query->where('s.guest = 0');
     $db->setQuery($query, 0, $params->get('count', 5));
     $results = $db->loadObjectList();
     // Check for database errors
     if ($error = $db->getErrorMsg()) {
         JError::raiseError(500, $error);
         return false;
     }
     foreach ($results as $k => $result) {
         $results[$k]->logoutLink = '';
         if ($user->authorise('core.manage', 'com_users')) {
             $results[$k]->editLink = JRoute::_('index.php?option=com_users&task=user.edit&id=' . $result->id);
             $results[$k]->logoutLink = JRoute::_('index.php?option=com_login&task=logout&uid=' . $result->id . '&' . JUtility::getToken() . '=1');
         }
         if ($params->get('name', 1) == 0) {
             $results[$k]->name = $results[$k]->username;
         }
     }
     return $results;
 }

 /**
  * Tests the JDatabaseQuery::select method.
  *
  * @return  void
  *
  * @covers  JDatabaseQuery::select
  * @since   11.3
  */
 public function testSelect()
 {
     $this->assertThat($this->_instance->select('foo'), $this->identicalTo($this->_instance), 'Tests chaining.');
     $this->assertThat($this->_instance->type, $this->equalTo('select'), 'Tests the type property is set correctly.');
     $this->assertThat(trim($this->_instance->select), $this->equalTo('SELECT foo'), 'Tests the select element is set correctly.');
     $this->_instance->select('bar');
     $this->assertThat(trim($this->_instance->select), $this->equalTo('SELECT foo,bar'), 'Tests the second use appends correctly.');
     $this->_instance->select(array('goo', 'car'));
     $this->assertThat(trim($this->_instance->select), $this->equalTo('SELECT foo,bar,goo,car'), 'Tests the second use appends correctly.');
 }

 /**
  * Tests the JDatabaseQuery::union method when passed two query objects in an array.
  *
  * @return  void
  *
  * @since   12.??
  */
 public function testUnionObjectsArray()
 {
     $this->_instance->select('name')->from('foo')->where('a=1');
     $q2 = new JDatabaseQueryInspector($this->dbo);
     $q2->select('name')->from('bar')->where('b=2');
     $q3 = new JDatabaseQueryInspector($this->dbo);
     $q3->select('name')->from('baz')->where('c=3');
     TestReflection::setValue($this->_instance, 'union', null);
     $this->_instance->union(array($q2, $q3));
     $this->assertThat((string) $this->_instance, $this->equalTo(PHP_EOL . "SELECT name" . PHP_EOL . "FROM foo" . PHP_EOL . "WHERE a=1" . PHP_EOL . "UNION (" . PHP_EOL . "SELECT name" . PHP_EOL . "FROM bar" . PHP_EOL . "WHERE b=2)" . PHP_EOL . "UNION (" . PHP_EOL . "SELECT name" . PHP_EOL . "FROM baz" . PHP_EOL . "WHERE c=3)"));
 }

	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;
	}

 /**
  * Method to get a list of options for a list input.
  *
  * @return      array           An array of JHtml options.
  */
 protected function getOptions()
 {
     $db = JFactory::getDBO();
     $query = new JDatabaseQuery();
     $query->select('#__helloworld.id as id,greeting,#__categories.title as category,catid');
     $query->from('#__helloworld');
     $query->leftJoin('#__categories on catid=#__categories.id');
     $db->setQuery((string) $query);
     $messages = $db->loadObjectList();
     $options = array();
     if ($messages) {
         foreach ($messages as $message) {
             $options[] = JHtml::_('select.option', $message->id, $message->greeting . ($message->catid ? ' (' . $message->category . ')' : ''));
         }
     }
     $options = array_merge(parent::getOptions(), $options);
     return $options;
 }

 /**
  * Method to get a list of options for a list input.
  *
  * @return      array           An array of JHtml options.
  */
 protected function getOptions()
 {
     $db = JFactory::getDBO();
     $query = new JDatabaseQuery();
     $query->select('#__jpaudiotracks.id as id,
                                                    pathatweb,
                                                    pathatlocal,
                                                    file,
                                                    title,
                                                    alias,
                                                    tracknumber,
                                                    mediatype,
                                                    bit_rate,
                                                    sample_rate,
                                                    channels,
                                                    channelmode,
                                                    filesize,
                                                    length,
                                                    catid,
                                                    add_datetime,
                                                    artist,
                                                    album,
                                                    year,
                                                    description,
                                                    lyrics,
                                                    frontcover,
                                                    backcover,
                                                    encoder,
                                                    metakey,
                                                    metadesc,
                             #__categories.title as category,catid');
     $query->from('#__jpaudiotracks');
     $query->leftJoin('#__categories on catid=#__categories.id');
     $db->setQuery((string) $query);
     $tracks = $db->loadObjectList();
     $options = array();
     if ($tracks) {
         foreach ($tracks as $track) {
             $options[] = JHtml::_('select.option', $track->id, $track->name . ($track->catid ? ' (' . $track->category . ')' : ''));
         }
     }
     $options = array_merge(parent::getOptions(), $options);
     return $options;
 }

 /**
  * Method to get a list of options for a list input.
  *
  * @return      array           An array of JHtml options.
  */
 protected function getOptions()
 {
     $db = JFactory::getDBO();
     $query = new JDatabaseQuery();
     $query->select('#__quipforum_boards.id as id,topic');
     $query->from('#__quipforum_boards');
     $db->setQuery((string) $query);
     $messages = $db->loadObjectList();
     $options = array();
     if ($messages) {
         //if($this->allow_all)
         //	$options[] = JHtml::_('select.option', 0, 'Any');
         foreach ($messages as $message) {
             $options[] = JHtml::_('select.option', $message->id, $message->topic);
         }
     }
     $options = array_merge(parent::getOptions(), $options);
     return $options;
 }

 /**
  * 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;
 }

 private function _getExtensionID($type, $id, $client, $group)
 {
     $db = $this->parent->getDbo();
     $result = $id;
     $query = new JDatabaseQuery();
     $query->select('extension_id');
     $query->from('#__extensions');
     $query->where('type = ' . $db->Quote($type));
     $query->where('element = ' . $db->Quote($id));
     switch ($type) {
         case 'plugin':
             // plugins have a folder but not a client
             $query->where('folder = ' . $db->Quote($group));
             break;
         case 'library':
         case 'package':
         case 'component':
             // components, packages and libraries don't have a folder or client
             // included for completeness
             break;
         case 'language':
         case 'module':
         case 'template':
             // languages, modules and templates have a client but not a folder
             $client = JApplicationHelper::getClientInfo($client, true);
             $query->where('client_id = ' . (int) $client->id);
             break;
     }
     $db->setQuery($query);
     $result = $db->loadResult();
     // note: for templates, libraries and packages their unique name is their key
     // this means they come out the same way they came in
     return $result;
 }

 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;
 }

 /**
  * Gets the list of groups and adds expensive joins to the result set.
  *
  * @return	mixed	An array of data items on success, false on failure.
  * @since	1.6
  */
 public function getItems()
 {
     // Get a storage key.
     $store = $this->getStoreId();
     // Try to load the data from internal storage.
     if (empty($this->cache[$store])) {
         $items = parent::getItems();
         // Bail out on an error or empty list.
         if (empty($items)) {
             $this->cache[$store] = $items;
             return $items;
         }
         // First pass: get list of the group id's and reset the counts.
         $groupIds = array();
         foreach ($items as $item) {
             $groupIds[] = (int) $item->id;
             $item->user_count = 0;
         }
         // Get the counts from the database only for the users in the list.
         $db = $this->getDbo();
         $query = new JDatabaseQuery();
         // Count the objects in the user group.
         $query->select('map.group_id, COUNT(DISTINCT map.user_id) AS user_count')->from('`#__user_usergroup_map` AS map')->where('map.group_id IN (' . implode(',', $groupIds) . ')')->group('map.group_id');
         $db->setQuery($query);
         // Load the counts into an array indexed on the user id field.
         $users = $db->loadObjectList('group_id');
         $error = $db->getErrorMsg();
         if ($error) {
             $this->setError($error);
             return false;
         }
         // Second pass: collect the group counts into the master items array.
         foreach ($items as &$item) {
             if (isset($users[$item->id])) {
                 $item->user_count = $users[$item->id]->user_count;
             }
         }
         // Add the items to the internal cache.
         $this->cache[$store] = $items;
     }
     return $this->cache[$store];
 }

 /**
  * Gets the list of users and adds expensive joins to the result set.
  *
  * @return	mixed	An array of data items on success, false on failure.
  * @since	1.6
  */
 public function getItems()
 {
     // Get a storage key.
     $store = $this->getStoreId();
     // Try to load the data from internal storage.
     if (empty($this->cache[$store])) {
         $groups = $this->getState('filter.groups');
         $groupId = $this->getState('filter.group_id');
         if (isset($groups) && (empty($groups) || $groupId && !in_array($groupId, $groups))) {
             $items = array();
         } else {
             $items = parent::getItems();
         }
         // Bail out on an error or empty list.
         if (empty($items)) {
             $this->cache[$store] = $items;
             return $items;
         }
         // Joining the groups with the main query is a performance hog.
         // Find the information only on the result set.
         // First pass: get list of the user id's and reset the counts.
         $userIds = array();
         foreach ($items as $item) {
             $userIds[] = (int) $item->id;
             $item->group_count = 0;
             $item->group_names = '';
         }
         // Get the counts from the database only for the users in the list.
         $db = $this->getDbo();
         $query = new JDatabaseQuery();
         // Join over the group mapping table.
         $query->select('map.user_id, COUNT(map.group_id) AS group_count')->from('#__user_usergroup_map AS map')->where('map.user_id IN (' . implode(',', $userIds) . ')')->group('map.user_id')->select('GROUP_CONCAT(g2.title SEPARATOR ' . $db->Quote("\n") . ') AS group_names')->join('LEFT', '#__usergroups AS g2 ON g2.id = map.group_id');
         $db->setQuery($query);
         // Load the counts into an array indexed on the user id field.
         $userGroups = $db->loadObjectList('user_id');
         $error = $db->getErrorMsg();
         if ($error) {
             $this->setError($error);
             return false;
         }
         // Second pass: collect the group counts into the master items array.
         foreach ($items as &$item) {
             if (isset($userGroups[$item->id])) {
                 $item->group_count = $userGroups[$item->id]->group_count;
                 $item->group_names = $userGroups[$item->id]->group_names;
             }
         }
         // Add the items to the internal cache.
         $this->cache[$store] = $items;
     }
     return $this->cache[$store];
 }

 private function add_product_rows_query_select(JDatabaseQuery $query)
 {
     $model_options = WDFHelper::get_model('options');
     $options = $model_options->get_options();
     $query->select('T_PRODUCTS.date_added');
     $query->select('T_PRODUCTS.id');
     $query->select('T_PRODUCTS.name');
     $query->select('IFNULL(T_PRODUCTS.category_id, 0) AS category_id');
     $query->select('T_CATEGORIES.name AS category_name');
     $query->select('FORMAT(T_RATINGS.rating, 1) AS rating');
     $query->select('CASE
             WHEN T_USER_RATINGS.ratings_count > 0 THEN 0
             WHEN T_USER_RATINGS.ratings_count = 0 THEN 1
             WHEN T_USER_RATINGS.ratings_count IS NULL THEN 1
         END AS can_rate');
     $query->select('IFNULL(T_PRODUCTS.manufacturer_id, 0) AS manufacturer_id');
     $query->select('T_MANUFACTURERS.name AS manufacturer_name');
     $query->select('T_MANUFACTURERS.logo AS manufacturer_logo');
     $query->select('T_MANUFACTURERS.site AS manufacturer_site');
     $query->select('T_PRODUCTS.description');
     $query->select('T_PRODUCTS.images');
     $select_price = 'T_PRODUCTS.price';
     if ($options->option_include_discount_in_price) {
         $select_price .= ' * (1 - IFNULL(T_DISCOUNTS.rate, 0) / 100)';
     }
     if ($options->option_include_tax_in_price) {
         $select_price .= ' * (1 + IFNULL(T_TAXES.rate, 0) / 100)';
     }
     $select_price .= ' AS price';
     $query->select($select_price);
     $query->select('T_PRODUCTS.market_price');
     $query->select('T_PRODUCTS.price AS price_without_t_d');
     $query->select('T_PRODUCTS.amount_in_stock');
     $query->select('T_PRODUCTS.unlimited');
     $query->select('CASE
             WHEN T_ORDERPRODUCTS.products_in_cart > 0 THEN 1
             WHEN T_ORDERPRODUCTS.products_in_cart = 0 THEN 0
         END AS added_to_cart');
     $query->select('T_LABELS.name AS label_name');
     $query->select('T_LABELS.thumb AS label_thumb');
     $query->select('T_LABELS.thumb_position AS label_thumb_position');
     $query->select('T_TAXES.name AS tax_name');
     $query->select('T_TAXES.rate AS tax_rate');
     $query->select('T_DISCOUNTS.name AS discount_name');
     $query->select('T_DISCOUNTS.rate AS discount_rate');
     $query->select('T_PRODUCTS.meta_title');
     $query->select('T_PRODUCTS.meta_description');
     $query->select('T_PRODUCTS.meta_keyword');
     $query->select('T_PRODUCTS.model');
     $query->select('T_PRODUCTS.sku');
     $query->select('T_PRODUCTS.upc');
     $query->select('T_PRODUCTS.ean');
     $query->select('T_PRODUCTS.jan');
     $query->select('T_PRODUCTS.isbn');
     $query->select('T_PRODUCTS.mpn');
     $query->select('T_PRODUCTS.weight');
     $query->select('T_PRODUCTS.dimensions');
     $query->select('T_PRODUCTS.videos');
     $query->select('T_FEEDBACK.reviews_count');
     return $query;
 }

 protected function _load($id)
 {
     $db = JFactory::getDbo();
     $app = JFactory::getApplication();
     $user = JFactory::getUser();
     $extension = $this->_extension;
     // Record that this $id has been checked
     $this->_checkedCategories[$id] = true;
     $query = new JDatabaseQuery();
     // right join with c for category
     $query->select('c.*');
     $query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug');
     $query->from('#__categories as c');
     $query->where('(c.extension=' . $db->Quote($extension) . ' OR c.extension=' . $db->Quote('system') . ')');
     if ($this->_options['access']) {
         $query->where('c.access IN (' . implode(',', $user->getAuthorisedViewLevels()) . ')');
     }
     if ($this->_options['published'] == 1) {
         $query->where('c.published = 1');
     }
     $query->order('c.lft');
     // s for selected id
     if ($id != 'root') {
         // Get the selected category
         $query->leftJoin('#__categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt)');
         $query->where('s.id=' . (int) $id);
     }
     $subQuery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ' . 'ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ' . $db->quote($extension) . ' AND parent.published != 1 GROUP BY cat.id) ';
     $query->leftJoin($subQuery . 'AS badcats ON badcats.id = c.id');
     $query->where('badcats.id is null');
     // i for item
     if (isset($this->_options['countItems']) && $this->_options['countItems'] == 1) {
         if ($this->_options['published'] == 1) {
             $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id AND i.' . $this->_statefield . ' = 1');
         } else {
             $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id');
         }
         $query->select('COUNT(i.' . $db->nameQuote($this->_key) . ') AS numitems');
     }
     // Group by
     $query->group('c.id');
     // Filter by language
     if ($app->isSite() && $app->getLanguageFilter()) {
         $query->where('(' . ($id != 'root' ? 'c.id=s.id OR ' : '') . 'c.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . '))');
     }
     // Get the results
     $db->setQuery($query);
     $results = $db->loadObjectList('id');
     $childrenLoaded = false;
     if (count($results)) {
         // foreach categories
         foreach ($results as $result) {
             // Deal with root category
             if ($result->id == 1) {
                 $result->id = 'root';
             }
             // Deal with parent_id
             if ($result->parent_id == 1) {
                 $result->parent_id = 'root';
             }
             // Create the node
             if (!isset($this->_nodes[$result->id])) {
                 // Create the JCategoryNode and add to _nodes
                 $this->_nodes[$result->id] = new JCategoryNode($result, $this);
                 // If this is not root, and if the current nodes parent is in the list or the current node parent is 0
                 if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
                     // Compute relationship between node and its parent - set the parent in the _nodes field
                     $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
                 }
                 // if the node's parent id is not in the _nodes list and the node is not root (doesn't have parent_id == 0),
                 // then remove this nodes from the list
                 if (!(isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
                     unset($this->_nodes[$result->id]);
                     continue;
                 }
                 if ($result->id == $id || $childrenLoaded) {
                     $this->_nodes[$result->id]->setAllLoaded();
                     $childrenLoaded = true;
                 }
             } else {
                 if ($result->id == $id || $childrenLoaded) {
                     // Create the JCategoryNode
                     $this->_nodes[$result->id] = new JCategoryNode($result, $this);
                     if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id)) {
                         // Compute relationship between node and its parent
                         $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
                     }
                     if (!isset($this->_nodes[$result->parent_id])) {
                         unset($this->_nodes[$result->id]);
                         continue;
                     }
                     if ($result->id == $id || $childrenLoaded) {
                         $this->_nodes[$result->id]->setAllLoaded();
                         $childrenLoaded = true;
                     }
                 }
             }
         }
     } else {
         $this->_nodes[$id] = null;
//.........这里部分代码省略.........