/** * 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); }
/** * Triggered after building the query. If we're in the front-end we force the sorting to be always by ordering, * ascending * * @param \JDatabaseQuery $query * @param bool $overrideLimits */ protected function onAfterBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false) { if ($this->container->platform->isFrontend()) { $query->order($this->getDbo()->qn('ordering') . ' ASC'); } }
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; } }
/** * Get options order by * * @param string $view View mode '' or 'filter' * @param bool|JDatabaseQuery $query Set to false to return a string * * @return string order by statement */ protected function getOrderBy($view = '', $query = false) { if ($view == 'filter') { $params = $this->getParams(); $joinKey = $this->getJoinValueColumn(); $joinLabel = $this->getJoinLabelColumn(); $order = ''; switch ($params->get('filter_groupby', 'text')) { case 'text': $order = $joinLabel . 'ASC '; break; case 'value': $order = $joinKey . 'ASC '; break; case '-1': default: // Check if the 'Joins where and/or order by statement' has an order by $joinWhere = $params->get('database_join_where_sql'); if (JString::stristr($joinWhere, 'ORDER BY')) { $joinWhere = str_replace('order by', 'ORDER BY', $joinWhere); $joinWhere = explode('ORDER BY', $joinWhere); if (count($joinWhere) > 1) { $order = $joinWhere[count($joinWhere) - 1]; } } break; } if (!$query) { return $order === '' ? '' : ' ORDER BY ' . $order; } else { if ($order !== '') { $query->order($order); } return $query; } } else { if (!empty($this->orderBy)) { if (!$query) { return $this->orderBy; } else { $order = JString::str_ireplace('ORDER BY', '', $this->orderBy); $query->order($order); return $query; } } else { if (!$query) { return "ORDER BY text ASC "; } else { $query->order('text ASC'); return $query; } } } }