Пример #1
0
 /**
  * Tests the JDatabaseQuery::group method.
  *
  * @return  void
  *
  * @since   11.3
  */
 public function testGroup()
 {
     $this->assertThat($this->_instance->group('foo'), $this->identicalTo($this->_instance), 'Tests chaining.');
     $this->assertThat(trim($this->_instance->group), $this->equalTo('GROUP BY foo'), 'Tests rendered value.');
     // Add another column.
     $this->_instance->group('bar');
     $this->assertThat(trim($this->_instance->group), $this->equalTo('GROUP BY foo,bar'), 'Tests rendered value after second use.');
 }
Пример #2
0
 /**
  * The post getQuery object.
  *
  * @param JDatabaseQuery $query The db query object.
  *
  * @return  void
  */
 protected function postGetQuery(\JDatabaseQuery $query)
 {
     $keys = $this->state->get('profileKeys');
     // Build SQL Pivot
     // ========================================================================
     foreach ($keys as $key) {
         if ($key) {
             /*
              * Use MySQL Pivot query:
              * MAX(IF(profile.key = 'foo', profile.value, NULL)) AS foo
              */
             $query->select($query->format("MAX(IF(profile.key = %q, profile.value, NULL)) AS %e", $key, $key));
         }
     }
     $query->group('user.id');
 }
Пример #3
0
 /**
  * Implements custom filtering
  *
  * @param   \JDatabaseQuery  $query           The model query we're operating on
  * @param   bool             $overrideLimits  Are we told to override limits?
  *
  * @return  void
  */
 protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false)
 {
     $db = $this->getDbo();
     $logCategoryLimit = $this->getState('logCategoryLimit', 0, 'int');
     $fltCategory = null;
     /**
      * In the back-end of the component when the model state 'logCategoryLimit' is set to 1 we will get the default
      * category ID to filter by from the 'category' state variable of the Logs model. This allows us to do a
      * drill-down search in the logs using nothing but XML forms.
      */
     if ($this->container->platform->isBackend() && $logCategoryLimit) {
         /** @var Categories $logsModel */
         $logsModel = $this->container->factory->model('Logs');
         $fltCategory = $logsModel->getState('category', null, 'int');
     }
     $fltCategory = $this->getState('category_id', $fltCategory, 'int');
     $fltCategory = $this->getState('category', $fltCategory, 'int');
     if ($fltCategory > 0) {
         $query->where($db->qn('category_id') . ' = ' . $db->q($fltCategory));
     }
     $fltAccessUser = $this->getState('access_user', null, 'int');
     if (!is_null($fltAccessUser)) {
         $access_levels = $this->container->platform->getUser($fltAccessUser)->getAuthorisedViewLevels();
         if (empty($access_levels)) {
             // Essentially, tell it to find nothing if no our user is authorised to no access levels
             $access_levels = [$db->q(-1)];
         }
         $access_levels = array_map(array($db, 'quote'), $access_levels);
         // Filter this table
         $query->where('(' . '(' . $db->qn('access') . ' IN (' . implode(',', $access_levels) . ')) OR (' . $db->qn('show_unauth_links') . ' = ' . $db->q(1) . '))');
         // Filter the categories table, too
         $this->whereHas('category', function (\JDatabaseQuery $subQuery) use($access_levels, $db) {
             $subQuery->where($db->qn('access') . ' IN (' . implode(',', $access_levels) . ')');
         });
     }
     $fltNoBEUnpub = $this->getState('nobeunpub', null, 'int');
     if ($fltNoBEUnpub) {
         // Filter this table
         $query->where($db->qn('published') . ' != ' . $db->q(0));
         // Filter the categories table, too
         $this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db) {
             $subQuery->where('NOT(' . $db->qn('published') . ' = ' . $db->q('0') . ' AND ' . $db->qn('type') . '=' . $db->q('bleedingedge') . ')');
         });
     }
     $fltLanguage = $this->getState('language', null, 'cmd');
     $fltLanguage2 = $this->getState('language2', null, 'string');
     if ($fltLanguage != '*' && $fltLanguage != '') {
         // Filter this table
         $query->where($db->qn('language') . ' IN (' . $db->q('*') . ',' . $db->q($fltLanguage) . ')');
         // Filter the categories table, too
         $this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db, $fltLanguage) {
             $subQuery->where($db->qn('language') . ' IN (' . $db->q('*') . ',' . $db->q($fltLanguage) . ')');
         });
     } elseif ($fltLanguage2) {
         // Filter this table
         $query->where($db->qn('language') . ' = ' . $db->q($fltLanguage2));
         // Filter the categories table, too
         $this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db, $fltLanguage2) {
             $subQuery->where($db->qn('language') . ' = ' . $db->q($fltLanguage2));
         });
     }
     $fltMaturity = $this->getState('maturity', 'alpha', 'cmd');
     switch ($fltMaturity) {
         case 'beta':
             $query->where($db->qn('maturity') . ' IN (' . $db->q('beta'), ',' . $db->q('rc') . ',' . $db->q('stable') . ')');
             break;
         case 'rc':
             $query->where($db->qn('maturity') . ' IN (' . $db->q('rc') . ',' . $db->q('stable') . ')');
             break;
         case 'stable':
             $query->where($db->qn('maturity') . ' = ' . $db->q('stable'));
             break;
     }
     // Latest version filter. Use as $releases->published(1)->latest(true)->get(true)
     $fltLatest = $this->getState('latest', false, 'bool');
     if ($fltLatest) {
         // Why just a DESC group by clause? See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group
         $query->group($db->qn('category_id') . ' DESC');
     }
     // Order filtering
     $fltOrderBy = $this->getState('orderby_filter', null, 'cmd');
     switch ($fltOrderBy) {
         case 'alpha':
             $this->setState('filter_order', 'version');
             $this->setState('filter_order_Dir', 'ASC');
             break;
         case 'ralpha':
             $this->setState('filter_order', 'version');
             $this->setState('filter_order_Dir', 'DESC');
             break;
         case 'created':
             $this->setState('filter_order', 'created');
             $this->setState('filter_order_Dir', 'ASC');
             break;
         case 'rcreated':
             $this->setState('filter_order', 'created');
             $this->setState('filter_order_Dir', 'DESC');
             break;
         case 'order':
             $this->setState('filter_order', 'ordering');
             $this->setState('filter_order_Dir', 'ASC');
             break;
     }
 }
Пример #4
0
 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;
     }
 }
Пример #5
0
 /**
  * The post getQuery object.
  *
  * @param JDatabaseQuery $query The db query object.
  *
  * @return  void
  */
 protected function postGetQuery(\JDatabaseQuery $query)
 {
     $query->group('module.id');
 }