PHP JDatabaseQuery::group方法代码示例

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

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

 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;
//.........这里部分代码省略.........

 /**
  * The post getQuery object.
  *
  * @param JDatabaseQuery $query The db query object.
  *
  * @return  void
  */
 protected function postGetQuery(\JDatabaseQuery $query)
 {
     $query->group('module.id');
 }