PHP JDatabaseQuery::order方法代码示例

 /**
  * Prepare result ordering.
  *
  * @param \JDatabaseQuery $query
  * @param array $options
  */
 protected function prepareOrder(&$query, $options)
 {
     // Filter by state.
     if (array_key_exists('order', $options)) {
         // Prepare direction of ordering.
         $direction = !array_key_exists('order_dir', $options) ? 'DESC' : $options['order_dir'];
         if (!in_array($direction, $this->allowedDirections, true)) {
             $direction = 'DESC';
         }
         switch ($options['order']) {
             case Constants::ORDER_BY_LOCATION_NAME:
                 // Order by location name.
                 $query->order('l.name ' . $direction);
                 break;
             case Constants::ORDER_BY_NUMBER_OF_PROJECTS:
                 // Order by location name.
                 $query->order('project_number ' . $direction);
                 break;
             default:
                 // Order by project title.
                 $query->order('a.title ' . $direction);
                 break;
         }
     }
 }

 /**
  * Set order by field and direction.
  *
  * This function can be used more than once to chain order by.
  *
  * @param  string $by
  * @param  int $direction
  * @param  string $alias
  *
  * @return $this
  */
 public function order($by, $direction = 1, $alias = 'a')
 {
     $direction = $direction > 0 ? 'ASC' : 'DESC';
     $by = $alias . '.' . $this->db->quoteName($by);
     $this->query->order("{$by} {$direction}");
     return $this;
 }

 /**
  * Tests the JDatabaseQuery::union method.
  *
  * @return  void
  *
  * @covers  JDatabaseQuery::union
  * @since   12.1
  */
 public function testUnionClear()
 {
     $this->_instance->union = null;
     $this->_instance->order = null;
     $this->_instance->order('bar');
     $this->_instance->union('SELECT name FROM foo');
     $this->assertThat($this->_instance->order, $this->equalTo(null), 'Tests that ORDER BY is cleared with union.');
 }

 /**
  * Tests the JDatabaseQuery::union method.
  *
  * @return  void
  *
  * @since   12.1
  */
 public function testUnionClear()
 {
     TestReflection::setValue($this->_instance, 'union', null);
     TestReflection::setValue($this->_instance, 'order', null);
     $this->_instance->order('bar');
     $this->_instance->union('SELECT name FROM foo');
     $this->assertThat(TestReflection::getValue($this->_instance, 'order'), $this->equalTo(null), 'Tests that ORDER BY is cleared with union.');
 }

 /**
  * Tests the JDatabaseQuery::order method.
  *
  * @return  void
  *
  * @since   11.3
  */
 public function testOrder()
 {
     $this->assertThat($this->_instance->order('foo'), $this->identicalTo($this->_instance), 'Tests chaining.');
     $this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo'), 'Tests rendered value.');
     // Add another column.
     $this->_instance->order('bar');
     $this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo,bar'), 'Tests rendered value after second use.');
     $this->_instance->order(array('goo', 'car'));
     $this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo,bar,goo,car'), 'Tests array input.');
 }

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

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

 /**
  * @param array $filters
  * @param array $sort_filters
  */
 public function process(array $filters, array $sort_filters = array(), $showUnpublished = false)
 {
     $this->showUnpublished = $showUnpublished;
     $this->setAccessWhere();
     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->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');
     }
     foreach ($this->sort_order as $sort) {
         $this->query->order($sort);
     }
 }

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

 /**
  * Prepare result ordering.
  *
  * @param \JDatabaseQuery $query
  * @param array $options
  */
 protected function prepareOrder(&$query, $options)
 {
     // Filter by state.
     if (isset($options["order"])) {
         // Prepare direction of ordering.
         $direction = !isset($options["order_dir"]) ? "DESC" : $options["order_dir"];
         if (!in_array($direction, $this->allowedDirections)) {
             $direction = "DESC";
         }
         switch ($options["order"]) {
             case Constants::ORDER_BY_LOCATION_NAME:
                 // Order by location name.
                 $query->order("l.name " . $direction);
                 break;
             case Constants::ORDER_BY_NUMBER_OF_PROJECTS:
                 // Order by location name.
                 $query->order("project_number " . $direction);
                 break;
             default:
                 // Order by project title.
                 $query->order("a.title " . $direction);
                 break;
         }
     }
 }

 /**
  * Get options order by
  *
  * @param   string               $view   View mode '' or 'filter'
  * @param   JDatabaseQuery|bool  $query  Set to false to return a string
  *
  * @return  string  order by statement
  */
 protected function getOrderBy($view = '', $query = false)
 {
     $params = $this->getParams();
     $orderBy = $params->get('notes_order_element');
     if ($orderBy == '') {
         return $query ? $query : '';
     } else {
         $order = FabrikString::safeQuoteName($params->get('join_db_name') . '.' . $orderBy) . ' ' . $params->get('notes_order_dir', 'ASC');
         if ($query) {
             $query->order($order);
             return $query;
         }
         return " ORDER BY " . $order;
     }
 }

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

 /**
  * Process ordering query.
  *
  * @param \JDatabaseQuery $query     The query object.
  * @param string          $ordering  The ordering string.
  * @param string          $direction ASC or DESC.
  *
  * @return  void
  */
 protected function processOrdering(\JDatabaseQuery $query, $ordering = null, $direction = null)
 {
     $ordering = $ordering ?: $this->get('list.ordering');
     // If no ordering set, ignore this function.
     if (!$ordering) {
         return;
     }
     $direction = $direction ?: $this->get('list.direction', 'ASC');
     $ordering = explode(',', $ordering);
     // Add quote
     foreach ($ordering as $key => &$value) {
         // Remove extra spaces
         $value = preg_replace('/\\s+/', ' ', trim($value));
         $value = StringHelper::explode(' ', $value);
         if (!$this->filterField($value[0])) {
             unset($ordering[$key]);
             continue;
         }
         $value[0] = $this->mapField($value[0]);
         // Ignore expression
         if (!empty($value[0]) && $value[0][strlen($value[0]) - 1] != ')') {
             $value[0] = $query->quoteName($value[0]);
         }
         $value = implode(' ', $value);
     }
     $ordering = implode(', ', $ordering);
     if (!$ordering) {
         return;
     }
     $query->order($ordering . ' ' . $direction);
 }

 private function add_product_rows_query_order(JDatabaseQuery $query)
 {
     $sort_data = $this->get_sort_data();
     $sort_by = '';
     $sort_order = $sort_data['sort_order'] == "asc" || $sort_data['sort_order'] == "desc" || $sort_data['sort_order'] == "" ? strtoupper($sort_data['sort_order']) : "ASC";
     switch ($sort_data['sort_by']) {
         case 'ordering':
             $sort_by = 'T_PRODUCTS.ordering';
             break;
         case 'name':
             $sort_by = 'T_PRODUCTS.name';
             break;
         case 'manufacturer':
             $sort_by = 'T_MANUFACTURERS.name';
             break;
         case 'price':
             $sort_by = 'T_PRODUCTS.price';
             break;
         case 'reviews_count':
             $sort_by = 'T_FEEDBACK.reviews_count';
             break;
         case 'rating':
             $sort_by = 'T_RATINGS.rating';
             break;
     }
     if ($sort_by != '') {
         $query->order($sort_by . ' ' . $sort_order);
     }
     $query->order('T_PRODUCTS.ordering ' . $sort_order);
     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;
//.........这里部分代码省略.........